테이블 설계: 정규화와 키

표를 어떻게 나누고 이어야 데이터가 꼬이지 않는지 배워요. 모든 정보를 엑셀처럼 한 표에 적으면 전화번호가 둘로 갈라지고 고객이 통째로 사라지는 모습을 재현하고, "한 가지 사실은 한 곳에"라는 정규화의 원칙을 1, 2, 3정규형으로 정리해요. 기본 키와 외래 키로 표 사이의 약속을 데이터베이스가 지키게 하고, SQLite가 외래 키 검사를 조용히 무시하는 함정, 상품 가격을 올리면 지난 주문 금액까지 바뀌는 설계 실수, 돈을 소수로 저장하면 생기는 오차까지 실행 결과로 확인해요.

중급강의 3 / 6약 16분

앞 강의의 쇼핑몰은 고객, 상품, 주문, 주문 항목 네 표로 나뉘어 있었어요. 처음 데이터를 다루는 사람은 대개 엑셀처럼 한 표에 다 적는 쪽을 더 자연스럽게 느껴요. 한눈에 보이고, 이어 붙일 필요도 없으니까요. 이 강의에서는 그렇게 했을 때 무슨 일이 생기는지 보고, 표를 나누는 원칙과 나눈 표를 잇는 키를 배워요. 처음에 표를 잘 나눠 두면 나중의 고생이 크게 줄어요.

한 표에 다 적으면 생기는 일

주문 기록을 한 표에 모두 적었어요. 줄마다 주문, 고객, 상품 정보가 함께 들어 있어요.

import sqlite3

flat = sqlite3.connect(":memory:")
flat.executescript("""
CREATE TABLE sales (order_id INTEGER, ordered_on TEXT, customer TEXT, phone TEXT, product TEXT, price INTEGER, qty INTEGER);
INSERT INTO sales VALUES
  (1, '2026-10-02', '민지', '010-1111-2222', '무선 키보드', 39000, 1),
  (1, '2026-10-02', '민지', '010-1111-2222', '무선 마우스', 25000, 1),
  (2, '2026-10-15', '민지', '010-1111-2222', '27인치 모니터', 289000, 1),
  (3, '2026-10-20', '준호', NULL,            '무선 키보드', 39000, 2);
""")
cur = flat.execute("UPDATE sales SET phone = '010-9999-0000' WHERE customer = '민지' AND order_id = 2")
print("바꾼 행:", cur.rowcount)
print("민지의 전화번호:", [p for (p,) in flat.execute("SELECT DISTINCT phone FROM sales WHERE customer = '민지'")])
flat.execute("DELETE FROM sales WHERE order_id = 3")
print("준호의 주문을 지운 뒤 준호라는 고객이 남아 있나요?",
      flat.execute("SELECT COUNT(*) FROM sales WHERE customer = '준호'").fetchone()[0] > 0)
바꾼 행: 1
민지의 전화번호: ['010-1111-2222', '010-9999-0000']
준호의 주문을 지운 뒤 준호라는 고객이 남아 있나요? False

세 가지 사고가 보여요. 이름도 붙어 있어요.

  • 고치기 이상: 민지가 전화번호를 바꿨는데, 민지의 줄은 세 개라 하나만 고치면 번호가 둘로 갈라져요. 어느 쪽이 맞는지 이제 아무도 몰라요. 모든 줄을 빠짐없이 고쳐야 하는 부담이 늘 따라다녀요.
  • 지우기 이상: 준호의 하나뿐인 주문을 취소했더니 준호라는 고객 자체가 사라졌어요. 주문을 지우려 했을 뿐인데요.
  • 넣기 이상: 아직 아무도 사지 않은 새 상품 "USB 허브"는 등록할 방법이 없어요. 줄마다 주문이 있어야 하는 표니까요. 주문 칸을 비워 둔 가짜 줄을 넣으면, 이번엔 "주문 수"를 셀 때마다 그 줄을 빼야 해요.

