[공통 프로젝트] DB 설계, 필수 체크 리스트!

이번 프로젝트를 시작할 때 가장 신경을 많이 써야겠다고 생각한 부분은 바로 DB이다.

DB설계를 아주 구체적이고 효율적으로 해서 탐색시간을 줄이는 것을 목표로 두면서,

프로젝트가 끝난 후 DB에 대한 이해가 매우 늘길 바라고 있다.

 

그래서 이번 라이브강의를 매우 흥미롭게 들었고, 이번 프로젝트에 적용할 계획이다.

---

 

1. 키는 정수형 사용하기

 

: 이메일처럼 유니크한 속성을 가진 문자열을 PK로 사용이 가능하다.

그러나

• 검색이 느림 : 하나하나 순차적으로 비교해야해서 연산 비용이 증가한다.
• 조인 성능 저하 : 문자열은 정수에 비해 비교 비용이 크기 때문에 성능이 떨어진다.
• 공백 문자 인식 문제 : 공백은 눈에 잘 띄지 않기 때문에 중복 데이터 발생 혹은 충돌 가능성이 높다.
• 수정 가능성 : 만약 이메일의 경우 사용자가 수정을 하면 값이 바뀔 수 있다.

이러한 문제점이 발생할 가능성이 매우 높기 때문에 정수형의 id값을 생성하여 PK로 이용하는 것이 좋다.

 

정수형 id의 데이터 타입을 고려할 필요도 있다.

unsigned int와 bigint 중 무엇을 선택해야할까?

 

 

unsigned int

• 크기 : 4byte (0 ~ 4,294,967,295 )
• 부호가 없어서 양수만 저장이 가능하다.
• 메모리 사용량이 적고 연산이 더 빠르다.(비교, 정렬 등)
• 테이블 크기 비용이 적다.

 

bigint

• 크기 : 8byte
• 부호 있음 (signed): -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807
• 부호 없음 (unsigned): 0 ~ 18,446,744,073,709,551,615
• 더 큰 범위를 다룰 수 있다.
• 테이블 크기가 증가하고 검색 시 연산 성능이 느리다.

 

MySql의 경우 두 타입 모두 지원하고 있다.

현재 진행하는 프로젝트 정도의 사이즈의 경우 usigned int 정도의 범위로도 충분하다고 한다.

 

---

2. 실수형 범위 고려하기

 

 

만약 집면적 데이터처럼 실수형 데이터가 필요하다면? 어떤 데이터 타입을 사용하면 좋을까?

• float : 4byte
• double : 8byte
• decimal : 사용자가 범위 지정. double보다 큼

데이터 타입	크기	속도	정밀도
float	4byte	빠름	낮음
double	8byte	보통	보통
decimal	사용자 지정. double보다 큼	느림	높음

 

여기서 정밀도는 실수를 얼마나 더 정확하게 사용할 수 있는가를 나타낸다.

0.1과 0.2 사이에는 무한한 수가 있는 것처럼 실수는 무한대이다. 

 

집면적 범위는 000.00m^2 ~ 500.00m^2 정도이므로 float로도 정확하게 표현이 가능하다.

더 큰 사이즈의 데이터 타입을 선택할 필요가 없다.

 

 

2-1. GPS에 적합한 데이터형

 

 

구글맵을 보면 위도와 경도로 위치 정보를 알 수 있다. 

데이터 타입	거리오차	오차범위
float	큼	약 1미터
double	보통	약 3나노미터
decimal	작음	double보다 작음

 

1미터의 오차라면 지도상의 목적지 건물로 가는 것에 큰 영향을 주진 않으므로 float로 선택해도 된다

(근데 뭔가 생각해봤는데 로봇청소기처럼 집의 위치를 세부적으로 알아야한다면 double을 써야할 것 같기도..?)

 

 

---

3. 컬럼이 많은 테이블

 

만약 아래의 예시처럼 테이블에 많은 컬럼이 들어가게 된다면 어떻게 될까?

 

user table

