Ch03. 데이터 관리 - DDL(테이블 생성)

쇼핑몰 테이블 실전 설계

• 고객
  • 고객 id, 이름, 이메일, 비밀번호, 주소, 가입 시각이 관리되어야 한다.
• 상품
  • 상품 id, 이름, 설명, 가격, 재고 수량이 관리되어야 한다.
• 주문
  • 주문 id, 주문 고객, 주문 상품, 주문 수량, 주문 시각, 주문 상태가 관리되어야 한다.
  • 주문이 등록되면 최초의 주문 상태는 주문접수 상태가 된다.
  • 예제를 단순화 하기 위해 한 번의 주문 시에 한 종류의 상품만 주문할 수 있다. 한 종류의 상품을 여러 개 주문하는 것은 가능하다.

고객 테이블

create table customers(
	customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    join_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

• customer_id: 고객을 식별한느 기본 키(primary_key). auto_increment로 자동 번호가 부여된다.
  • auto_increment 덕분에 데이터를 저장할 때마다 값이 1씩 자동으로 증가한다. 따라서 모든 행을 구분할 수 있다.
• name, email, password, address : 모두 필수 값이므로 NOT NULL 을 설정했다.
• email : 고객마다 유일해야 하므로 UNIQUE 제약 조건을 추가했다.
• join_date : 가입 시각. 값을 따로 넣지 않으면 DEFAULT 설정에 따라 현재 시각(CURRENT_TIMESTAMP)가 자동으로 기록된다.

날짜와 기본값 설정 옵션
CREATE TABLE test (
...
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
...
);
이 기능을 사용하면 행이 추가되거나 변경된 날짜를 편리하게 관리할 수 있다.
DEFAULT CURRENT_TIMESTAMP : 새로운 데이터 행(row)이 추가될 때, 해당 컬럼에 별도의 값을 지정하지 않으면 현재의 날짜와 시간이 자동으로 입력된다.
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP : 새로운 데이터 행(row)이 추가될 때는 물론이고, 같은 행의 컬럼 값이 변경되어 업데이트될 때, 이 컬럼의 값은 현재 날짜와 시간으로 자동 갱신된다.

상품 테이블

create table products(
	product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price INT NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0
);

• product_id : 상품을 식별하는 기본 키. AUTO_INCREMENT를 사용했다.
• price : 금액이므로 INT 타입을 사용했고, 필수 값이므로 NOT NULL 이다.
• stock_quantity : 재고 수량. 필수 값이지만, 값을 넣지 않으면 기본적으로 0이 되도록 DEFAULT를 설정했다.
• description: 상품 설명은 길 수 있으므로 TEXT 타입을 사용했고, 필수는 아니므로 NOT NULL 제약 조건을 걸지 않았다.

주문 테이블

create table orders(
	order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT '주문접수',
      
    CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES custoordersmers(customer_id),
    CONSTRAINT fk_orders_products FOREIGN KEY (product_id) REFERENCES products(product_id)
);

• order_id : 주문을 식별하는 기본 키. AUTO_INCREMENT 를 사용했다.
• customer_id : 가장 중요한 부분. 이 주문을 한 고객이 누구인지를 나타낸다.
  • 이 값은 customers 테이블의 customer_id를 참조한다. 이곳을 확인하면 고객의 이름을 찾을 수 있다.
• product_id: 가장 중요한 부분. 어떤 상품을 주문했는지 나타낸다.
  • 이 값은 products 테이블의 product_id 를 참조한다. 이곳을 확인하면 상품명을 찾을 수 있다.

외래키 제약 조건

CONSTRAINT [제약조건_이름]
FOREIGN KEY ([자식_테이블의_컬럼명])
REFERENCES [부모_테이블명]([부모_테이블의_컬럼명])
[ON DELETE 옵션] [ON UPDATE 옵션]

fk_orders_customers

CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

• orders 테이블의 customer_id를 customers 테이블의 customer_id 와 연결하는 외래키 설정이다. 이로써 두 테이블 사이에 관계가 형성된다. 이제 존재하지 않는 고객 ID로 주문을 넣으려는 시도는 데이터베이스단에서 차단된다.
• 외래 키 제약조건의 이름에는 관계 있는 테이블의 참조 순서와 이름을 사용했다. 이렇게 하면 이름으로 관계를 쉽게 파악할 수 있다.(orders -> customers)

fk_orders_products

CONSTRAINT fk_orders_products FOREIGN KEY (product_id) REFERENCES products(product_id)

• orders 테이블의 product_id를 products 테이블의 product_id 와 연결하는 외래키 설정이다. 이로써 두 테이블 사이에 관계가 형성된다. 이제 존재하지 않는 상품 ID로 주문을 넣으려는 시도는 데이터베이스단에서 차단된다.
• 외래키 제약조건의 이름에는 관계 있는 테이블의 이름을 사용해서 fk_orders_products라고 했다. 이렇게 하면 이름으로 관계를 쉽게 파악할 수 있다. (orders -> products)

ERD: 데이터베이스의 지도, 관계를 한눈에 파악하기

erd

• mySql 워크벤치 실행
• 상단 메뉴 바에서 Database > Reverse Engineer... 실행
• Continue로 계속 이동 후, my_shop 데이터베이스 선택
• 이후 계속 넘어가면서 ERD 확인

왜 ERD가 필요할까? 지금까지 우리는 customers, products, orders 라는 3개의 테이블을 만들었다. 테이블이 몇 개 안 될 때는 머릿속으로 그 구조와 관계를 그릴 수 있지만, 실무에서는 수십, 수백 개의 테이블이 복잡하게 얽히게 된다. 이때, 테이블과 그들 사이의 관계(Relationship)를 그림으로 표현한 설계도가 없다면 우리는 데이터의 구조라는 거대한 숲에서 길을 잃고 말 것이다. ERD가 바로 그 설계도, 즉 데이터베이스의 지도 역할을 한다.

 

우리가 만든 쇼핑몰의 관계를 간단히 표현하면 다음과 같다.

• 고객(Customers)과 주문(Orders)의 관계
  • 한 명의 고객(1)은 여러 번 주문(N)할 수 있다.
  • 이를 1:N (일대다, One-to-Many) 관계라고 한다.
  • customers (1) -----< orders (N)
  • customers (1) -----< orders (N)
• 상품(Products)과 주문(Orders)의 관계
  • 하나의 상품(1)은 여러 번 주문(N)될 수 있다.
  • 이 또한 1:N (일대다, One-to-Many) 관계다.
  • products (1) -----< orders (N)

테이블과 컬럼 이름 규칙

기본 규칙 (백틱 미사용)

백틱으로 감싸지 않는 이름은 가장 일반적이며 권장되는 방식이다. 예) product_name

