[SEOMARU]データベース設計入門#3 テーブル設計の手順【実戦形式で解説】 테이블 설계의 순서

테이블 설계의 순서

1. 시스템의 요건을 파악

2. 테이블의 개요설계(러프스케치)

컬럼형을 어떻게 할 것인지, 유니크 키를 어떻게 할 것인지 등의 상세한 설계는 스텝3에서 한다.

스텝2에는 주요한 테이블과 컬럼의 일람을 적는 러프스케치 작업을 한다.

3. 테이블의 상세 설계(최종조정)

STEP1 시스템의 요건을 파악

 

설계의 제1보는 시스템의 요건과 기능을 명확히 할 것.

요건은

아마존같은 EC사이트

기능일람을 보면

프론트화면에서

상품검색

상품 상세 페이지

마이페이지

- 로그인

-구입이력

관리자 화면(판매자)

-로그인

-상품관리

-상품카테고리관리

 

설계의 제1 스텝으로는 요건과 기능을 명확히 정의하는 것부터 시작한다.

어떻게 정의할 것인지는 고객과 요건이 정의가 되어야 한다.

 

 

테이블의 개요설계(러프스케치)

시스템의 요건과 기능일람이 정의되면

테이블의 개요를 작성한다.

스텝2의 페이즈이다.

칼럼의 형과 제약 조건등은 나중에 설정한다.

우선 테이블의 일람과 주요 컬럼을 적으면 된다.

툴은 엑셀을 추천하지만, 노트에 손으로 적어도 괜찮다.

기능일람을 보면서 시나리오에 따라 테이블을 적어감.

기능일람에서는 상품검색이 제일 첫번째 나오지만, 상품이 하나도 등록되어 있지 않으므로, 우선 상품을 등록할 필요가 있다.

시나리오로 생각할 때에는, 위에서부터 순서대로 보는 것이 아니라 시나리오를 머리에 떠올린다.

우선 관리자로 로그인을 하고 나서, 상품을 등록하는 등 이 부분부터 확인해볼 것이다.

관리자가 상품을 등록하는 시나리오.

로그인을 진행할 때 필요한 테이블이 있다.

점포테이블에서

점포명과 메일어드레스, 패스워드를 통해 점포관리자가 로그인.

다음은 상품과 상품카테고리를 관리하기 위한 테이블이 필요하다.

상품테이블과 상품카테고리 테이블 추가.

 

다음은 일반 유저가 사용할 프론트 화면을 보도록 하자.

상품검색이나 상품상세 페이지이지만, 관리자가 등록한 테이블을 통해 진행한다.

따라서 새로운 테이블은 필요없다.

마이페이지에서 로그인한다던가 구입이력을 보는 것이 있다.

이를 위해 유저 테이블을 추가한다.

구입이력 테이블도 추가한다.

이런 느낌으로 시나리오를 떠올리며 테이블과 컬럼을 작성했다.

이것으로 스텝2의 개요설계는 끝났다.

 

 

 

 

 

3-1. 일본어를 영어로 변환

3-2. 컬럼에 형을 붙인다.

3-3. ER도를 적으면서 정규화

3-4. 제약조건이나 인덱스를 붙인다.

일본어 테이블을 영어로 변환.

영어로 변환시 명명규칙이 있다.

- 반각 알파벳, 반각 숫자, 언더바

- 테이블의 이름은 복수형, 컬럼명은 단수형이 기본

- text1, text2와 같은 조잡한 이름은 작성하지 않는다.

 

 

재고는 stock 등이다.

다른 컬럼을 봐도 다 단수형이다.

다 의미가 있으므로 한번에 보면 이해할 수 있는 컬럼명을 작성.

일본어 이름은 논리명

영어는 물리명

이를 기억하라.

 

이중 관리는 돈이 들므로, 작성하고 물리명으로 관리.

 

데이터베이스에는 예약명이라고 해서 위와 같은 단어들이 있다.

테이블 명이나 컬럼 명으로 사용해서는 안된다.

왜냐하면 SQL에서 구분 에러가 발생.

예약명은 많이 있으므로, 일람을 기억해두도록 해라.

여기부터는 엑셀로는 작업이 어려우므로 MySQL Workbench를 사용해서 진행하도록 하겠다.

컬럼에 형을 붙인 결과가 이와 같다.

items의 name은 VARCHAR(255)에 형을 붙인다.

255는 의미가 있으므로 기억해두자.

varchar의 자리수.

累乗(るいじょう) 누승, 거듭제곱

이중에 256은 255로 고쳐야 한다.

256이상은 인덱스가 붙을 수가 없기 때문이다.

(255는 1바이트 사용, 256은 2바이트 사용)

자릿수는 여유를 주는 것이 좋다.

 

모든 테이블에 id, created_at, updated_at 3종 세트가 있다.

id

프라이머리키(기본키)

