일상의기록 프로젝트 2 (MariaDB 데이터베이스 설계)

DB설계 먼저 우선적으로 진행하려고 한다.

일단 사용할 DB의 경우 SQLite 와 MariaDB 두 개 중 고민했는데

정규화된 데이터 구조와 백엔드 개발에 집중하고 있기 때문에,MariaDB로 정했다.

SQLite의 경우 파일형식으로 된 DB라 간단하긴 하지만, 실제 DB 설계에 근접하게 공부해보고 싶어서

그나마 관계형 DB이면서 무료인 MariaDB를 사용하기로 한다.

또 여러사람이 함께 사용하게 되는 서비스이기 때문에 SQLite는 동시에 하나의 쓰기 작업만 허용하므로,

다중 사용자가 동시에 쓰기 작업을 시도하면 경합 상태(contention)가 발생할 수 있다고해서

아무래도 MariaDB를 선택하기로 마음먹었다.

### 경합 상태(contention) : 여러 프로세스나 사용자가 동시에 동일한 리소스(예: 데이터베이스, 파일 등)에 접근하려고 하여 대기나 성능 저하가 발생하는 상황 ###

일단 내 데이터 요구사항은 아래와 같다.

회원 시스템:
- 회원가입, 로그인 기능 제공.
- 사용자별로 업로드된 게시글과 사진을 관리.
게시글 작성 및 관리:
- 게시글 작성, 수정, 삭제.
- 게시글과 사진 연결.
사진 업로드 및 분석:
- 사진의 EXIF 데이터와 캡션 추출 결과 저장.
AI 기반 스토리 생성:
- OpenAI API 호출 후 생성된 스토리 저장.

1. 당연히 회원가입 및 로그인을 위해 일단 회원정보 테이블이 필요하다.

Users 테이블로 만들 예정이다.

기본적으로 유저명,이메일,패스워드,활성화여부,회원가입일,정보수정일 정도로 아주 필요한 정보만 들어갈 예정이다.

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username NVARCHAR(255) NOT NULL,
    email NVARCHAR(255) NOT NULL UNIQUE,
    password NVARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

사용자 계정 정보를 저장하며, id는 Primary Key
email은 UNIQUE로 설정하여 중복 가입 방지.

2. 게시글을 관리할 Posts 테이블도 필요할 것 같다.

제목, 본문, 게시글의 상태, 게시글이 게시된 시점, 작성자의ID, 작성시각,수정시각 이 저장 될 예정이다.

CREATE TABLE posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title NVARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    status NVARCHAR(20) DEFAULT 'draft',
    published_at DATETIME NULL,
    user_id BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

status는 기본값(DEFAULT)은 'draft'로 설정되어, 게시글이 초안 상태로 저장된다.

게시글이 작성 중인지, 게시된 상태인지 등을 구분하는 값이다.

다른 상태값으로는 published, archived 등이 있을 예정이다.

published_at과 created_at의 차이는

사용자가 게시글을 작성했지만 초안 상태로 저장(status = 'draft')했다면, published_at은 NULL 이 들어간다.

이후 게시글을 게시(status = 'published')할 때 현재 시각이 published_at에 저장된다.

created_at의 경우 게시글이 생성될 때 자동으로 현재 시각이 저장된다.

게시글이 초안 상태든, 즉시 게시되든 관계없이 작성 시점은 항상 기록된다.

created_at:
- 작성된 모든 게시글의 생성 날짜를 기록.
- 초안 상태(status = 'draft')인 게시글도 포함.
published_at:
- 게시된 게시글만 표시하거나, 특정 기간 동안 게시된 글을 필터링할 때 사용.
- 게시되지 않은 게시글은 NULL로 유지되므로 초안 상태의 글을 구분할 수 있음.

user_id의 경우 외래 키(FOREIGN KEY)로 users 테이블의 id를 참조한다. (사용자와 게시글 간 1:N 관계)