CREATE TABLE `user` (
    `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '사용자 고유 ID',
    `username` VARCHAR(50) NOT NULL COMMENT '사용자 이름',
    `password` VARCHAR(255) NOT NULL COMMENT '암호 (해시 처리됨)',
    `email` VARCHAR(100) NOT NULL COMMENT '이메일 주소',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT '전화번호',
    `profile_picture` TEXT DEFAULT NULL COMMENT '프로필 사진 URL',
    `bio` TEXT DEFAULT NULL COMMENT '사용자 소개글',
    `gender` ENUM('M', 'F', 'O') DEFAULT NULL COMMENT '성별 (M: 남성, F: 여성, O: 기타)',
    `date_of_birth` DATE DEFAULT NULL COMMENT '생년월일',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '주소',
    `city` VARCHAR(100) DEFAULT NULL COMMENT '도시',
    `state` VARCHAR(100) DEFAULT NULL COMMENT '주/도',
    `postal_code` VARCHAR(20) DEFAULT NULL COMMENT '우편번호',
    `country` VARCHAR(100) DEFAULT NULL COMMENT '국가',
    `last_login` DATETIME DEFAULT NULL COMMENT '마지막 로그인 시간',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '계정 생성 시간',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '계정 수정 시간',
    `status` ENUM('ACTIVE', 'INACTIVE', 'BANNED') DEFAULT 'ACTIVE' COMMENT '계정 상태',
    `role` ENUM('USER', 'ADMIN', 'MODERATOR') DEFAULT 'USER' COMMENT '사용자 권한',
    `preferences` JSON DEFAULT NULL COMMENT '사용자 설정 (JSON 형식)',
    `newsletter_opt_in` BOOLEAN DEFAULT TRUE COMMENT '뉴스레터 수신 여부',
    `loyalty_points` INT UNSIGNED DEFAULT 0 COMMENT '사용자 포인트',
    `social_links` JSON DEFAULT NULL COMMENT '소셜 미디어 링크 (JSON 형식)',
    `theme` ENUM('LIGHT', 'DARK') DEFAULT 'LIGHT' COMMENT '사용자 테마 선호',
    `login_attempts` INT UNSIGNED DEFAULT 0 COMMENT '로그인 시도 횟수',
    `account_locked_until` DATETIME DEFAULT NULL COMMENT '계정 잠김 해제 시간',
    `security_question` VARCHAR(255) DEFAULT NULL COMMENT '보안 질문',
    `security_answer` VARCHAR(255) DEFAULT NULL COMMENT '보안 질문 답변 (해시 처리됨)',
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_email` (`email`),
    INDEX `idx_status` (`status`),
    INDEX `idx_last_login` (`last_login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='사용자 정보 테이블';

 

chat gpt에게 컬럼이 많은 user 테이블을 작성해달라고 부탁해서 받은 결과이다.

정말 매우 복잡하고 많다. 

 

다음은 위의 user 테이블을 

 

• users: 기본적인 사용자 계정 관리 정보를 저장.
• user_profiles: 프로필 및 추가 정보를 저장.

이렇게 두 테이블로 나눠서 1대 1 관계로 다시 생성해달라고 요청했다.

 

 

users table

CREATE TABLE `users` (
    `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '사용자 고유 ID',
    `username` VARCHAR(50) NOT NULL COMMENT '사용자 이름',
    `password` VARCHAR(255) NOT NULL COMMENT '암호 (해시 처리됨)',
    `email` VARCHAR(100) NOT NULL COMMENT '이메일 주소',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT '전화번호',
    `status` ENUM('ACTIVE', 'INACTIVE', 'BANNED') DEFAULT 'ACTIVE' COMMENT '계정 상태',
    `role` ENUM('USER', 'ADMIN', 'MODERATOR') DEFAULT 'USER' COMMENT '사용자 권한',
    `last_login` DATETIME DEFAULT NULL COMMENT '마지막 로그인 시간',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '계정 생성 시간',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '계정 수정 시간',
    `login_attempts` INT UNSIGNED DEFAULT 0 COMMENT '로그인 시도 횟수',
    `account_locked_until` DATETIME DEFAULT NULL COMMENT '계정 잠김 해제 시간',
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `uk_username` (`username`),
    UNIQUE KEY `uk_email` (`email`),
    INDEX `idx_status` (`status`),
    INDEX `idx_last_login` (`last_login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='사용자 계정 테이블';

 

 

user_profiles table

CREATE TABLE `user_profiles` (
    `profile_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '프로필 고유 ID',
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '사용자 고유 ID (FK)',
    `profile_picture` TEXT DEFAULT NULL COMMENT '프로필 사진 URL',
    `bio` TEXT DEFAULT NULL COMMENT '사용자 소개글',
    `gender` ENUM('M', 'F', 'O') DEFAULT NULL COMMENT '성별 (M: 남성, F: 여성, O: 기타)',
    `date_of_birth` DATE DEFAULT NULL COMMENT '생년월일',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '주소',
    `city` VARCHAR(100) DEFAULT NULL COMMENT '도시',
    `state` VARCHAR(100) DEFAULT NULL COMMENT '주/도',
    `postal_code` VARCHAR(20) DEFAULT NULL COMMENT '우편번호',
    `country` VARCHAR(100) DEFAULT NULL COMMENT '국가',
    `preferences` JSON DEFAULT NULL COMMENT '사용자 설정 (JSON 형식)',
    `newsletter_opt_in` BOOLEAN DEFAULT TRUE COMMENT '뉴스레터 수신 여부',
    `loyalty_points` INT UNSIGNED DEFAULT 0 COMMENT '사용자 포인트',
    `social_links` JSON DEFAULT NULL COMMENT '소셜 미디어 링크 (JSON 형식)',
    `theme` ENUM('LIGHT', 'DARK') DEFAULT 'LIGHT' COMMENT '사용자 테마 선호',
    `security_question` VARCHAR(255) DEFAULT NULL COMMENT '보안 질문',
    `security_answer` VARCHAR(255) DEFAULT NULL COMMENT '보안 질문 답변 (해시 처리됨)',
    PRIMARY KEY (`profile_id`),
    UNIQUE KEY `uk_user_id` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='사용자 프로필 테이블';

 

이렇게 같은 데이터를 두 테이블로 나눌 수도 있다.

 

두개의 방식 중 무엇을 선택하는가에는 정답은 없고 필요한 것을 사용하면 된다고 한다.

만약 전체의 데이터를 다 불러와서 자주 사용하는 기능이 있다면, 한 테이블에 모든 데이터를 담는 것을 좋다.

그러나 users 테이블 내에서 자주 사용하는 정보가 있고, user_profiles에서 자주 사용하는 정보가 따로 있는데 가끔 함께 사용해야하는 경우라면 가끔 두 테이블을 조인해서 사용하면 되는 것이니 상황에 따라 원하는 대로 나누면 되겠다.

 

 

 

---

결론

 

-> 프로젝트 시 pk를 id로 생성해 unsigned int형 정수 사용하기

-> 실수형 데이터 사용 시 float 선택하기

-> 테이블을 사용하는 데이터를 잘 보고 나누기