[DB-004][기초] 주문 데이터를 나눠 중복과 오류 줄이기 > IT 기술 공유

• 목록

[이번 수업]
한 주문 행에 고객·상품 정보를 모두 넣으면 값이 반복되고 상품 칸도 늘어납니다. 데이터 모델링(대상을 표와 관계로 설계하는 일)과 정규화(중복과 오류를 줄이는 표 분리 규칙)로 네 표를 만듭니다.

[선수지식]
DB-001~003의 표·행·열, INSERT, SELECT, JOIN을 알고 있으면 됩니다. 로컬 SQLite를 준비하세요.

[학습목표]
1. 갱신·삽입·삭제 이상을 설명한다.
2. 기본키와 외래키로 관계를 표현한다.
3. 제약 조건과 JOIN을 실행한다.

[핵심개념]
orders(order_id, customer_name, customer_email, product1, product2) 한 표는 이메일 일부만 고쳐 값이 달라지는 갱신 이상, 주문 없이 상품을 넣기 어려운 삽입 이상, 마지막 주문과 고객 정보를 함께 잃는 삭제 이상을 만듭니다.

제1정규형은 한 칸에 한 값을 두고 반복 열을 행으로 바꿉니다. 제2정규형은 복합키 전체에 의존하지 않는 값을 분리합니다. 주문항목 키가 (order_id, product_id)라면 상품 이름은 product_id에만 의존하므로 products로 갑니다. 제3정규형은 일반 열이 다른 일반 열에 기대지 않게 합니다. 고객 이메일은 customers에 둡니다.

PRIMARY KEY는 행을 유일하게 찾고, FOREIGN KEY는 다른 표를 가리킵니다. 주문과 상품의 다대다 관계는 order_items로 연결합니다. UNIQUE는 이메일 중복을, CHECK는 음수 가격과 0개 수량을 막습니다.

[따라하기]
아래를 schema.sql로 저장합니다. 가격은 원 단위 정수입니다.

```sql
PRAGMA foreign_keys = ON;
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO customers VALUES (1, '민수', 'minsu@example.invalid');
INSERT INTO products VALUES (10, '키보드', 50000);
INSERT INTO orders VALUES (100, 1);
INSERT INTO order_items VALUES (100, 10, 2);
SELECT o.order_id, c.name, p.name, oi.quantity
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id;
```

macOS/Linux는 `sqlite3 :memory: < schema.sql`, Windows PowerShell은 `Get-Content schema.sql | sqlite3 :memory:`를 실행합니다. 결과는 `100|민수|키보드|2`입니다. SQLite 외래키 검사는 연결마다 켜야 하므로 첫 줄이 필요합니다.

[흔한 실수]
고객 정보를 orders에도 복사하면 중복됩니다. 상품 ID를 쉼표로 저장하면 검색과 제약 검사가 어렵습니다. 앱에서 검사했다고 DB 제약 조건을 생략하지 마세요.

[보안 주의]
로컬 DB와 가짜 정보만 사용하세요. 개인정보 접근 권한을 최소화하고 앱에서는 매개변수 쿼리를 사용하세요. 기본키를 알아도 조회 권한이 생기지 않으므로 서버에서 사용자 권한을 확인해야 합니다. 제약 조건은 암호화·백업을 대신하지 않습니다.

[직접 해볼 과제]
1. 상품과 주문항목을 하나씩 추가해 주문 100이 두 줄인지 확인하세요.
2. 같은 이메일을 다시 넣어 UNIQUE 오류를 관찰하세요.
3. orders에 created_at을 추가하고 왜 주문 표에 속하는지 설명하세요.

[확인문제]
1. 주문과 상품 사이에 order_items가 왜 필요한가요?
2. 기본키와 외래키의 역할은 어떻게 다른가요?
3. 고객 이메일을 한 번만 저장하면 어떤 갱신 이상을 막나요?

[다음 학습]
다음은 OS-004 셸 파이프·리다이렉션·텍스트 처리입니다. DB 트랙은 DB-005 인덱스와 실행 계획으로 이어집니다.

[공식 참고 자료]
https://www.postgresql.org/docs/current/tutorial-table.html
https://www.postgresql.org/docs/current/ddl-constraints.html
https://www.sqlite.org/foreignkeys.html
https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description