원인은 하나예요. 같은 사실이 여러 줄에 반복해서 적혀 있기 때문이에요. "민지의 전화번호는 무엇이다"라는 사실이 세 군데에 있으니 셋이 어긋날 수 있고, "준호라는 고객이 있다"는 사실이 주문 줄에 얹혀 있으니 주문과 함께 사라져요.

정규화: 한 가지 사실은 한 곳에

표를 나눠서 각 사실을 딱 한 곳에만 적는 걸 정규화라고 해요. 단계별로 이름(정규형)이 붙어 있는데, 실무에서 중요한 건 세 번째까지예요.

단계규칙어긴 예고친 예
1정규형칸 하나에 값 하나주문 칸에 "키보드, 마우스"를 함께 적기상품마다 한 줄씩(주문 항목 표)
2정규형키의 일부에만 딸린 정보는 떼어 내기주문 항목(주문 번호 + 상품 번호)에 상품 이름 적기상품 이름은 상품 표에
3정규형키가 아닌 칸에 딸린 정보는 떼어 내기주문 표에 고객 번호와 고객 전화번호 함께 적기전화번호는 고객 표에
  • 1정규형: "키보드, 마우스"처럼 칸 하나에 여러 값을 넣으면, 마우스가 몇 개 팔렸는지 세거나 마우스를 산 주문만 고르기가 어려워져요. 글자를 잘라 내는 코드가 필요하고, "무선 마우스 패드"까지 마우스로 잘못 셀 수도 있어요.
  • 2정규형: 주문 항목은 (주문 번호, 상품 번호) 두 칸을 합쳐서 한 줄을 가리켜요. 그런데 상품 이름은 상품 번호만 알면 정해져요. 키의 일부에만 딸린 정보라서, 같은 상품이 팔릴 때마다 이름이 반복돼요.
  • 3정규형: 주문 표의 키는 주문 번호인데, 전화번호는 주문이 아니라 고객에 딸린 정보예요. 고객 번호를 거쳐 정해지는 정보라 고객 표로 옮겨요.

흔히 이렇게 외워요. 모든 칸은 키에, 키 전체에, 오직 키에만 딸려야 한다. 이 원칙대로 나눈 결과가 앞 강의의 네 표예요.

키: 행을 정확히 가리키는 방법

표를 나눴으면 다시 이을 방법이 필요해요. 그게 키예요.

  • 기본 키(PRIMARY KEY): 표 안에서 한 행을 정확히 가리키는 값이에요. 중복될 수 없고 비어 있을 수 없어요.
  • 인공 키와 자연 키: 이메일이나 전화번호처럼 현실의 값(자연 키)을 기본 키로 쓰고 싶어질 수 있어요. 하지만 이런 값은 바뀌어요. 기본 키가 바뀌면 그 값을 가리키던 다른 표의 모든 행도 고쳐야 해요. 그래서 보통은 뜻이 없는 번호(id, 인공 키)를 기본 키로 두고, 이메일 같은 값에는 UNIQUE를 걸어 중복만 막아요.
  • 복합 키: 주문 항목처럼 두 칸을 합쳐야 한 행이 정해지면 PRIMARY KEY (order_id, product_id)로 함께 묶어요. 같은 주문에 같은 상품이 두 줄로 들어가는 것도 이걸로 막혀요.
  • 외래 키(FOREIGN KEY): 다른 표의 기본 키를 가리키는 칸이에요. 주문 표의 customer_id가 고객 표의 id를 가리키는 것처럼요.

외래 키: 표 사이의 약속을 데이터베이스가 지키게

외래 키를 선언해 두면 "주문의 고객 번호는 실제로 있는 고객이어야 한다"는 약속을 데이터베이스가 지켜 줘요. 단, SQLite에는 알아 둘 함정이 있어요.

