[DB] 데이터 베이스 설계

데이터베이스 설계는 데이터를 효율적으로 저장, 관리, 검색할 수 있도록 구조화 하는 과정을 의미한다.

Analysis, 요구 사항 분석

시스템 혹은 기능을 이해한다.

데이터베이스 설계의 첫 단계는 시스템의 요구 사항을 이해하는 것부터 시작한다.

요구 사항이라 함은 구축하려는 서비스 혹은 기능에서 사용자는 어떤 데이터를 주고 받을 것이고, 어떤 동작을 수행할 것인지, 어떤 결과가 도출되여야 하는지 등 목적 달성을 위한 정보를 의미할 것이다.

고객이 상품을 구매하는 요구 사항을 기반으로 예시를 들겠다.

• 고객이 상품을 검색하고 주문할 수 있어야 한다.

• 판매자는 상품을 등록하고 관리할 수 있어야 한다.

• 관리자는 주문 내역을 확인하고 관리할 수 있어야 한다.

앞으로의 예시는 위의 예시를 기반으로 진행하겠다.

Conceptual Design, 개념적 설계

키워드를 도출하여 관계를 정의한다.

이해한 요구 사항에서 키워드를 추출해내는 과정이라 할 수 있다.

먼저 요구 사항 이해에서 예시로 들었던 고객이 상품을 구매하는 상황에서의 키워드는

• 행위 : 검색, 구매, 등록, 관리, 확인

• 데이터 : 고객, 상품, 판매자, 관리자, 주문, 주문 내역

와 같이 키워드를 추출할 수 있을 것이다.

물론 실무에서 사용할 정도는 아니기에 간단하게 키워드만 추출하고 고객의 상품을 주문하는 시점에서 필요한 요소로만 예시를 들겠다.

추출한 키워드를 행위와 데이터로 구분하고 이를 ERD(Entity-Relationship Diagram)등과 같이 시각적으로 나타내기 위해 엔티티, 속성, 관계를 구성해야한다.

Entity, 엔티티

분리한 데이터를 객체의 형태로 변환하여 실직적으로 저장되는 주요 데이터 객체를 의미한다.

실질적으로 저장되는 이라는 구문을 다시 해석하자면 유지하고 싶은 정보들을 의미할 것이다.

• 고객(Customer)
• 상품(Product)
• 주문(Order)
• 주문 상세(Order Detail)

Attribute, 속성

Entity 객체의 상세 요소들을 의미한다.

• 고객: 고객ID, 이름, 이메일, 주소(CustomerID, Name, Email, Address)
• 상품: 상품ID, 이름, 가격, 재고(ProductID, Name, Price, Stock)
• 주문: 주문ID, 주문일, 고객ID(OrderID, OrderDate, CustomerID)
• 주문 상세: 주문ID, 상품ID, 수량, 총 가격(OrderID, ProductID, Quantity, TotalPrice)

ERD를 작성하기 위해서 자료형 또한 생각하는게 좋다.

참고로 주문 상세에서 OrderID, ProductID는 복합키로 활용할 예정이다. 복합키 외에도 OrderDetailID를 따로 둘 수도 있다.

Relation, 관계

엔티티 간의 연관성 혹은 관계를 의미한다.

관계는 크게

• One-to-one (1:1) : 엔티티 인스턴스 하나가 다른 엔티티 인스턴스 하나와 직접적으로 연결된다.
• One-to-many (1:M) : 엔티티 인스턴스 하나가 다른 엔티티 인스턴스 여러개와 연결된다.
• Many-to-many (M:N) : 엔티티 인스턴스 여러개가 다른 엔티티 인스턴스 여러개와 연결된다.

가 있다.

• 고객은 여러 주문을 할 수 있다. (1:N)
• 주문은 여러 주문 상세를 포함한다. (1:N)
• 주문 상세는 하나의 상품과 연결된다. (N:1)

Logical Design, 논리적 설계

데이터베이스 적용을 위해 구조를 변환한다.

개념적으로 설계한 요소들을 데이터베이스에 적용하기 위한 구조로 변환 시킨다.

Normalization, 정규화

데이터를 중복 없이 효율적으로 저장하면서 데이터 무결성을 보장하기 위해 데이터베이스 테이블을 구조화한다.

먼저 분리가 되지 않은 초기 테이블에서부터 시작해보자.

물론 개념적 설계를 토대로라면 아래와 같은 예시처럼 정돈되지 않은 형태는 나오기 힘들 것이다.

