데이터 베이스 설계

개발 일지/데이터베이스

데이터 베이스 설계

DDD Developer 2024. 9. 6. 15:16

728x90

DB 설계 과정

◆ 요구사항 분석

: 어떤 데이터를 저장할지, 어떤 식의 관계를 형성할지에 대해서 사전 작
성한다. 최초 요구사항의 경우 다소 사람이 이해하기 쉬운 형태이며, 명
확한 Datatype등이 명시되어있지 않으므로 이에 대해서 명확히 해야한다.

◆ 개념적 설계

: 요구사항에 따라 개념적인 설계를 한다. 요구사항 분석으로 만들어진
데이터들을 관계나 실제 어떠한 데이터타입으로 할지 등 개념적으로 설계

◆ 논리적 설계

: 실제 DBMS에서 동작할 수 있도록 짜 만드는 것

◆ 요구사항 분석

● 필요로 할 데이터들의 형태와 필요한 수준에 대해서 일반인이 이해할 수있을 언어로 적은 요구사항을 받아와 분석한다.

➢“고객 정보(이름, 전화번호, 주소)를 저장한다.”

➢“제품 정보(이름, 가격, 개수)를 저장한다.”

➢“이 두 정보를 이용하여 하나의 판매에 대한 정보를 저장한다.”

➢“이 때, 거래 상태와 거래일자에 대한 정보를 저장한다.”

➢“거래 상태는 거래 상태가 변화함에 따라 수정될 수 있다.”

● 다소 정확하지 않은 방식의 요구사항에 대해서 개발자는 필요한 정보를 추가 요구하거나 이에 대한 분석 결과를 내야한다.

● 추가 요구

➢“고객명, 전화번호, 고객주소의 경우 각각 최대 몇글자까지 허용할 것인가?”

>> “고객명 15글자, 전화번호 17글자, 고객주소는 일반적으로 외국명까지 가늠해서 해주세요.”

➢“제품명의 최대길이와 제품가격과 제품개수의 상한선이 있는가?”
>> “제품명은 100글자 내로, 제품가격과 제품개수는 크게 상관 없으나 거래가주로 1억 내의 물건만 취급한다.”

➢“거래 상태에 대한 정보는 몇단계인가?”
>> “거래 상태는 ‘결제완료’, ‘준비중’, ‘전달완료‘, ‘배달중‘ ,’배달완료‘, ‘거래완료‘로 한다. 추가로 ‘환불’,

‘배달실패‘와 같은 경우도 존재한다. 다만, 경우에 따라 상태가 추가될 수 있다.”

➢“거래 일시는 어떤 상태를 기준으로 책정하는가?”
>> “거래 일시는 결제 완료를 기준으로 한다. 다만, 환불이나 배달실패의 경우해당 시간으로 변경될 수 있다.”

● 요구사항 세부 분석

➢고객정보는 고객명은 가변적이므로 varchar 15글자, 전화번호는 가변적일 수있으니 varchar 17글자, 주소의 경우 일반적으로 255글자를 넘기는 경우가 적으며, 가변적이므로 varchar(255), 고객정보를 식별가능하도록 찾기 위해서 ID값이 필요하므로 이를 int로 추가(세계인구수 고려, 필요시 biging로 변경가능)

➢제품정보는 제품명은 가변적이므로 varchar 100글자, 제품가격은 1억 미만이므로 적당한 int값을 사용, 제품개수 또한 일반적으로 int를 적용, 제품정보를 식별가능하도록 찾기 위해서 ID값이 필요하므로 이를 int로 추가(제품의 종류
를 고려해도 int값을 넘기기 힘드므로)

➢거래정보는 고객정보와 제품정보를 가져와 관계를 형성하고 이를 위해 ID를 외래키로 가지도록 하며, 거래상태는 상태 추가를 고려하여 smallint로 적당히 선정, 거래일시는 datetime으로 선정

순서도 및 ERD 제작 사이트

◆ https://draw.io

◆ 개념적 데이터 모델링

● Entity(엔티티, 객체)와 Relation(릴레이션, 관계)로 이루어진 다이어그램

● 객체와 관계는 Attribute(어트리뷰트, 속성)을 가진다.(단, 관계는 속성을가지지 않아도 된다.)

● 객체는 사각형으로 표현하고 관계는 마름모꼴로 표현한다.

● 선을 통해 객체나 속성, 관계를 연결한다.

● 타원형은 속성이다.

● 속성은 객체에서 식별할 수 있는 고유 키값을 가지는데 이를 기본키라고부른다.

● 속성 중 키에 해당하는 속성은 밑줄을 그어 표현할 수 있다.

● 명확하고 정확한 표현보다는 개념적인 이해를 중심으로 다이아그램을 통해서 이해하는 것을 목적으로 한다.