• 허용 문자
  • 영문 대소문자 (a-z , A-Z )
  • 숫자 (0-9)
  • 밑줄 (_)
  • 달러 기호($)
  • UTF-8과 같은 다국어 문자 (예: 한글)
• 제한 사항
  • 이름이 숫자로 시작할 수 없다.
  • MySQL 예약어 (예: SELECT , TABLE , ORDER )는 사용할 수 없다.
  • 총 길이는 64자를 넘을 수 없다.

확장 규칙 (백틱 사용)

이름을 백틱(`)으로 감싸면 훨씬 더 자유로운 이름 생성이 가능하다.

• 허용 문자: 거의 모든 문자가 허용된다.
• 공백 (예: `user name`)
• 하이픈(-) 및 기타 특수문자 (예: `item-code`, `@email`)
• 숫자로 시작하는 이름 (예: `2025_report`)
• MySQL 예약어 (예: `order`, `select`)

권장되는 명명 규칙 (Best Practice)

기술적인 규칙을 넘어, 실무에서는 다음과 같은 규칙을 따르는 것이 좋다.

• 영문 소문자와 밑줄 사용: user_orders , product_name 처럼 영문 소문자와 단어를 구분하는 밑줄(_)을 사용하는 것이 가장 일반적이다. (스네이크 케이스, snake_case)
• 일관성 유지: 프로젝트 내에서 명명 규칙을 하나로 정하고 일관되게 적용하는 것이 중요하다.
• 예약어 피하기: 백틱을 사용하면 예약어도 이름으로 쓸 수 있지만, 혼란을 피하기 위해 사용하지 않는 것이 좋다.
• 간결하고 명확하게: 이름만 보고도 데이터의 의미를 파악할 수 있도록 만든다. (예: prdct_nm 보다는 product_name )
• 다국어 이름 피하기: 한글 이름도 기술적으로는 가능하지만, 호환성 및 인코딩 문제를 예방하기 위해 영문으로 작성하는 것을 권장한다.