OrderID	OrderDate	CustomerID	CustomerName	CustomerAddress	CustomerEmail	Products	TotalPrice
1	2024-07-22	101	John Doe	123 Main St	john@example.com	201:Laptop:1:1000:50	1000
2	2024-07-23	102	Jane Smith	456 Elm St	jane@example.com	202:Smartphone:2:250:100, 203:Headphones:1:100:200	500, 100

1차 정규화

각 컬럼(속성)이 하나의 값(원자값)을 갖도록 보장한다.

이 단계에서는 컬럼 내부에서 중복 데이터가 존재할 수 있다.

초기 테이블에는 구매하는 상품들(Products)을 202:Smartphone:2:500, 203:Headphones:1:100와 총 가격(TotalPrice) ` 500,100` 와 같이 하나로 표현하여 구분 짓기도 어렵다.

또한 ,로 구분되어 2개 이상의 정보를 표현하고 있는데 이는 원자값이라 할 수 없다.

상품 구분을 위해서 각 컬럼이 원자 값을 가지도록 아래와 같이 Products를 ProductID, ProductName, Quantity, Price, Stock 의 컬럼으로 구분지어 다중 값을 개별 행으로 분리하자.

OrderID	OrderDate	CustomerID	CustomerName	CustomerAddress	CustomerEmail	ProductID	ProductName	Quantity	Price	Stock	TotalPrice
1	2024-07-22	101	John Doe	123 Main St	john@example.com	201	Laptop	1	1000	50	1000
2	2024-07-23	102	Jane Smith	456 Elm St	jane@example.com	202	Smartphone	2	250	100	500
3	2024-07-23	102	Jane Smith	456 Elm St	jane@example.com	203	Headphones	1	100	200	100

2차 정규화

기본 키에 완전 종속적인 속성만 남긴다.(부분 함수 종속 제거)

기본 키의 일부에만 종속된 속성을 제거하여 별도의 테이블로 분리한다.

주문 테이블에 고객 데이터가 포함되어있다면, 주문 테이블과 고객 테이블을 분리한 뒤 주문 테이블에는 고객 테이블의 PK값 즉, 고객 ID만 포함시킨다.

고객 테이블 (Customer Table):

CustomerID	CustomerName	CustomerEmail	CustomerAddress
101	John Doe	john@example.com	123 Main St
102	Jane Smith	jane@example.com	456 Elm St

주문 테이블 (Order Table):

OrderID	OrderDate	CustomerID
1	2024-07-22	101
2	2024-07-23	102

같은 고객이 여러개의 주문을 수행했을 경우에는 Order는 하나로 Group화하고, 복합키를 사용한 OrderDetail 테이블에 OrderID 2와 ProductID로 구분을 지을 수 있다.

주문 상세 테이블 (Order Detail Table):

OrderID	ProductID	Quantity	ProductName	Price	Stock	TotalPrice
1	201	1	Laptop	1000	50	1000
2	202	2	Smartphone	250	100	500
2	203	1	Headphones	100	200	100

3차 정규화

이행적 종속을 없앤다.

이행적 종속이라함은 A->B, B->C 일 때 A->C가 성립 되는 것을 의미한다.

즉, 기본키가 아닌 속성 간의 종속성을 제거하는 것이다.

주문 상세 테이블에서 제품 정보를 포함하고 있다면, 제품 정보를 별도의 제품 테이블로 분리하여 주문 테이블에는 제품 ID만 포함시킨다.

주문 테이블 (Order Table)

OrderID	OrderDate	CustomerID
1	2024-07-22	101
2	2024-07-23	102

고객 테이블 (Customer Table):

CustomerID	CustomerName	CustomerEmail	CustomerAddress
101	John Doe	john@example.com	123 Main St
102	Jane Smith	jane@example.com	456 Elm St

주문 상세 테이블 (Order Detail Table):

OrderID	ProductID	Quantity	TotalPrice
1	201	1	1000
2	202	2	500
2	203	1	100

제품 테이블 (Product Table):

ProductID	ProductName	Price	Stock
201	Laptop	1000	50
202	Smartphone	250	100
203	Headphones	100	200

위의 예시는 모든 테이블의 기본 키가 결정자로 작동하여 다른 속성들을 결정할 수 있기에 3차 정규화의 특수한 형태인 보이스-코드 정규화(BCNF)를 만족한다.

보통은 1~3차까지 정규화를 마쳤다면 충분하지만 복잡한 데이터 구조를 관리하는 경우나 성능을 위해서 BCNF와 4, 5차 정규화 혹은 오히려 정규화를 진행하지 않을 수도 있다.

스키마 정의

개념적 설계에서 정의한 엔티티와 속성을 그리고 정규화를 마치고 난 뒤의 도출물을 고려하여 테이블과 컬럼을 어떻게 구성할 것인지 정의한다.