SCHEMA = """
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    phone TEXT UNIQUE
);
CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE,
    price INTEGER NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    ordered_on  TEXT NOT NULL
);
CREATE TABLE order_items (
    order_id   INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    qty        INTEGER NOT NULL CHECK (qty > 0),
    unit_price INTEGER NOT NULL,                   -- 주문한 순간의 가격
    PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers VALUES (1, '민지', '010-1111-2222'), (2, '준호', NULL);
INSERT INTO products VALUES (1, '무선 키보드', 39000), (2, '무선 마우스', 25000), (3, 'USB 허브', 18000);
INSERT INTO orders VALUES (1, 1, '2026-10-02'), (2, 2, '2026-10-20');
INSERT INTO order_items VALUES (1, 1, 1, 39000), (1, 2, 1, 25000), (2, 1, 2, 39000);
"""

loose = sqlite3.connect(":memory:")               # 외래 키 검사를 켜지 않은 연결
loose.executescript(SCHEMA)
loose.execute("INSERT INTO orders VALUES (3, 99, '2026-11-01')")
print("검사를 켜지 않으면 없는 고객(99번)의 주문도 들어가요:",
      loose.execute("SELECT customer_id FROM orders WHERE id = 3").fetchone())
loose.execute("PRAGMA foreign_keys = ON")         # 위의 INSERT로 트랜잭션이 열린 채라서
print("트랜잭션 안에서 켜면:", loose.execute("PRAGMA foreign_keys").fetchone()[0], "(0은 꺼짐)")

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")            # 연결하자마자, 다른 일을 하기 전에 켜요
db.executescript(SCHEMA)
for sql in ("INSERT INTO orders VALUES (3, 99, '2026-11-01')",
            "DELETE FROM customers WHERE id = 1",
            "INSERT INTO order_items VALUES (2, 3, 0, 18000)",
            "INSERT INTO order_items VALUES (1, 1, 1, 39000)"):
    try:
        db.execute(sql)
    except sqlite3.IntegrityError as e:
        print("거절:", e)
db.execute("DELETE FROM orders WHERE id = 2")
print("주문 2를 지우면 그 항목도 함께 지워져요. 남은 항목:",
      db.execute("SELECT COUNT(*) FROM order_items WHERE order_id = 2").fetchone()[0], "개")
db.commit()
검사를 켜지 않으면 없는 고객(99번)의 주문도 들어가요: (99,)
트랜잭션 안에서 켜면: 0 (0은 꺼짐)
거절: FOREIGN KEY constraint failed
거절: FOREIGN KEY constraint failed
거절: CHECK constraint failed: qty > 0
거절: UNIQUE constraint failed: order_items.order_id, order_items.product_id
주문 2를 지우면 그 항목도 함께 지워져요. 남은 항목: 0 개
  • SQLite는 외래 키 검사가 기본으로 꺼져 있어요. 옛 프로그램과의 호환 때문이에요. 그래서 선언만 해 두면 없는 고객의 주문도 그냥 들어가요. 연결할 때마다 PRAGMA foreign_keys = ON을 실행해야 해요.
  • 트랜잭션 안에서 켜면 조용히 무시돼요. 파이썬의 sqlite3는 INSERT 같은 문장을 실행할 때 트랜잭션을 자동으로 열어요. 그 상태에서 켜면 오류도 없이 꺼진 채로 남아요. 이 예제를 처음 만들 때 실제로 이 함정에 빠져서, 거절되어야 할 문장이 모두 통과했어요. 연결하자마자, 다른 일을 하기 전에 켜는 게 안전해요. PostgreSQL이나 MySQL(InnoDB)은 외래 키를 언제나 검사해요.
  • 검사를 켜니 네 문장이 모두 거절됐어요. 없는 고객의 주문, 주문이 남아 있는 고객의 삭제, 0개 주문(CHECK), 같은 주문에 같은 상품 두 줄(복합 기본 키)이에요.

주문 항목의 외래 키에는 ON DELETE CASCADE를 붙여서, 주문을 지우면 그 항목이 함께 지워졌어요. 가리키던 행이 지워질 때 어떻게 할지는 이렇게 정해요.