◆ 논리적 데이터 모델

● 실제 DBMS로 만들 수 있는 형태로서 제작

● 각 사각형은 테이블을 의미한다.

● 테이블 내부의 좌측은 키에 대한 정보를 입력한다.

● 우측은 속성명과 속성의 데이터 타입, 제약조건 등을 적는다.

● 각 속성은 다른 테이블의 속성과 관계를 나타내는 선을 그릴 수 있다.

● 선의 형태에 따라 그 관계를 표현할 수 있다. 유형은 다음과 같다

◆ DB

만들어진 내용을 실제 DB로 운영하기 위하여 DB로 작성

●CREATE DATABASE SHOP; // USE SHOP;

●CREATE TABLE CLIENT (C_ID INT, C_NAME VARCHAR(15) NOT NULL, C_PN
VARCHAR(17) NOT NULL UNIQUE, C_ADDR VARCHAR(255) NOT NULL,
CONSTRAINT PK_CLIENT PRIMARY KEY (C_ID));

●CREATE TABLE PRODUCT (P_ID INT, P_NAME VARCHAR(100) NOT NULL, P_PRICE
INT NOT NULL, P_COUNT INT NOT NULL,
CONSTRAINT PK_PRODUCT PRIMARY KEY (P_ID));

●CREATE TABLE ACCOUNT (A_ID INT, C_ID INT NOT NULL, P_ID INT NOT NULL,
A_STATE SMALLINT NOT NULL, A_DATE DATETIME NOT NULL,CONSTRAINT PK_ACCOUNT PRIMARY KEY (A_ID),

CONSTRAINT FK_ACCOUNT_C FOREIGN KEY (C_ID) REFERENCES CLIENT(C_ID),
CONSTRAINT FK_ACCOUNT_P FOREIGN KEY (P_ID) REFENECES PRODUCT(P_ID));

예시

●  구조적인 설계를 해보기 위해서 예제를 만들어봄.
●  학생이 수강신청하는 DB를 만듦
●  요구사항은 다음과 같다.
●  “학생 정보는 이름, 성별, 나이, 전공, 학년, 기숙사 여부로 선정한다.”
●  “교수 정보는 이름, 성별, 나이, 전공, 교수실호실로 선정한다.”
●  “수강 정보는 수강명, 대상 학생, 교수 정보, 교실, 수업시간으로 한다.”
● 굳이 테이블 개수가 3개일 필요는 없다.
● 필요한 속성은 추가해도 상관 없다.
● 요구사항에 있는 속성은 무조건 구현되어야 한다.

◆ 순서도

◆ 논리적 데이터 모델

● 실제 DBMS로 만들 수 있는 형태로서 제작

◆ 테이블 설계

학생 테이블 (Students)

학생ID (StudentID) - 기본키
이름 (Name)
성별 (Gender)
나이 (Age)
전공 (Major)
학년 (Year)
기숙사 여부 (Dormitory) - Y/N

교수 테이블 (Professors)

교수ID (ProfessorID) - 기본키
이름 (Name)
성별 (Gender)
나이 (Age)
전공 (Major)
교수실호실 (OfficeRoom)

수강 테이블 (Signclass)

수강ID (EnrollmentID) - 기본키
수강명 (CourseName)
학생ID (StudentID) - 외래키
교수ID (ProfessorID) - 외래키
교실 (Classroom)
수업시간 (ClassTime)

데이터베이스 생성

CREATE DATABASE Csystem;
USE Csystem;

CREATE DATABASE Csystem ;
Csystem라는 이름의 데이터베이스를 생성.
USE Csystem;
생성한 데이터베이스를 사용하겠다는 명령.

학생 테이블 생성

CREATE TABLE Students (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Gender ENUM('남','여') NOT NULL,
    Age INT CHECK (Age > 0),
    Major VARCHAR(50) NOT NULL,
    Year VARCHAR(50) NOT NULL,
    Dormitory BOOL NOT NULL
);

CREATE TABLE Students: Students라는 이름의 테이블을 생성.
StudentID INT AUTO_INCREMENT PRIMARY KEY: 학생 ID를 정수형으로 설정하고, 자동으로 증가하며 기본 키로 설정
Name VARCHAR(50) NOT NULL: 학생의 이름을 최대 50자의 문자열로 저장하며, 필수 입력.
Gender ENUM('남', '여') NOT NULL: 성별을 '남' 또는 '여' 중 하나로 저장하며, 필수 입력.
Age INT CHECK (Age > 0): 나이를 정수형으로 저장하며, 0보다 큰 값만 허용.
Major VARCHAR(50) NOT NULL: 전공을 최대 50자의 문자열로 저장하며, 필수 입력.
Year VARCHAR(50) NOT NULL : 학년을 최대 50자의 문자열로 저장하며, 필수 입력
(필자는 LMS4차반이기 때문에 정수형 안썼음)
Dormitory BOOL NOT NULL: 기숙사 여부를 true와 false으로 저장하며, 필수 입력.