• 고객 테이블: Customer(CustomerID, Name, Email, Address)

• 상품 테이블: Product(ProductID, Name, Price, Stock)

• 주문 테이블: Order(OrderID, OrderDate, CustomerID)

• 주문 상세 테이블: OrderDetail(OrderID, ProductID, Quantity, Price)

와 같이 말이다.

Physical Design, 물리적 설계

논리적 설계를 실제 사용하는 데이터베이스에 맞게 변환하며, 효율적으로 데이터를 저장하고 관리할 수 있도록 구체적인 스키마를 정의한다.

테이블 설계

논리적 설계에서 정의한 테이블을 실제 DB에 생성한다.

DDL을 통해 테이블의 이름과, 제약 조건, PK, FK 등을 설정하여 테이블을 생성한다.

제약 조건에 관한 자세한 내용은 밑에서 다루겠다.

주문 테이블 (Order Table)

CREATE TABLE `Order` (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

고객 테이블 (Customer Table)

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(20),
    Email VARCHAR(50) UNIQUE,
    Address VARCHAR(200)
);

주문 상세 테이블 (Order Detail Table)

CREATE TABLE OrderDetail (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    TotalPrice DECIMAL(10, 2),
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES `Order`(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

제품 테이블 (Product Table)

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(20),
    Price DECIMAL(10, 2),
    Stock INT
);

인덱스 설정

데이터 검색 속도를 높이기 위해 인덱스를 설정할 수 있다.

CREATE INDEX idx_customer_email ON Customer(Email);
CREATE INDEX idx_order_customer ON `Order`(CustomerID);
CREATE INDEX idx_orderdetail_product ON OrderDetail(ProductID);

인덱스를 설정하게 되면 컬럼 혹은 테이블 따위에 추가적으로 정렬된 데이터를 넣어줘 검색 속도를 높인다.

하지만 인덱스가 많아지면 삽입, 삭제, 업데이트 작업이 느려질 수 있으니 자주 조회되는 열, 조인 열, 중복이 적은 열, 쓰기 작업 빈도, 인덱스 크기 등을 고려하여 적절하게 설계해야한다.

기본적으로 기본 키 인덱스가 설정되는데 이는 기본 키로 지정한 열에 자동으로 생성되는 인덱스이며 각 행의 고유성을 보장해준다.

이외에도 복합 인덱스, 클러스터드 인덱스, 부분 인덱스 등 여러가지 인덱스 설정 방법이 있다.

뷰 설계

자주 사용하는 쿼리나 복잡한 쿼리를 단순화하기 위해 View를 생성할 수 있다.

뷰는 일종의 가상 테이블로 생성 시 실제 데이터가 저장되진 않는다.

뷰를 통해 데이터에 접근할 때마다 뷰에 정의한 쿼리가 실행된다.

CREATE VIEW OrderSummary AS
SELECT 
    o.OrderID, 
    o.OrderDate, 
    c.Name AS CustomerName, 
    c.Email AS CustomerEmail, 
    p.Name AS ProductName, 
    od.Quantity, 
    od.Price
FROM `Order` o
JOIN Customer c ON o.CustomerID = c.CustomerID
JOIN OrderDetail od ON o.OrderID = od.OrderID
JOIN Product p ON od.ProductID = p.ProductID;

사용 시에는

SELECT * FROM OrderSummary;

와 같이 사용 가능하며 뷰를 통한 데이터 업데이트 또한 가능하다.

UPDATE SimpleOrderSummary
SET OrderDate = '2024-07-23'
WHERE OrderID = 1;

하지만 모든 뷰에서 업데이트가 가능한 것은 아니기에 특정 조건을 만족해야한다.

뷰를 사용함으로써

• 쿼리의 단순화.
• 보안
• 데이터 무결성 등의 이점을 얻을 수 있다.

제약 조건 설정

데이터 베이스의 무결성을 유지하는데 중요한 역할을 한다.

잘못된 데이터 입력을 방지하고, 데이터 일관성을 유지시켜준다.

또한 데이터베이스 구조와 데이터의 관계를 명확히 정의할 수 있다.

기본 키 (Primary Key)

테이블에서 각 행을 고유하게 식별하는 유일한 키를 설정한다.

중복, NULL 값을 허용하지 않는다.

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(20),
    Email VARCHAR(50) UNIQUE,
    Address VARCHAR(200)
);

외래 키 (Foreign Key)

다른 테이블의 기본 키를 참조하는 열.

