VocaLab DB 설계

VocaLab Database Schema

1. Users 테이블

컬럼명	컬럼 ID	자료형	NN	PK	FK	UK	설명
no	userNo	BIGINT	O	O			사용자 일련번호 (AutoIncrement)
id	userId	VARCHAR(100)	O			O	사용자 고유 ID
name	userName	VARCHAR(100)	O				사용자 이름
password	userPassword	VARCHAR(255)					비밀번호 (해시 저장)
nickname	userNickname	VARCHAR(100)					닉네임
email	userEmail	VARCHAR(100)	O			O	이메일
social	userSocial	ENUM	O				소셜 로그인 정보 (NONE, GOOGLE, NAVER)
role	userRole	ENUM	O				사용자 권한 (USER, ADMIN)
status	userStatus	ENUM	O				계정 상태 (NORMAL, BANNED)
birthDate	birthDate	VARCHAR(10)	O				생년월일
gender	gender	INT	O				성별
createdAt	createdAt	DATETIME	O				생성 날짜
updatedAt	updatedAt	DATETIME	O				수정 날짜

2. Files 테이블

컬럼명	컬럼 ID	자료형	NN	PK	FK	UK	설명
id	fileId	INT	O	O			파일 고유 ID
userId	userId	VARCHAR(100)	O		users.userId		업로드한 사용자 ID
tableId	tableId	INT					관련 테이블의 ID
category	category	ENUM	O				NONE, COMPILE, TEST, ESSAY, BOARD, PROFILE, WORD, TESTRECORD, COMPILERESULT
fileType	fileType	ENUM	O				FILE, IMAGE
filePath	filePath	VARCHAR(255)	O			O	파일 경로
uploadedAt	uploadedAt	DATETIME	O				업로드 일자

3. WordBooks 테이블

컬럼명	컬럼 ID	자료형	NN	PK	FK	UK	설명
id	wordBookId	INT	O	O			단어장 고유 ID
userId	userId	VARCHAR(100)	O		users.userId		단어장 소유 유저 ID
title	wordBookTitle	VARCHAR(255)	O				단어장 제목
bookmark	bookmark	TINYINT(1)	O				즐겨찾기 여부 (0, 1)
createdAt	createdAt	DATETIME	O				생성 날짜
updatedAt	updatedAt	DATETIME	O				수정 날짜

4. Tests 테이블

컬럼명	컬럼 ID	자료형	NN	PK	FK	UK	설명
id	testId	INT	O	O			테스트 고유 ID
userId	userId	VARCHAR(100)	O		users.userId		테스트 생성 사용자 ID
wordBookId	wordBookId	INT	O		wordBooks.wordBookId		연관된 단어장 ID
category	category	ENUM	O				CHOMEAN, CHOVOCA, FLASHCARDS
createdAt	createdAt	DATETIME	O				생성 일자

5. TestRecord 테이블

컬럼명	컬럼 ID	자료형	NN	PK	FK	UK	설명
id	testRecordId	INT	O	O			테스트 기록 고유 ID
userId	userId	VARCHAR(100)	O		users.userId		테스트 사용자 ID
testId	testId	INT	O		tests.testId		테스트 ID
score	score	INT	O				테스트 점수
mistakes	mistakes	TEXT					틀린 단어 리스트
createdAt	createdAt	DATETIME	O				생성 일자

6. CompileRecord 테이블

컬럼명	컬럼 ID	자료형	NN	PK	FK	UK	설명
id	compileId	INT	O	O			단어 추출 기록 고유 ID
userId	userId	VARCHAR(100)	O		users.userId		단어 추출 사용자 ID
source	source	TEXT					단어 추출의 원본 데이터
createdAt	createdAt	DATETIME	O				생성 일자

7. Board 테이블

컬럼명	컬럼 ID	자료형	NN	PK	FK	UK	설명
id	boardId	INT	O	O			게시판 고유 ID
userId	userId	VARCHAR(100)	O		users.userId		게시글 작성자 ID
category	category	ENUM	O				NOTICE, FAQ, INQUIRY, REPLY
title	title	VARCHAR(255)					게시글 제목
content	content	TEXT	O				게시글 내용
parentId	parentId	INT			board.boardId		원글 참조 ID
replyStatus	replyStatus	ENUM	O				답변 유무 (NONE, DONE)
createdAt	createdAt	DATETIME	O				생성 일자
updatedAt	updatedAt	DATETIME	O				수정 일자