ON DELETE CASCADE: 작성자가 삭제되면 해당 사용자가 작성한 모든 게시글도 삭제된다.

3. 사진정보와 추출한 메타데이터를 저장할 Photos 테이블도 필요하다.

CREATE TABLE photos (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    blob_url NVARCHAR(255) NOT NULL,
    blob_name NVARCHAR(255) NOT NULL,
    original_filename NVARCHAR(255) NOT NULL,
    file_size INT NOT NULL,
    latitude FLOAT NULL,
    longitude FLOAT NULL,
    taken_at DATETIME NULL,
    user_id BIGINT NOT NULL,
    post_id BIGINT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    exif_make NVARCHAR(255) NULL,
    exif_model NVARCHAR(255) NULL,
    exif_exposure_time NVARCHAR(255) NULL,
    exif_f_number FLOAT NULL,
    exif_iso_speed INT NULL,
    processing_status NVARCHAR(20) DEFAULT 'pending',
    error_message TEXT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL
);

사진 데이터와 EXIF 메타데이터를 저장
게시글(Post)과 연관될 수 있으므로 post_id를 추가

엄청 길긴한데 정리하자면 이렇다.

blob_url NVARCHAR(255) NOT NULL : 사진이 저장된 경로(URL) 또는 위치.
blob_name NVARCHAR(255) NOT NULL : 사진 파일의 시스템 내부에서 사용되는 이름.
original_filename NVARCHAR(255) NOT NULL : 사용자가 업로드한 사진의 원래 파일명.
file_size INT NOT NULL : 사진 파일의 크기(바이트 단위).
latitude FLOAT NULL : 사진이 촬영된 위치의 위도.
longitude FLOAT NULL : 사진이 촬영된 위치의 경도.
taken_at DATETIME NULL : 사진이 촬영된 시점(EXIF 기반).
user_id BIGINT NOT NULL : 사진을 업로드한 사용자의 ID.
post_id BIGINT NULL : 사진이 연결된 게시글의 ID.
created_at DATETIME DEFAULT CURRENT_TIMESTAMP : 사진이 처음 업로드된 시점.
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP : 사진 정보가 마지막으로 수정된 시점.
exif_make NVARCHAR(255) NULL : 사진을 촬영한 카메라 제조사 정보(EXIF 기반).
exif_model NVARCHAR(255) NULL : 사진을 촬영한 카메라 모델 정보(EXIF 기반).
exif_exposure_time NVARCHAR(255) NULL : 사진의 노출 시간(EXIF 기반).
exif_f_number FLOAT NULL : 사진의 조리개 값(EXIF 기반).
exif_iso_speed INT NULL : 사진의 ISO 감도(EXIF 기반).
processing_status NVARCHAR(20) DEFAULT 'pending' : 사진 처리 상태(예: pending, completed, failed).
error_message TEXT NULL : 처리 실패 시 에러 메시지.

사진을 클라우드서버의 storage에 저장할 것 이기 때문에 url, 저장될 파일명, 원본 파일명이 분리되어 있다.

원본파일명과 저장될 파일명 두개로 나뉜이유는

일단 파일이름 중복 방지와 저장된파일명에 민감한정보가 포함되지 않도록 보호하기 위함이다.

Users 테이블과 한 사용자는 여러 사진을 업로드할 수 있다.(1:N 관계)

Posts 테이블과 한 게시글은 여러 사진을 포함할 수 있다.(1:N 관계)

나머지는 EXIF 메타데이터를 추출한 값들이다.

4.사진에서 추출된 dence caption 이 저장 될 AI_Generate_Data 테이블이다.

CREATE TABLE ai_generate_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    caption TEXT NULL,
    story TEXT NULL,
    photo_id BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE
);

