MariaDB 강좌 / 중복 데이터를 줄이는 정규화와 주문 데이터베이스 설계

정규화는 어떤 키가 어떤 사실을 결정하는지 분석해 중복과 변경 오류를 줄이도록 테이블을 나누는 설계 과정입니다. 고객·상품의 현재 정보와 주문 당시의 거래 사실을 구분하면, 이름 변경은 한 곳에서 처리하면서 과거 주문 단가는 보존할 수 있습니다.

같은 사실을 여러 행에 저장할 때의 문제 찾기

고객 이름을 주문 상세마다 복사하면 한 주문의 이름만 바뀌어 같은 고객이 두 이름으로 나타날 수 있습니다. 마지막 주문 상세를 지우면서 고객 정보까지 잃거나, 아직 주문하지 않은 고객을 저장할 곳이 없는 문제도 생깁니다. 이런 수정·삭제·삽입 이상을 줄이기 위해 사실의 주체를 분리합니다. 키와 외래키의 실제 정의는 테이블 관계 설계하기: 일대다·다대다·외래키, 이미 있는 데이터에 새 조건을 적용하는 과정은 ALTER TABLE로 구조 변경하기: 열·제약 조건·변경 전 점검에서 각각 검증합니다.

원자적인 값의 범위는 사용 목적과 도메인에 따라 정합니다. 이 강좌처럼 상품별 조회·수량 계산이 필요하다면 상품 목록을 쉼표 문자열 한 열에 넣는 방식은 적합하지 않습니다. 모든 문자열이나 모든 JSON을 무조건 분해해야 한다는 뜻은 아닙니다.

정규화된 구조와 비교용 펼친 테이블 만들기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 c37_customers, c37_products, c37_orders, c37_items는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

USE shopcourse;
CREATE TABLE c37_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c37_order_customer FOREIGN KEY (customer_id)
  REFERENCES c37_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c37_item_order FOREIGN KEY (order_id)
  REFERENCES c37_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c37_item_product FOREIGN KEY (product_id)
  REFERENCES c37_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c37_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c37_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c37_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c37_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

c37_flat이 없는지 추가로 확인한 뒤 다음 코드를 실행합니다. CREATE TABLE AS SELECT로 만든 비교용 복사본은 원본의 모든 키·제약 조건을 자동 복제하는 백업이 아니며, 중복 현상을 관찰하는 데만 사용합니다.

CREATE TABLE c37_flat AS
SELECT o.order_id,i.product_id,c.customer_id,c.customer_name,
 p.product_name,i.quantity,i.unit_price
FROM c37_orders o JOIN c37_customers c ON c.customer_id=o.customer_id
JOIN c37_items i ON i.order_id=o.order_id
JOIN c37_products p ON p.product_id=i.product_id;
SELECT customer_id,customer_name,COUNT(*) AS repeated_rows
FROM c37_flat GROUP BY customer_id,customer_name ORDER BY customer_id;
START TRANSACTION;
UPDATE c37_flat SET customer_name='김민수 변경' WHERE order_id=101;
SELECT DISTINCT customer_id,customer_name FROM c37_flat
WHERE customer_id=1 ORDER BY customer_name;
ROLLBACK;

고객 1의 이름은 상세 세 행에 반복됩니다. 101번 주문의 행만 바꾸면 고객 1에게 기존 이름과 변경 이름이 함께 보입니다. 마지막 ROLLBACK으로 비교용 변경을 취소하며 정규화된 원본 고객 테이블은 수정하지 않았습니다.

부분 종속과 이행 종속을 실제 열로 살펴보기

복합키가 주문 번호·상품 번호인 상세 행에서 주문 날짜는 주문 번호만으로 정해집니다. 이를 상세마다 저장하면 복합키 일부에만 의존하는 사실이 반복됩니다. 주문 날짜를 orders로 옮겨 상세의 수량·거래 단가와 구분합니다.

주문 번호가 고객 번호를 결정하고 고객 번호가 현재 고객 이름을 결정한다면, 현재 이름을 주문마다 저장하는 것은 고객 정보 변경을 여러 곳에 전파하게 만듭니다. 고객 번호로 customers를 참조하도록 나누면 현재 이름을 한 곳에서 관리할 수 있습니다. 이것이 키와 종속 관계를 따져 2정규형·3정규형을 검토하는 실질적인 이유입니다. 3정규형의 종속 관계 설명을 참고할 수 있습니다.

과거 거래 단가와 현재 상품 가격은 다른 사실

items.unit_price를 products.price와 값이 같다는 이유로 지우지 않습니다. 전자는 주문 당시 가격이고 후자는 현재 판매가이므로 서로 다른 시점의 사실입니다. 상품 가격이 바뀐 뒤 과거 주문 합계를 다시 계산할 때 당시 단가가 필요합니다.

배송지나 고객 표시 이름도 주문 시점의 증빙이 필요하면 별도의 스냅샷으로 보존할 수 있습니다. 현재 고객 정보와 거래 당시 정보를 같은 이름으로 혼용하지 말고 의미·작성 시점·수정 정책을 명확히 합니다. 무조건 열 중복을 없애는 것과 필요한 이력 보존은 다릅니다.

키·외래키·업무 규칙을 함께 검증하기

정규화된 네 테이블을 만들었다고 잘못된 참조가 자동으로 막히는 것은 아닙니다. 기본키·UNIQUE·NOT NULL·외래키를 실제로 정의하고 누락 부모 입력과 중복 키가 거절되는지 확인해야 합니다. 외래키의 실제 적용 조건도 함께 확인하세요.

이 예제는 한 주문에서 같은 상품을 한 상세 행으로 모은 정책입니다. 옵션·로트·부분 배송을 구분해야 한다면 상세 번호와 추가 키를 설계해야 합니다. 설계 표를 모든 주문 시스템의 정답으로 사용하지 말고 어떤 행이 어떤 사실을 나타내는지 요구 사항에 맞춰 바꿉니다.

성능 때문에 구조를 바꾸기 전 기준 정하기

정규화는 변경 일관성의 기준이며, 조회 성능은 실제 쿼리·인덱스·데이터량으로 따로 측정합니다. 보고용 요약 테이블 같은 비정규화가 필요하다면 원본의 권위와 갱신·오류 복구 규칙을 먼저 정해야 합니다. 단순히 JOIN이 많다는 이유만으로 같은 현재 정보를 여러 곳에 복사하지 않습니다.

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

DROP TABLE shopcourse.c37_flat;
DROP TABLE shopcourse.c37_items;
DROP TABLE shopcourse.c37_orders;
DROP TABLE shopcourse.c37_products;
DROP TABLE shopcourse.c37_customers;