8. Essay 테이블

컬럼명	컬럼 ID	자료형	NN	PK	FK	UK	설명
id	essayId	INT	O	O			에세이 고유 ID
userId	userId	VARCHAR(100)	O		users.userId		에세이 작성자 ID
wordBookId	wordBookId	INT	O		wordBooks.wordBookId		연관된 단어장 ID
title	title	VARCHAR(255)	O				에세이 제목

VocaLab ERD 상세 설명

1. Users 테이블

• 사용자 정보를 저장하는 기본 테이블입니다.
• userNo를 기본 키(PK)로 사용하여 각 사용자를 고유하게 식별합니다.
• userId와 userEmail은 고유한 값(UK)으로 설정되어 중복을 허용하지 않습니다.
• userPassword와 userNickname은 NULL을 허용하여 소셜 로그인 사용자를 수용합니다.
• userSocial 필드로 일반/Google/Naver 로그인 방식을 구분합니다.

2. Files 테이블

• 업로드된 파일의 정보를 저장하는 테이블입니다.
• filePath는 고유값(UK)으로 설정되어 중복 파일 업로드를 방지합니다.
• category는 파일의 용도를 9가지로 구분합니다:
  • NONE: 기본값
  • COMPILE: 단어 추출 관련
  • TEST: 시험 관련
  • ESSAY: 에세이 관련
  • BOARD: 게시판 관련
  • PROFILE: 프로필 이미지
  • WORD: 단어 관련
  • TESTRECORD: 시험 기록 관련
  • COMPILERESULT: 컴파일 결과 관련
• fileType으로 일반 파일과 이미지를 구분합니다.

3. WordBooks 테이블

• 사용자가 생성한 단어장 정보를 저장합니다.
• wordBookId를 기본 키로 사용하며, 특정 사용자(userId)의 소유임을 명시합니다.
• bookmark 필드는 TINYINT(1) 타입으로, 0(미설정)과 1(설정)로 즐겨찾기 여부를 관리합니다.
• 생성 및 수정 시각을 자동으로 기록합니다.

4. Tests 테이블

• 단어장 기반의 테스트 정보를 저장합니다.
• category로 세 가지 테스트 유형을 구분합니다:
  • CHOMEAN: 의미 선택 테스트
  • CHOVOCA: 단어 선택 테스트
  • FLASHCARDS: 플래시카드 테스트
• wordBookId를 외래 키로 참조하여 특정 단어장과의 연결성을 유지합니다.

5. TestRecord 테이블

• 사용자의 테스트 수행 결과를 기록합니다.
• score 필드로 테스트 점수를 기록하며, mistakes 필드에 오답 정보를 저장합니다.
• testId와 userId를 외래 키로 참조하여 어떤 사용자가 어떤 테스트를 수행했는지 추적합니다.

6. CompileRecord 테이블

• 단어 추출 작업의 이력을 관리합니다.
• source 필드에 추출 작업의 원본 데이터를 저장합니다(TEXT 타입).
• 모든 추출 작업은 특정 사용자(userId)와 연결됩니다.

7. Board 테이블

• 게시판 글 정보를 관리합니다.
• category로 네 가지 글 유형을 구분합니다:
  • NOTICE: 공지사항
  • FAQ: 자주 묻는 질문
  • INQUIRY: 문의사항
  • REPLY: 답변
• parentId로 답변 글을 원글과 연결하며, CASCADE 삭제를 지원합니다.
• replyStatus로 답변 상태를 관리합니다(NONE/DONE).

8. Essay 테이블

• 단어장 기반의 에세이 정보를 저장합니다.
• wordBookId를 외래 키로 참조하여 특정 단어장과 연결됩니다.
• title 필드는 NULL을 허용하지 않아 모든 에세이는 제목을 가져야 합니다.

데이터베이스 특징

1. 참조 무결성:
   • 모든 외래 키는 적절한 참조 테이블의 기본 키를 가리킵니다.
   • 대부분의 외래 키는 CASCADE 삭제를 지원하여 참조 무결성을 유지합니다.
2. 자동 시간 관리:
   • createdAt은 레코드 생성 시 자동으로 현재 시각이 기록됩니다.
   • updatedAt이 있는 테이블의 경우, 수정 시 자동으로 시각이 업데이트됩니다.
