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 (---|): 하나의 기록은 반드시 하나의 천문 현상에 속함.
- User 테이블과: 사용자 한 명은 0개 이상의 관측 기록을 가질 수 있음.
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|---<): 천문 현상은 여러 번 관측될 수 있음.
- 천문 현상이 여러 관측 기록과 연관되지만, 각 기록은 하나의 이벤트에만 속해야 한다.
- ObservationRecord 테이블과: 하나의 천문 현상은 여러 개의 관측 기록과 연관될 수 있음.
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 (---|): 하나의 추천은 하나의 장소와만 연결됨.
- User 테이블과: 사용자는 여러 번 추천을 받을 수 있음.
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|---<): 장소는 여러 번 추천될 수 있음.
- ObservationRecommendation 테이블과: 하나의 장소는 여러 번 추천될 수 있음.
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|---<): 천문 현상도 여러 사용자의 관심을 받을 수 있음.
- User 테이블과: 사용자는 0개 이상의 천문 현상에 관심을 가질 수 있음.
'Coding History > Team Project' 카테고리의 다른 글
팀플 소셜(구글)로그인 구현중. (완료) (9) | 2024.10.06 |
---|---|
팀플 소셜(구글)로그인 구현 시작. (Firebase 프로젝트 생성) (4) | 2024.10.05 |
팀플 프로젝트에 DB 추가. (1) | 2024.10.05 |
팀플 깃 전략 (git Organizations) (8) | 2024.10.05 |
팀플 아이디어 임시 계획 (천체 관측 추천 사이트) (2) | 2024.10.01 |