테이블 간의 관계를 정의하며 참조 데이터 무결성을 보장할 수 있다.

CREATE TABLE `Order` (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

고유 제약 조건 (Unique Constraint)

특정 열의 값이 중복 되지 않고 고유하도록 설정한다.

기본 키와 달리 여러개의 고유 제약 조건을 설정할 수 있다.

DBMS 마다 다를 수 있지만 NULL 값을 허용한다.

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(20),
    Email VARCHAR(50) UNIQUE,
    Address VARCHAR(200)
);

체크 제약 조건 (Check Constraint)

열의 값이 특정 조건을 만족하도록 설정한다.

데이터 무결성을 보장하기 위한 조건식을 설정할 수 있다.

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(50),
    Price DECIMAL(10, 2),
    Stock INT,
    CHECK (Price > 0),
    CHECK (Stock >= 0)
);

NOT NULL 제약 조건

열의 값이 NULL이 될 수 없도록 설정한다.

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Email VARCHAR(50) NOT NULL,
    Address VARCHAR(200)
);

기본값 (Default Constraint)

열의 데이터를 입력하지 않았을 때의 기본값을 설정한다.

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(50),
    Price DECIMAL(10, 2) DEFAULT 0.0,
    Stock INT DEFAULT 0
);

저장 프로시저 및 트리거 설계

데이터베이스 작업을 자동화하거나 특정 조건에서 실행되는 로직을 정의할 수 있다.

저장 프로시저 (Stored Procedure)

특정 작업을 수행하기 위해 미리 컴파일된 SQL 코드의 집합이다.

여러개의 SQL문을 포함할 수 있고, 매개 변수를 받아 동적으로 실행할 수도 있다.

이로써 반복 작업의 자동화, 보안 강화, 유지보수 용이 등의 이점을 가질 수 있다.

DELIMITER //

CREATE PROCEDURE InsertOrder(
    IN p_OrderDate DATE,
    IN p_CustomerID INT,
    IN p_ProductID INT,
    IN p_Quantity INT,
    IN p_Price DECIMAL(10, 2)
)
BEGIN
    DECLARE v_OrderID INT;
    
    -- 주문 테이블에 새 주문 삽입
    INSERT INTO `Order` (OrderDate, CustomerID) VALUES (p_OrderDate, p_CustomerID);
    
    -- 마지막 삽입된 주문 ID 가져오기
    SET v_OrderID = LAST_INSERT_ID();
    
    -- 주문 상세 테이블에 주문 항목 삽입
    INSERT INTO OrderDetail (OrderID, ProductID, Quantity, Price)
    VALUES (v_OrderID, p_ProductID, p_Quantity, p_Price);
    
    -- 재고 업데이트
    UPDATE Product
    SET Stock = Stock - p_Quantity
    WHERE ProductID = p_ProductID;
END //

DELIMITER ;

작성한 프로시저는 아래와 같이 CALL 예약어를 통해 실행 가능하다.

CALL InsertOrder('2024-07-22', 101, 201, 1, 1000.00);

트리거(Trigger)

INSERT, UPDATE, DELETE 등과 같은 이벤트가 발생할 때 자동으로 실행되는 SQL 코드를 작성할 수 있다.

트리거는 자동으로 로그를 기록하거나, 데이터 변경을 감지하는데 유용하다.

DELIMITER //

CREATE TRIGGER AfterOrderDetailInsert
AFTER INSERT ON OrderDetail
FOR EACH ROW
BEGIN
    UPDATE Product
    SET Stock = Stock - NEW.Quantity
    WHERE ProductID = NEW.ProductID;
END //

DELIMITER ;

트리거는 따로 실행 명령어를 수행하지 않아도 OrderDetail 테이블에 데이터가 삽입 된 후에 자동으로 동작이 수행된다.

프로시저와 트리거를 설계할 때는 불필요한 시기에 실행되어 성능이 저하될 수도 있고, 복잡한 로직으로 인해 유지 보수성이 떨어질 수도 있다.

구현 및 테스트

앞서 설계된 논리적 구조와 물리적 설계를 실제 DBMS에 적용하고 데이터를 입력한다.

테이블을 생성하고, 제약 조건과 인덱스 등을 설정하며, 필요한 프로시저와 트리거를 구현한다.

위에서 작성한 결과를 최종적으로 DDL(Data Definition Language)로 작성하고 적용하자.

데이터베이스 생성 및 테이블 정의

-- 데이터베이스 생성
CREATE DATABASE EcommerceDB;

-- 데이터베이스 사용
USE EcommerceDB;

-- Customer 테이블 생성
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(20),
    Email VARCHAR(50) UNIQUE,
    Address VARCHAR(200)
);