3. 문자셋:
   • 모든 테이블은 utf8mb4 문자셋을 사용하여 이모지를 포함한 모든 유니코드 문자를 지원합니다.
4. 스토리지 엔진:
   • 모든 테이블은 트랜잭션을 지원하는 InnoDB 엔진을 사용합니다.

데이터 관계 (ERD 상 표현)

1. Users 테이블 (중심 엔티티):
   • 모든 주요 테이블과 1:N 관계를 형성 (userId 기반)
   • Files, WordBooks, Tests, TestRecord, CompileRecord, Board, Essay의 부모 테이블
   • 삭제 시 연관된 모든 자식 레코드가 CASCADE로 삭제됨
2. Files 테이블 (파일 관리 허브):
   • tableId + category 조합으로 다음 엔티티들과 논리적 연결:
     • WordBooks: 단어장 첨부 파일 (예: 엑셀 파일)
     • Tests: 테스트 문제/보기 파일
     • TestRecord: 테스트 결과 증빙 파일
     • CompileRecord: 원본 텍스트 파일
     • Board: 게시글 첨부 파일
     • Essay: 에세이 관련 파일
3. WordBooks ↔ Tests/Essay (1:N):
   • 하나의 단어장에서 여러 테스트 생성 가능
   • 하나의 단어장으로 여러 에세이 작성 가능
   • 단어장 삭제 시 관련 테스트와 에세이도 CASCADE 삭제
4. Tests ↔ TestRecord (1:N):
   • 하나의 테스트에 대해 여러 사용자의 응시 기록 저장
   • mistakes 필드로 오답 분석 지원
   • 테스트 삭제 시 모든 응시 기록 CASCADE 삭제
5. Board의 자기 참조 (1:N):
   • parentId로 문의글-답변글 관계 구현
   • 원글 삭제 시 답변글도 CASCADE 삭제
   • replyStatus로 답변 상태 추적

설계 요약

1. 파일 관리 전략 (Files 테이블):
   • 중앙 집중식 파일 관리로 일관성 확보
   • 두 가지 참조 방식 지원:
   • -- 방식 1: 논리적 참조 SELECT * FROM Files WHERE tableId = ? AND category = 'WORDBOOK'; -- 방식 2: 외래 키 참조 ALTER TABLE Files ADD COLUMN wordBookId INT; ALTER TABLE Files ADD FOREIGN KEY (wordBookId) REFERENCES WordBooks(wordBookId);
2. 데이터 무결성 보장:
   • 모든 테이블이 userId로 Users와 연결
   • CASCADE DELETE로 고아 레코드 방지
   • 유니크 제약조건으로 중복 데이터 방지
     (예: Files.filePath, Users.userId/email)
   • Users 테이블만 BIGINT 타입의 userNo를 사용
   • userId는 VARCHAR(100)으로 비즈니스 식별자 역할
3. 참조 무결성 관리:
   • 모든 외래 키는 적절한 CASCADE 규칙 적용
   • Board 테이블의 자기 참조도 CASCADE 삭제 지원
4. Soft Delete 미적용:
   • 실제 레코드 삭제 방식 사용
   • 단, Users 테이블의 경우 userStatus로 계정 상태 관리
5. 시간 정보 관리:
   • 대부분의 테이블이 createdAt 포함
   • 주요 테이블(Users, WordBooks, Board)은 updatedAt도 포함
6. 파일 관리 전략:
   • Files 테이블을 통한 중앙 집중식 파일 관리
   • category와 fileType으로 용도 구분
   • filePath의 유니크 제약으로 중복 방지
7. 확장성 고려사항:
   • ENUM 타입 사용으로 도메인 제약 설정
   • category 필드들의 확장 가능한 설계
   • 소셜 로그인 대비한 NULL 허용 필드들
8. 성능 최적화:
   • 적절한 인덱스 설정 (FK, UK)
   • InnoDB 엔진 사용으로 트랜잭션 지원
   • utf8mb4 문자셋으로 다국어 지원