선택지가리키던 행이 지워지면어울리는 곳
아무것도 안 적음(NO ACTION), RESTRICT지우기를 거절해요주문이 있는 고객: 구매 기록을 함부로 지우면 안 돼요
CASCADE함께 지워요주문과 주문 항목: 항목은 주문 없이 뜻이 없어요
SET NULL가리키던 칸을 비워요글과 글쓴이: 탈퇴해도 글은 남기고 글쓴이만 비워요

관계의 세 가지 모양

표 사이의 관계는 세 가지 중 하나예요.

고객 1 ──< 주문 N            한 고객이 여러 주문(1:N): 주문 표에 customer_id
주문 N >──< 상품 M           주문 하나에 여러 상품, 상품 하나가 여러 주문에(N:M)
   └─ 주문 항목(order_id, product_id, qty)   사이에 잇는 표를 따로 둬요
회원 1 ── 프로필 1           하나에 하나(1:1): 드물게, 자주 안 쓰는 정보를 떼어 둘 때

N:M 관계는 칸 하나로 표현할 수 없어서, 두 표의 키를 함께 담는 잇는 표(연결 테이블)를 둬요. 주문 항목 표가 그 역할을 하고, 덕분에 "몇 개를 샀는지" 같은 관계 자체의 정보도 담을 수 있어요.

지난 사실은 그때 값으로

앞 강의에서 고객별 총액을 구할 때 상품 표의 현재 가격(p.price)을 곱했어요. 사실 이 계산에는 문제가 있어요. 키보드 가격을 올려 볼게요.

def totals(source):
    return dict(db.execute(f"""
        SELECT o.id, SUM(i.qty * {source})
        FROM orders o JOIN order_items i ON i.order_id = o.id JOIN products p ON p.id = i.product_id
        GROUP BY o.id"""))

print("가격을 올리기 전  현재 가격으로:", totals("p.price"), "주문 당시 가격으로:", totals("i.unit_price"))
db.execute("UPDATE products SET price = 45000 WHERE id = 1")          # 키보드 가격을 올렸어요
print("가격을 올린 뒤    현재 가격으로:", totals("p.price"), "주문 당시 가격으로:", totals("i.unit_price"))
가격을 올리기 전  현재 가격으로: {1: 64000} 주문 당시 가격으로: {1: 64000}
가격을 올린 뒤    현재 가격으로: {1: 70000} 주문 당시 가격으로: {1: 64000}

10월에 64,000원을 낸 주문이, 오늘 키보드 가격을 올렸다는 이유로 70,000원짜리 주문이 돼 버렸어요. 영수증과 매출 장부가 어긋나는 큰 사고예요. 그래서 주문 항목에는 주문한 순간의 가격(unit_price)을 따로 적어 둬요.

같은 값을 두 번 적었으니 정규화를 어긴 것 같지만 아니에요. "지금 이 상품의 가격"과 "그 주문에서 받은 가격"은 서로 다른 사실이에요. 배송 주소, 할인율, 환율처럼 시간이 지나면 바뀌지만 그때의 값이 중요한 정보는 모두 그때의 값을 함께 적어 둬요.

알맞은 자료형과 규칙

칸마다 알맞은 종류와 규칙을 고르면 잘못된 값을 처음부터 막을 수 있어요. 특히 돈은 조심해야 해요.

print(0.1 + 0.2 == 0.3, 0.1 + 0.2)
total = 0.0
for _ in range(10):
    total += 0.1