-- Order 테이블 생성
CREATE TABLE `Order` (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

-- OrderDetail 테이블 생성
CREATE TABLE OrderDetail (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    TotalPrice DECIMAL(10, 2),
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES `Order`(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Product 테이블 생성
CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(20),
    Price DECIMAL(10,2),
    Stock INT
);

이후 제약 조건 및 인덱스, 프로시저, 트리거는 필요에 따라 적용하고, 데이터를 입력한다.

데이터 입력

-- Customer 데이터 삽입
INSERT INTO Customer (CustomerID, Name, Email, Address) VALUES
(1, 'John Doe', 'john@example.com', '123 Main St'),
(2, 'Jane Smith', 'jane@example.com', '456 Elm St');

-- Product 데이터 삽입
INSERT INTO Product (ProductID, Name, Price, Stock) VALUES
(1, 'Laptop', 1000.00, 50),
(2, 'Smartphone', 500.00, 100),
(3, 'Headphones', 100.00, 200);

-- Order 데이터 삽입
INSERT INTO `Order` (OrderID, OrderDate, CustomerID) VALUES
(1, '2024-07-22', 1),
(2, '2024-07-23', 2);

-- OrderDetail 데이터 삽입
INSERT INTO OrderDetail (OrderID, ProductID, Quantity, Price) VALUES
(1, 1, 1, 1000.00),
(2, 2, 2, 500.00),
(2, 3, 1, 100.00);

마지막으로 데이터가 제대로 입력 됐는지, 올바르게 동작하는지 테스트하기 위해 데이터를 로드해본다.

여기서 제약 조건이라던가 무결성, 성능 등 모두 제대로 작동하는지 확인한다.

테스트 및 검증

-- 데이터 조회 테스트
SELECT 
    o.OrderID, o.OrderDate, c.Name AS CustomerName, c.Email AS CustomerEmail, 
    p.Name AS ProductName, od.Quantity, od.Price
FROM 
    `Order` o
JOIN 
    Customer c ON o.CustomerID = c.CustomerID
JOIN 
    OrderDetail od ON o.OrderID = od.OrderID
JOIN 
    Product p ON od.ProductID = p.ProductID;

유지 보수

운영 중인 데이터베이스를 지속적으로 관리하고 개선한다.

모니터링

데이터 베이스 응답 시간, 처리량, 자원 사용률 등을 분석하며 성능을 모니터링하고 필요 시 쿼리를 최적화 하거나, 인덱스 튜닝, 하드웨어 업그레이드 등의 성능 향상을 위한 고려를 할 수 있다.

서비스 장애, 데이터 무결성 위반 보안 문제 등 모니터링 시스템과 연동하여 자동으로 복구하는 시스템을 구축할 수도 있다.

백업 및 복구

서비스 장애로 인해 데이터 손실 시 최소 손실을 위해 주기적으로 데이터 베이스 백업을 수행한다.

문제가 발생 했을 시 신속하게 복구할 수 있도록 시스템을 갖출 수 있다.

무결성 유지

데이터 무결성을 유지하기 위해 제약 조건을 주기적으로 검토하고 필요 시 업데이트 할 수 있다.

중복 데이터, 불일치 데이터, 불필요한 데이터를 정리하여 무결설 및 용량을 확보할 수 있다.

보안 관리

사용자 권한을 주기적으로 검토하여 필요한 권한만 부여되어 있는지 등을 검토할 수 있다.

민감 데이터를 암호화하여 외부에 노출되어도 데이터를 복호화 할 수 없도록 암/복호화 시스템을 구축할 수 있다.

비정상적인 활동을 탐지하여 로그에 기록하고 보안 위협에 대응할 수 있다.

데이터베이스 확장 및 축소

오래된 데이터는 아카이빙하여 데이터 베이스 용량을 관리할 수 있다.

대용량 테이블을 파티셔닝하여 성능을 최적화하고 유시보수성을 높일 수 있다.

정기 점검 및 보고

데이터베이스 상태를 정기적으로 점검하고 잠재적인 문제를 사전에 발견하여 방지할 수 있다.

데이터베이스의 상태, 성능, 보안, 백업 현황 등을 주기적으로 보고서로 작성하여 관리자가 쉽게 이해하고 필요한 조치를 취할 수 있다.

Ref.

https://yeongunheo.tistory.com/entry/DB-%EC%84%A4%EA%B3%84%ED%95%98%EB%8A%94-%EB%B2%95-feat-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%AA%A8%EB%8D%B8%EB%A7%81

https://jiyoungtt.tistory.com/47