caption TEXT NULL : AI가 생성한 사진의 캡션.
story TEXT NULL : AI가 생성한 사진의 스토리.
photo_id BIGINT NOT NULL : AI 데이터가 연관된 사진의 ID.
created_at DATETIME DEFAULT CURRENT_TIMESTAMP : AI 데이터가 처음 생성된 시점.
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP : AI 데이터가 마지막으로 수정된 시점.

AI가 생성한 캡션과 사진의 story데이터를 저장할 테이블이며, photos 테이블의 id를 참조한다.

연관된 사진이 삭제될 경우 해당 AI 데이터도 자동으로 삭제되도록 설정한다.

4개의 테이블이 존재하며 정리하자면 아래와 같다.

Users (사용자 테이블): 사용자 계정 정보 저장.
Posts (게시글 테이블): 사용자가 작성한 게시글 관리.
Photos (사진 테이블): 업로드된 사진과 메타데이터(EXIF) 관리.
AI_Generate_Data (AI 생성 데이터 테이블):AI로 생성된 캡션, 스토리, 기타 정보를 저장.

Users → Posts (1:N): 한 사용자는 여러 게시글을 작성할 수 있음.
Users → Photos (1:N):한 사용자는 여러 사진을 업로드할 수 있음.
Posts → Photos (1:N):한 게시글은 여러 사진을 포함할 수 있음.
Photos → AI_Generate_Data (1:1):한 사진은 하나의 AI 생성 데이터를 가질 수 있음.

텍스트 만으로는 정리하기 어려워 ERD 다이어그램을 만들려고 했는데,

https://dbdiagram.io/ 사이트에서 SQL 스키마를 dbdiagram.io에서 사용하는 DSL로 변환한 뒤

~~(sql문 주면 GPT가 알아서 짜줌)~~

에디터에 붙여넣으면 자동으로 다이어그램이 생성되며 이미지파일로도 저장할 수 있다고 해서 생성해봤다.

시각화한 자료는 위 이미지와 같다.

텍스트도 도움이 되지만 한눈에 파악하는건 ERD가 좀 더 수월하다.

역시 이미지란 대단하다.

( ERD는 Entity Relationship Diagram의 약자로, 개체 관계 다이어그램 또는 개체 관계 지도라고도 불립니다. 데이터베이스에 저장될 데이터 엔티티와 엔티티 간의 관계를 시각적으로 표현하는 모델링 기법으로, 데이터베이스 설계 과정에서 중요한 역할을 한다고합니다. )

DB 설계는 일단 이정도로 정리하고 추 후 구현 시 필요한 데이터가 있다면 추가로 저장할 예정이다.

이로써 아이디어 구체화,기능정의,플로우차트,DB설계까지 완료되었다.

다음에 할일은 사용할 기술정하기와 api 기능정리 진행예정이다.(바뀔수도 있음)

담편계속...

'만들고 싶은거 만들기' 카테고리의 다른 글

일상의기록 프로젝트 1 (프로젝트 컨셉 및 기능 정의) (0)	2024.12.22
광고 수익을 창출할 웹사이트 만들어보기 4-4(React서버 프론트엔드 부분 기능 추가구현 및 개선사항) (2)	2024.12.10
광고 수익을 창출할 웹사이트 만들어보기 4-3(Flask서버 백앤드 부분 기능 추가구현 및 개선사항) (0)	2024.12.10
광고 수익을 창출할 웹사이트 만들어보기 4-2(open AI API,AWS EC2 사용료 예상) (1)	2024.12.10
광고 수익을 창출할 웹사이트 만들어보기 4-1(중간점검) (2)	2024.12.08

pongdonglog

일상의기록 프로젝트 2 (MariaDB 데이터베이스 설계)

'만들고 싶은거 만들기' 카테고리의 다른 글

티스토리툴바

일상의기록 프로젝트 2 (MariaDB 데이터베이스 설계)

'만들고 싶은거 만들기' 카테고리의 다른 글

'만들고 싶은거 만들기' Related Articles

티스토리툴바