-- Users 테이블
CREATE TABLE `Users` (
  `userNo` bigint NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) NOT NULL,
  `userName` varchar(100) NOT NULL,
  `userPassword` varchar(255) DEFAULT NULL,
  `userNickname` varchar(100) DEFAULT NULL,
  `userEmail` varchar(100) NOT NULL,
  `userSocial` enum('NONE','GOOGLE','NAVER') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'NONE',
  `userRole` enum('USER','ADMIN') NOT NULL DEFAULT 'USER',
  `userStatus` enum('NORMAL','BANNED') NOT NULL DEFAULT 'NORMAL',
  `birthDate` varchar(10) NOT NULL,
  `gender` int NOT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`userNo`),
  UNIQUE KEY `userEmail` (`userEmail`),
  UNIQUE KEY `userId_UNIQUE` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Board 테이블
CREATE TABLE `Board` (
  `boardId` int NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) NOT NULL,
  `category` enum('NOTICE','FAQ','INQUIRY','REPLY') NOT NULL DEFAULT 'NOTICE',
  `title` varchar(255) DEFAULT NULL,
  `content` text NOT NULL,
  `parentId` int DEFAULT NULL,
  `replyStatus` enum('NONE','DONE') NOT NULL DEFAULT 'NONE',
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`boardId`),
  KEY `parentId` (`parentId`),
  KEY `Board_ibfk_1` (`userId`),
  CONSTRAINT `Board_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `Users` (`userId`),
  CONSTRAINT `Board_ibfk_2` FOREIGN KEY (`parentId`) REFERENCES `Board` (`boardId`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- CompileRecord 테이블
CREATE TABLE `CompileRecord` (
  `compileId` int NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) NOT NULL,
  `source` text,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`compileId`),
  KEY `CompileRecord_ibfk_1` (`userId`),
  CONSTRAINT `CompileRecord_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `Users` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=226 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Essay 테이블
CREATE TABLE `Essay` (
  `essayId` int NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) NOT NULL,
  `wordBookId` int NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`essayId`),
  KEY `wordBookId` (`wordBookId`),
  KEY `Essay_ibfk_1` (`userId`),
  CONSTRAINT `Essay_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `Users` (`userId`),
  CONSTRAINT `Essay_ibfk_2` FOREIGN KEY (`wordBookId`) REFERENCES `WordBooks` (`wordBookId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Files 테이블
CREATE TABLE `Files` (
  `fileId` int NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) NOT NULL,
  `tableId` int DEFAULT NULL,
  `category` enum('NONE','COMPILE','TEST','ESSAY','BOARD','PROFILE','WORD','TESTRECORD','COMPILERESULT') NOT NULL DEFAULT 'NONE',
  `fileType` enum('FILE','IMAGE') NOT NULL DEFAULT 'FILE',
  `filePath` varchar(255) NOT NULL,
  `uploadedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`fileId`),
  UNIQUE KEY `filePath` (`filePath`),
  KEY `Files_ibfk_1` (`userId`),
  CONSTRAINT `Files_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `Users` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- TestRecord 테이블
CREATE TABLE `TestRecord` (
  `testRecordId` int NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) NOT NULL,
  `testId` int NOT NULL,
  `score` int NOT NULL,
  `mistakes` text,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`testRecordId`),
  KEY `testId` (`testId`),
  KEY `TestRecord_ibfk_1` (`userId`),
  CONSTRAINT `TestRecord_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `Users` (`userId`),
  CONSTRAINT `TestRecord_ibfk_2` FOREIGN KEY (`testId`) REFERENCES `Tests` (`testId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Tests 테이블
CREATE TABLE `Tests` (
  `testId` int NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) NOT NULL,
  `wordBookId` int NOT NULL,
  `category` enum('CHOMEAN','CHOVOCA','FLASHCARDS') NOT NULL DEFAULT 'CHOMEAN',
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`testId`),
  KEY `wordBookId` (`wordBookId`),
  KEY `Tests_ibfk_1` (`userId`),
  CONSTRAINT `Tests_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `Users` (`userId`),
  CONSTRAINT `Tests_ibfk_2` FOREIGN KEY (`wordBookId`) REFERENCES `WordBooks` (`wordBookId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- WordBooks 테이블
CREATE TABLE `WordBooks` (
  `wordBookId` int NOT NULL AUTO_INCREMENT,
  `userId` varchar(100) NOT NULL,
  `wordBookTitle` varchar(255) NOT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `bookmark` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`wordBookId`),
  KEY `WordBooks_ibfk_1` (`userId`),
  CONSTRAINT `WordBooks_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `Users` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;