인트형 오토인크리먼트

이름은 id로 해야 한다. shop_id와 같이 해서는 안된다.

 

created_at/updated_at

datetime형

_date가 아닌 _at이라고 명명

 

ER도를 쓰면서 정규화.

외부키를 추가해보자

최종형태는 이런 식으로 된다.

shops 테이블과 items 테이블을 보도록 하자.

1개의 점포는 복수의 상품을 가지고 있다.

1:n일때에는 n테이블에 shop_id를 추가한다.

외부키의 이름 명명은 테이블의 단수형_id이다.

shops의 단수형 + _ + id

복수형을 적으면 안된다.

 

기본키 id를 설명할 때에는 shop_id 등이 안된다고 했었다.

외부키의 경우에는 shop_id와 같이 된다.

 

다른 1:n테이블을 보도록 하자.

users 테이블과 purchase_histories 테이블이다.

사용자 테이블과 구매이력 테이블이다.

 

한명의 사용자는 복수의 구매이력을 갖기 때문에 1:n 관계가 된다.

1:n관계에는 n 방향에 외부키를 갖는다.

users의 단수형 + _ + id

 

구입 이력 테이블에는 구입한 상품의 id를 가지게 하고 싶지만, 상품을 복수 구매하는 것을 생각한다면, 구입이력에서 상품id를 가지고 있는 게 아니라, 구입 이력 명세 테이블에 넣는다.

 

구입이력 테이블에서부터 구입 명세 이력 테이블은 1:n관계에 있다.

 

상품과 구입 명세 이력은 1:1 관계이다.

ER도를 그려가면서 정규화를 할 수 있다.

 

상품카테고리와 상품의 관계는 n:n 관계이다.

n:n 관계는 중간에 테이블을 만들어 중간에 외부키를 만든다.

items 테이블에 대해서 item_id

item_categories 테이블에 대해서 item_category_id

 

제약조건이나 인덱스를 붙인다.

인덱스는 검색을 빠르게 하기 위한 것이다.

검색의 키가 되는 컬럼에 붙인다.

기본키나 외부키에는 붙이지 않아도 된다. 왜냐하면 자동으로 붙는다.

 

이번의 요건을 고려하자면 상품검색 기능이 있다.

상품명으로 검색을 하기 때문에, items테이블의 name으로 설정한다.

 

제약

NOT NULL 제약

유니크키 제약

외부키 제약

3개의 제약은 가능한한 걸어라.

 

모두 필수 사항이므로 전부 NOT NULL제약을 건다.

반대로 어떤 때 NOT NULL 제약을 걸지 않는가?

구입이력에서 메모라는 컬럼이 있다.

메모는 비고로서 영수증주세요 등 있어도 없어도 되는 컬럼이다.

이 때에는 NULL허가가 된다.

 

 

유니크 키 제약은 값이 중복되지 않는 제약이다. 중복되지 않는 항목에는 가능한한 붙이자.

예를 들면 상품의 카테고리 테이블이다.

술이라던가 상품 카테고리의 이름이 중복되는 것을 방지하기 위해 name에 유니크키 제약을 걸 것이다.

상품에서도 상품명의 중복을 방지하기 위해 name에 유니크키 제약을 걸 것이다.

 

점포의 이름도 중복되면 안되므로

shops의 name에 유니크키 제약을 건다.

 

사용자에는 이름의 중복이 있을 수 있으므로 걸지 않는다.

외부키 제약은 relation에 레코드가 있음을 보증하는 제약이다. 가능한 제약을 걸도록 한다.

외부키에는 전부 건다.

 

유저id라던가 구매이력id라던가 shopid.

전부 외부키이므로 relation에 레코드가 있음을 보증.

구입이력의 user_id를 생각해보자.

유저가 탈퇴했을 때, 물리삭제를 할 것인가, 논리삭제를 할 것인가에 따라 외부키 제약 조건이 바뀐다.

물리삭제는 레코드 삭제로 외부키 제약이 담보되지 않는다.

한편, 논리삭제는 삭제 플래그를 집어넣어 논리적으로 삭제되므로 유지된다.

たもつ [保つ] 유지되다.

 

논리 삭제인가 물리 삭제인가 개인정보의 보호방침 등에 설계 이전에 시스템 요건과 엮여 있으며, 요건을 확인하면서 설계하자.

 

 

 

테이블의 설계 순서

1. 시스템의 요건을 파악

2. 테이블의 개요설계(시나리오에 따라서)

あらいだす [洗い出す] 밝혀 내다

そう [沿う] 따르다.

3. 테이블의 상세 설계

3-1 일본어를 영어로 변환

3-2 컬럼에 형을 붙임

3-3 ER도를 적으면서 정규화

3-4 제약조건이나 인덱스를 붙임.

 

https://youtu.be/D5UgoChhrSo?si=JGDc6-vBUNazeyBM