교수 테이블 생성

CREATE TABLE Professors (
    ProfessorID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Gender ENUM('남', '여') NOT NULL,
    Age INT CHECK (Age > 0),
    Major VARCHAR(50) NOT NULL,
    OfficeRoom VARCHAR(20) NOT NULL
);

CREATE TABLE Professors: Professors라는 이름의 테이블을 생성.
ProfessorID INT AUTO_INCREMENT PRIMARY KEY: 교수 ID를 정수형으로 설정하고, 자동으로 증가하며 기본 키로 설정.
학생 테이블과 유사하나 교수의 전공과 사무실 호실이 포함.

수강 테이블 생성

CREATE TABLE Signclass (
    SignclassID INT AUTO_INCREMENT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    StudentID INT,
    ProfessorID INT,
    Classroom VARCHAR(20) NOT NULL,
    ClassTime VARCHAR(20) NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (ProfessorID) REFERENCES Professors(ProfessorID)
);

CREATE TABLE Signclass : Signclass 라는 이름의 테이블을 생성.
SignclassID INT AUTO_INCREMENT PRIMARY KEY: 수강 ID를 정수형으로 설정하고, 자동으로 증가하며 기본 키로 설정.
CourseName VARCHAR(100) NOT NULL: 수업명을 최대 100자의 문자열로 저장하며, 필수 입력.
StudentID INT: 수강하는 학생의 ID를 저장.
ProfessorID INT: 수업을 담당하는 교수의 ID를 저장.
Classroom VARCHAR(20) NOT NULL: 수업이 진행되는 교실 정보를 저장.
ClassTime VARCHAR(20) NOT NULL: 수업 시간 정보를 저장.
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
: StudentID가 Students 테이블의 StudentID와 연결되어 있음을 나타냄(외래키).
FOREIGN KEY (ProfessorID) REFERENCES Professors(ProfessorID)
: ProfessorID가 Professors 테이블의 ProfessorID와 연결되어 있음을 나타냄(외래키).

학생 데이터 삽입

INSERT INTO Students (Name, Gender, Age, Major, Year, Dormitory) 
VALUES ('박성민', '남', 29, '인공지능 기반 서비스플랫폼', 'LMS4차', 0);

INSERT INTO Students (...) VALUES (...): Students 테이블에 새로운 학생 정보를 추가.

교수 데이터 삽입

INSERT INTO Professors (Name, Gender, Age, Major, OfficeRoom) 
VALUES ('이복녘', '남', 99, '인공지능 기반 서비스플랫폼', '303호');

교수 정보를 Professors 테이블에 추가하는 명령.

수강 정보 삽입

INSERT INTO Signclass (CourseName, StudentID, ProfessorID, Classroom, ClassTime) 
VALUES ('자료구조', 1, 1, '303호', '금 9:00');

수강 정보를 Signclass 테이블에 추가하는 명령.
여기서 StudentID와 ProfessorID는 이미 존재하는 학생과 교수의 ID.

학생 테이블 확인

SELECT * FROM Students;

Students 테이블의 모든 데이터를 조회.

교수 테이블 확인

SELECT * FROM Professors;

Professors 테이블의 모든 데이터를 조회.

수강 테이블 확인

SELECT * FROM Signclass;

Signclass 테이블의 모든 데이터를 조회.

ENUM 사용 장단점

장점

데이터 용량을 적게 차지한다. 문자열은 자동적으로 숫자로 인코딩되어 저장되기 때문이다.(쿼리 결과에서 저장된 숫자는 대응되는 문자열로 변환)
'medium'이라는 값 100만개를 VARCHAR로 저장하려면 600만 bytes가 필요하지만 enum은 100만 byte만 필요하다.
데이터의 정합성을 높일 수 있다. (유효하지 않은 값은 무효화되므로)

단점

ENUM 컬럼에 대해 order by를 하면 추가적인 비용이 든다.
ENUM 항목 변경하는 작업이 수고롭다. 스키마 변경을 해야하고 삭제된 항목의 값을 가지는 컬럼에 대한 처리도 필요하다.(그냥 두면 무효화되므로 경우에 따라 마이그레이션 필요할 수도)
Enum 컬럼을 다른 테이블 FK로 사용한 경우 다른 테이블들까지 영향을 받게 된다.
다른 DBMS로 이식성이 안좋다. (다른 DBMS는 ENUM 타입이 없으므로)

728x90