print("0.1을 열 번 더하면:", total)
won = 39000 + 25000 + 18000                       # 원 단위 정수
print("부가세 포함(소수로):", won * 1.1, "/ 정수로:", won * 11 // 10)
from decimal import Decimal
print("Decimal로:", Decimal("0.1") * 10, Decimal("19.99") * 3)
False 0.30000000000000004
0.1을 열 번 더하면: 0.9999999999999999
부가세 포함(소수로): 90200.00000000001 / 정수로: 90200
Decimal로: 1.0 59.97
  • 돈은 소수(float)로 저장하지 않아요. 컴퓨터의 소수는 이진수라서 0.1을 정확히 담지 못하고, 작은 오차가 계산할수록 쌓여요. 원화는 원 단위 정수로, 센트가 있는 화폐는 센트 단위 정수로 저장해요. 소수점이 꼭 필요하면 파이썬은 Decimal, 데이터베이스는 NUMERIC(PostgreSQL 등) 같은 정확한 형식을 써요.
  • 날짜와 시각: SQLite에는 날짜 형식이 따로 없어서 '2026-11-16'처럼 ISO 8601 글자로 적어요. 이 형식은 글자 순서대로 정렬해도 날짜 순서가 되고 비교도 돼요. 시각은 세계 표준시(UTC)로 저장하고 보여 줄 때 한국 시간으로 바꾸면, 서버를 다른 나라로 옮겨도 꼬이지 않아요. PostgreSQL에는 DATE, TIMESTAMPTZ 같은 전용 형식이 있어요.
  • 규칙을 데이터베이스에도 적어요. NOT NULL, UNIQUE, CHECK (qty > 0), 외래 키는 프로그램에 버그가 있어도, 사람이 직접 데이터를 고쳐도 지켜지는 마지막 방어선이에요.

일부러 반복해 두는 때: 비정규화

정규화가 늘 정답은 아니에요. 표를 잘게 나눌수록 읽을 때 이어 붙이는 일이 늘어요. 하루에 수백만 번 읽고 가끔 바뀌는 값이라면, 계산 결과를 일부러 저장해 두기도 해요. 주문 표에 총액 칸을 두거나, 글마다 댓글 수를 적어 두는 식이에요. 이걸 비정규화라고 해요.

다만 지켜야 할 것이 있어요. 원래의 사실은 정규화된 표에 그대로 두고, 저장해 둔 값은 언제든 다시 계산할 수 있는 복사본으로 다뤄요. 원래 값을 바꿀 때 복사본도 같은 트랜잭션 안에서 함께 고쳐요. 그리고 정말 느려서 필요한지 먼저 재 봐요. 다음 강의의 인덱스만으로 충분한 경우가 많아요.

이름 짓기 관례

  • 표 이름은 복수형 영어 소문자에 밑줄(customers, order_items)로 써요.
  • 기본 키는 id, 외래 키는 가리키는표_id(customer_id)로 써요. 이름만 봐도 무엇을 가리키는지 보여요.
  • 언제 만들고 고쳤는지 created_at, updated_at을 두면 문제를 추적할 때 큰 도움이 돼요.
  • order, user, group처럼 SQL 낱말과 겹치는 이름은 피해요. ORDER BY의 그 order라서 따옴표를 매번 붙여야 해요.

실습

  1. 한 표 예제에서 민지의 전화번호를 모든 줄에서 바꾸는 UPDATE를 쓰고, 정규화된 설계에서는 몇 행만 바꾸면 되는지 비교해 보세요.
  2. 학교 시간표를 설계해 보세요. 학생, 과목, 수강 신청, 선생님이 있고, 한 과목은 한 선생님이 가르치며, 학생은 여러 과목을 들어요. 어떤 표가 필요하고 어떤 관계인가요?
  3. 글과 댓글 표를 만들고, 글을 지우면 댓글도 지워지게(CASCADE), 회원이 탈퇴하면 댓글의 글쓴이만 비워지게(SET NULL) 해 보세요. 실제로 지워 보며 확인하세요.
  4. orders에 total 칸을 더하고, 주문 항목을 넣을 때 같은 트랜잭션 안에서 total도 고치는 함수를 만들어 보세요.
  5. PRAGMA foreign_keys를 켜지 않은 연결로 DELETE FROM customers WHERE id = 1을 실행해 보세요. 남은 주문들의 고객 번호는 무엇을 가리키나요?

자주 하는 실수

  • 모든 것을 한 표에 적는다. 같은 사실이 반복되면 언젠가 어긋나요.
  • 칸 하나에 쉼표로 여러 값을 적는다. 세고, 고르고, 잇기가 어려워져요. 줄로 나눠요.
  • 바뀔 수 있는 값을 기본 키로 쓴다. 이메일, 전화번호는 UNIQUE로 두고 기본 키는 뜻 없는 번호로 해요.
  • SQLite에서 외래 키 검사를 켜지 않는다. 선언만으로는 아무것도 지켜지지 않아요. 연결하자마자 켜요.
  • 주문에 현재 가격을 이어 붙여 계산한다. 가격을 바꾸는 순간 지난 매출이 바뀌어요. 그때의 가격을 저장해요.
  • 돈을 소수로 저장한다. 정수나 정확한 소수 형식을 써요.
  • 구매 기록이 있는 표에 CASCADE를 건다. 고객 하나를 지웠다가 매출 기록이 통째로 사라질 수 있어요.

점검 문제

  1. 한 표에 모든 정보를 적으면 생기는 세 가지 이상과, 그 공통 원인은?
  2. 1정규형, 2정규형, 3정규형을 한 문장씩으로 말해 보세요.
  3. 이메일을 기본 키로 쓰지 않고 UNIQUE만 거는 이유는?
  4. SQLite에서 외래 키가 지켜지지 않는 두 가지 경우는?
  5. 주문과 주문 항목, 고객과 주문 사이에는 각각 어떤 ON DELETE가 어울리나요?
  6. N:M 관계는 어떻게 표현하나요?
  7. 주문 항목에 unit_price를 따로 저장하는 것이 정규화를 어긴 게 아닌 이유는?
  8. 돈을 소수(float)로 저장하면 안 되는 이유와 대안은?

정답

  1. 고치기 이상(같은 값을 여러 줄에서 다 고쳐야 하고 일부만 고치면 어긋남), 지우기 이상(주문을 지우면 고객 정보까지 사라짐), 넣기 이상(주문 없는 새 상품을 넣을 수 없음)이에요. 같은 사실이 여러 줄에 반복해서 적혀 있는 게 공통 원인이에요.
  2. 1정규형은 칸 하나에 값 하나, 2정규형은 키의 일부에만 딸린 정보를 떼어 내기, 3정규형은 키가 아닌 칸에 딸린 정보를 떼어 내기예요.
  3. 이메일은 바뀔 수 있는데, 기본 키가 바뀌면 그 값을 가리키는 다른 표의 모든 행을 고쳐야 하기 때문이에요. 중복만 UNIQUE로 막고 기본 키는 변하지 않는 번호로 둬요.
  4. 연결에서 PRAGMA foreign_keys = ON을 실행하지 않았을 때, 그리고 트랜잭션이 열린 상태에서 실행해서 조용히 무시됐을 때예요.
  5. 주문 항목은 주문 없이 뜻이 없으니 CASCADE, 주문은 구매 기록이라 함부로 지우면 안 되니 고객 쪽은 거절(NO ACTION 또는 RESTRICT)이 어울려요.
  6. 두 표의 키를 함께 담는 잇는 표(연결 테이블)를 둬요. 주문과 상품 사이의 주문 항목 표가 그 예예요.
  7. 상품의 현재 가격과 그 주문에서 받은 가격은 서로 다른 사실이기 때문이에요. 현재 가격이 바뀌어도 지난 주문의 금액은 바뀌면 안 돼요.
  8. 소수는 0.1 같은 값을 정확히 담지 못해 오차가 쌓이기 때문이에요. 원 단위 정수로 저장하거나, 소수점이 필요하면 Decimal이나 NUMERIC 같은 정확한 형식을 써요.

다음 강의

표를 잘 나눴으니 이제 빠르게 꺼낼 차례예요. 첫 강의에서 인덱스 하나로 검색이 수천 배 빨라지는 걸 봤죠. 다음 강의에서는 인덱스가 안에서 어떻게 생겼는지, 어떤 질문에 쓰이고 어떤 질문에는 쓰이지 않는지, 인덱스를 늘릴 때 치르는 대가는 무엇인지 실행 계획을 읽으며 배워요.