ERD 설명 및 SQL 코드

이번 ERD는 천문 현상 관측 서비스를 구축하기 위한 데이터베이스 구조를 설계. 이 시스템은 사용자 데이터를 기반으로 맞춤형 관측 장소와 천문 현상 이벤트를 추천하며, 사용자는 개인 기록을 남길 수 있다.

1. User 테이블

CREATE TABLE User (
    user_id INT AUTO_INCREMENT PRIMARY KEY,  -- 고유 사용자 ID
    name VARCHAR(100) NOT NULL,              -- 사용자 이름
    email VARCHAR(100) NOT NULL UNIQUE,      -- 이메일 (Google 계정)
    google_id VARCHAR(255) NOT NULL UNIQUE,  -- Google 소셜 로그인 ID
    latitude DECIMAL(10, 7),                 -- 선호 관측 위치 (위도)
    longitude DECIMAL(10, 7),                -- 선호 관측 위치 (경도)
    preferred_time VARCHAR(50),              -- 선호 관측 시간대 (아침, 저녁 등)
    alert_enabled BOOLEAN DEFAULT TRUE,      -- 천문 현상 알림 여부
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• 설명: 사용자의 정보를 저장하는 테이블로, Google 소셜 로그인을 사용하며, 사용자의 선호 관측 위치와 시간을 저장할 수 있다.
• 관계: User 테이블은 ObservationRecord, ObservationRecommendation, UserEventInterest와 연결된다. 사용자는 여러 관측 기록을 가질 수 있지만, 각 관측 기록은 반드시 하나의 사용자와 연관됨.
  • zero or many (O|---<): 사용자는 0개 이상의 관측 기록, 추천, 관심 천문 현상에 연결될 수 있다.
  • user_id는 여러 테이블에서 외래키로 참조됨.

2. ObservationRecord 테이블 (사용자 관측 기록 저장)

CREATE TABLE ObservationRecord (
    record_id INT AUTO_INCREMENT PRIMARY KEY,    -- 고유 관측 기록 ID
    user_id INT,                                 -- 관측한 사용자 ID
    event_id INT,                                -- 관측한 천문 현상 ID
    observation_date DATETIME NOT NULL,          -- 관측 날짜
    latitude DECIMAL(10, 7),                     -- 관측 장소의 위도
    longitude DECIMAL(10, 7),                    -- 관측 장소의 경도
    observation_result TEXT,                     -- 관측 결과 (성공 여부, 메모 등)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES User(user_id),
    FOREIGN KEY (event_id) REFERENCES AstronomicalEvent(event_id)
);

• 설명: 사용자의 관측 기록을 저장하는 테이블. 관측 날짜와 장소 정보도 포함된다.
• 관계:
  • User 테이블과: 사용자 한 명은 0개 이상의 관측 기록을 가질 수 있음.
    • zero or many (O|---<): 사용자는 0개 이상의 기록을 가질 수 있음.
  • AstronomicalEvent 테이블과: 하나의 천문 현상에 대한 관측 기록이 여러 개일 수 있음.
    • one only (---|): 하나의 기록은 반드시 하나의 천문 현상에 속함.

3. AstronomicalEvent 테이블 (천문 현상 정보)

CREATE TABLE AstronomicalEvent (
    event_id INT AUTO_INCREMENT PRIMARY KEY,   -- 고유 천문 현상 ID
    event_name VARCHAR(100) NOT NULL,          -- 천문 현상 이름 (ex. 일식, 월식 등)
    start_time DATETIME NOT NULL,              -- 천문 현상 시작 시각
    end_time DATETIME NOT NULL,                -- 천문 현상 종료 시각
    latitude DECIMAL(10, 7),                   -- 이벤트 발생 위치 (위도)
    longitude DECIMAL(10, 7),                  -- 이벤트 발생 위치 (경도)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• 설명: 천문 현상의 정보를 저장하는 테이블. 이벤트 이름과 시작, 종료 시각, 발생 위치 정보가 포함된다.
• 관계:
  • ObservationRecord 테이블과: 하나의 천문 현상은 여러 개의 관측 기록과 연관될 수 있음.
    • zero or many (O|---<): 천문 현상은 여러 번 관측될 수 있음.
    • 천문 현상이 여러 관측 기록과 연관되지만, 각 기록은 하나의 이벤트에만 속해야 한다.

4. ObservationRecommendation 테이블 (관측 장소 추천 정보)

CREATE TABLE ObservationRecommendation (
    recommendation_id INT AUTO_INCREMENT PRIMARY KEY,  -- 고유 추천 ID
    user_id INT,                                       -- 추천 대상 사용자 ID
    location_id INT,                                   -- 추천된 장소 ID
    recommended_time DATETIME,                         -- 추천된 시간
    FOREIGN KEY (user_id) REFERENCES User(user_id),
    FOREIGN KEY (location_id) REFERENCES ObservationLocation(location_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• 설명: 사용자에게 추천된 관측 장소와 추천된 시간을 저장하는 테이블.
• 관계:
  • User 테이블과: 사용자는 여러 번 추천을 받을 수 있음.
    • zero or many (O|---<): 사용자는 0개 이상의 추천을 받을 수 있음.
  • ObservationLocation 테이블과: 추천된 장소와 연관됨.
    • one only (---|): 하나의 추천은 하나의 장소와만 연결됨.

5. ObservationLocation 테이블 (추천 장소 정보)

CREATE TABLE ObservationLocation (
    location_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),           -- 관측 장소 이름
    latitude DECIMAL(10, 7),      -- 관측 장소 위도
    longitude DECIMAL(10, 7),     -- 관측 장소 경도
    is_recommended BOOLEAN DEFAULT FALSE, -- 추천된 장소 여부
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• 설명: 추천된 관측 장소의 정보를 저장하는 테이블.
• 관계:
  • ObservationRecommendation 테이블과: 하나의 장소는 여러 번 추천될 수 있음.
    • zero or many (O|---<): 장소는 여러 번 추천될 수 있음.

6. UserEventInterest 테이블 (사용자와 천문 현상의 관심도)

CREATE TABLE UserEventInterest (
    user_id INT,
    event_id INT,
    PRIMARY KEY (user_id, event_id),
    FOREIGN KEY (user_id) REFERENCES User(user_id),
    FOREIGN KEY (event_id) REFERENCES AstronomicalEvent(event_id)
);

• 설명: 사용자가 관심을 가지는 천문 현상을 저장하는 중간 테이블. 다대다 관계를 처리.
• 관계:
  • User 테이블과: 사용자는 0개 이상의 천문 현상에 관심을 가질 수 있음.
    • zero or many (O|---<): 사용자는 여러 개의 천문 현상과 연관될 수 있음.
  • AstronomicalEvent 테이블과: 천문 현상도 여러 명의 사용자와 연결될 수 있음.
    • zero or many (O|---<): 천문 현상도 여러 사용자의 관심을 받을 수 있음.