8-4. 쇼핑몰 모델링

1.쇼핑몰 관리 프로그램

데이터베이스 모델링에 관한 여러 가지 이론에 대해 알아보았다. 이제 작은 규모나마 온라인 쇼핑몰 프로젝트에 대한 모델링 실습을 해 보자. 쇼핑몰은 실생활과 밀접해 별도의 업무 파악이 필요 없고 비즈니스 로직도 간단하기 때문이다. 누구나 한번쯤은 온라인에서 물건을 사 본 적이 있다.

업무가 유사한 도서 대여점이나 음반 대여점 등에도 그대로 적용할 수 있으며 약간만 형태를 바꾸면 재고 관리나 슈퍼마켓 관리, 회원 관리 등에 응용할 수 있다. 여기서 모델링한 데이터베이스는 예제 수준의 쇼핑몰 관리 프로그램을 실습하기에도 충분하다.

다음 장부터 연구할 서브쿼리, 조인 등의 고급 문법에 쇼핑몰 테이블을 활용한다. 직접 모델링하면서 테이블 구조를 잘 익혀두자. 주제를 선정했으니 기획은 이미 완료한 것이고 다음은 업무 파악을 할 단계이다. 실제 개발이라면 쇼핑몰의 업무를 꼼꼼히 파악해야겠지만 여기서는 가상의 쇼핑몰을 대상으로 하므로 비즈니스 로직도 가상으로 만든다. 이 쇼핑몰은 다음 규칙대로 운영한다.

l 처음 가입시 준회원으로 등록하며 매출이 10000원을 넘을 경우 정회원이 되고 50000원이 되면 우수회원으로 등록한다. 정회원 이상은 배송비가 무료이며 우수회원은 10% 추가 할인된다.

l 간편한 결제를 위해 예치금을 맡기고 예치금으로만 쇼핑한다. 현금이나 신용카드로는 구매할 수 없다.

l 가입 즉시 축하금 1000원을 예치금으로 지급한다. 예치금 10000원 적립시 12000원 입금으로 처리한다.

l 상품은 몇 가지 유형으로 나누고 유형에 따라 할인율, 배송비, 반품 여부가 다르다. 유형은 고정이며 편집 기능은 제공하지 않는다.

l 미성년자(19세 미만)에게는 성인용품을 판매하지 않는다.

이 규칙 중 일부는 모델링 단계에서 적용하고 나머지는 구현 단계에서 적용한다. 업무 규칙을 정리하는 수준으로 끝났지만 실제 프로젝트에서는 업무가 복잡해 분석 단계가 이보다 훨씬 길다.

2.회원 테이블

모델링의 첫 단계는 업무 분석 결과를 바탕으로 엔터티를 추출하는 것이다. 앞 항에서 작성한 규칙을 참조하여 무엇이 엔터티가 되는지 파악해 보자. 쇼핑을 하려면 사는 사람이 있어야 하고 팔 물건이 있어야 하니 회원과 상품 두 명사가 기본적인 엔터티이다.

회원은 상품을 구입하는 식으로 관계를 맺는다. 이 관계를 주문 엔터티로 정의한다. 주문이 없으면 회원과 상품이 관계를 맺을 수 없으므로 주문은 쇼핑몰의 주요 엔터티이다. 예치금과 고객의 나이, 주문 수량 등은 엔터티의 속성일 뿐 엔터티는 아니다.

엔터티 추출 결과 회원, 상품, 주문이라는 3개의 엔터티가 필요함을 파악하였다. 다음은 추출된 엔터티의 속성을 결정한다. 먼저 회원의 속성을 나열해 보고 이 중 쇼핑몰에 어떤 정보가 필요한지 생각해 보자. 회원은 사람이므로 다음과 같은 속성을 가지는데 이 중 업무에 사용할만한 속성만 추려낸다.

이름, 전화번호, 주소, 이메일, 나이, 키, 몸무게, 고향, 시력, 혈액형, 좋아하는 연예인, ....

이름은 기본 정보라 당연히 필요하고 배송을 위한 주소가 있어야 한다. 배송 안내를 위해 이메일이나 전화번호가 필요한데 요즘은 이메일이 보편적이다. 업무 규칙에 미성년자 어쩌고 하는 조항이 있어 나이는 필요하지만 키나 몸무게, 고향 등은 쇼핑몰 업무와는 하등의 상관이 없어 제외한다.

여기까지 회원 자신이 가진 고유한 속성을 선정하였다. 이 외에 업무에 필요한 인위적인 속성을 추가한다. 업무 규칙에 회원의 등급을 관리하는 항목이 있어 각 회원은 등급을 가진다. 예치금은 회원별로 관리되므로 회원의 속성으로 표현하는 것이 논리상 합당하다.

다음은 식별을 위한 기본키를 선정한다. 실명은 동명이인 문제로 기본키로 잘 쓰지 않는다. 전화번호나 주소는 한 집에 사는 두 회원(예를 들어 고시원이나 하숙생)이 따로 가입할 수 있어 검색 대상일 수는 있어도 기본키로는 부적합하다. 이럴 때는 별도의 ID를 쓰는 것이 보편적이다.

여기까지 모델링한 결과를 문서로 정리하여 구현 단계에서 실제 테이블로 생성한다. 책에서 모델링 문서를 보이기는 어려워 생성 스크립트로 대신한다. 회원 엔터티는 tMember 테이블로 구체화하고 속성에 적당한 이름을 붙였다. 다음이 회원 테이블 생성 스크립트이다.

-- 회원 테이블

CREATE TABLE tMember

(

member VARCHAR(20) PRIMARY KEY, -- 아이디

age INT NOT NULL, -- 나이

email VARCHAR(30) NOT NULL, -- 이메일

addr VARCHAR(50) NOT NULL, -- 주소

money INT DEFAULT 1000 NOT NULL, -- 예치금

grade INT DEFAULT 1 NOT NULL, -- 고객등급. 1=준회원, 2=정회원, 3=우수회원

remark VARCHAR(100) NULL -- 메모 사항

);

member 필드가 PK이며 회원의 고유한 이름이다. 실명도 따로 입력받아야 하지만 예제의 편의상 ID를 실명처럼 사용하기로 한다. 업무 규칙에 의거하여 예치금은 기본값 1000원을 적용하고 새로 가입한 회원의 초기 등급은 준회원이다. 메모 사항을 제외한 모든 필드는 NULL을 허용하지 않는다. 다음은 테스트를 위한 샘플 데이터이다. 실습에 꼭 필요한 만큼만 넣었다.

-- 회원 데이터

INSERT INTO tMember VALUES ('춘향',16,'1004@naver.com','전남 남원시',20000, 2, '');

INSERT INTO tMember VALUES ('이도령',18,'wolf@gmail.com','서울 신사동',150000, 3, '');

INSERT INTO tMember VALUES ('향단',25,'candy@daum.net','전남 남원시',5000, 2, '');

INSERT INTO tMember VALUES ('방자',28,'devlin@ssang.co.kr','서울 개포동',1000, 1, '요주의 고객');

샘플 데이터는 모델링 단계가 아닌 구현 단계에서 필요하지만 이 책에서는 구현 과정이 너무 멀리 떨어져 있고 다음 장부터 당장 쿼리 실습에 사용해야 하므로 여기서 입력했다. 잘 알려진 이름을 사용했으며 신상 정보도 대충 입력했다.

나이 필드가 정수 타입인 부분이 좀 의아한데 실제 프로젝트에서는 생년을 기록한 후 현재 날짜와 비교해서 나이를 계산하는 것이 보편적이다. 여기서는 예제의 일관성을 위해 그렇게 하지 않았다. 2021년에 실행하나 2039년에 실행하나 결과가 일정해야 한다.

3.상품 테이블

다음은 상품의 엔터티를 설계해 보자. 회원과 마찬가지로 속성을 나열하고 그중 업무에 필요한 속성을 선택한다. 다음은 일차 모델링한 결과와 샘플 데이터를 같이 표시한 것이다.

상품	제조사	재고	정가	유형	할인율	배송비	반품 여부
노트북	샘성	3	820000	가전	20	2500	y
모니터	알지	1	450000	가전	20	2500	y
사과	문경농원	24	16000	식품	0	3000	n
대추	보은농원	19	15000	식품	0	3000	n
전자담배	T&G	4	7000	성인	5	1000	n
청바지	방방	99	21000	패션	10	2000	y

일단은 상품명이 필요하고 제조사, 정가 등의 기본적인 정보도 있어야 한다. 재고 정보는 상품 자체의 속성은 아니지만 업무상 필요하다. 잘 팔리는 제품은 재고를 넉넉히 확보해 놔야 하며 인기없는 상품은 한두 개만 있어도 된다. 개별 상품별로 레코드를 정의할 필요는 없고 같은 상품은 레코드 하나로 정의하고 재고수만 기록한다. 고객 입장에서는 남은 재고중 어떤 상품을 구입하나 똑같다.

상품의 또 다른 속성으로 할인율과 배송비, 반품 여부 등이 있다. 식품은 포장비가 많이 들어 배송비가 비싸며 할인 여력이 없고 부패 위험이 있어 반품을 받지 않는다. 전자 제품은 마진이 높아 할인을 왕창 해 줘도 되고 반품도 받아 준다. 이런 규칙은 개별 상품마다 다른 것이 아니라 유형별로 결정된다. 즉, 기본키인 상품에 종속적이지 않고 일반 필드인 유형에 종속적이다.

1차 모델링한 위 테이블은 기본키가 아닌 일반 필드끼리 독립적이어야 한다는 제 3 정규화 규칙을 위반한다. 할인율, 배송비, 반품 여부가 계속 중복되어 노트북과 모니터, 사과와 대추의 뒷부분이 같다. 정규화하여 상품과 유형 테이블로 분리해야 한다. 수정된 모델링 결과는 다음과 같다.

상품	제조사	재고	정가	유형
노트북	샘성	3	820000	가전
모니터	알지	1	450000	가전
사과	문경농원	24	16000	식품
대추	보은농원	19	15000	식품
전자담배	T&G	4	7000	성인
청바지	방방	99	21000	패션

상품 엔터티에 직접 종속되지 않는 할인율, 배송비, 반품 여부는 제외한다. 유형과 관련된 정보는 별도의 유형 테이블로 분리한다.

유형	할인율	배송비	반품 여부
가전	20	2500	y
식품	0	3000	n
성인	5	1000	n
패션	10	2000	y

업무 규칙상 유형은 편집 가능하지 않다고 했으므로 이름이 중복될 가능성은 없고 따라서 유형 이름이 기본키로 적합하다. 유형과 상품은 부모 자식 관계를 구성하며 유형 외래키로 연결한다. 이제 이 두 테이블을 구현해 보자. 상품이 유형 정보를 참조하므로 유형 테이블을 먼저 정의한다.

-- 상품 유형 테이블

CREATE TABLE tCategory

(

category VARCHAR(10) PRIMARY KEY, -- 분류명

discount INT NOT NULL, -- 할인율

delivery INT NOT NULL, -- 배송비

takeback CHAR(1) -- 반품 가능성

);

-- 유형 데이터

INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('식품', 0, 3000, 'n');

INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('패션', 10, 2000, 'y');

INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('가전', 20, 2500, 'y');

INSERT INTO tCategory (category, discount, delivery, takeback) VALUES ('성인', 5, 1000, 'n');

모델링 결과대로 테이블을 생성하고 샘플 데이터도 삽입했다. 유형은 편집 기능이 없으므로 샘플이 곧 최종 데이터이다. 이처럼 자주 바뀌지 않고 읽기만 하는 정보를 룩업 테이블이라 한다. 네 개의 유형을 미리 정의했으며 유형에 따라 할인율, 배송비, 반품 여부 등이 다르다. 다음은 상품 테이블이다.

-- 상품 테이블

CREATE TABLE tItem

(

item VARCHAR(20) PRIMARY KEY, -- 상품명

company VARCHAR(20) NULL, -- 제조사

num INT NOT NULL, -- 재고

price INT NOT NULL, -- 정가

category VARCHAR(10) NOT NULL, -- 분류

CONSTRAINT item_fk FOREIGN KEY(category) REFERENCES tCategory(category)

);

-- 상품 데이터

INSERT INTO tItem (item,company,num,price,category) VALUES ('노트북', '샘성', 3, 820000, '가전');

INSERT INTO tItem (item,company,num,price,category) VALUES ('청바지', '방방', 80, 32000, '패션');

INSERT INTO tItem (item,company,num,price,category) VALUES ('사과', '문경농원', 24, 16000, '식품');

INSERT INTO tItem (item,company,num,price,category) VALUES ('대추', '보은농원', 19, 15000, '식품');

INSERT INTO tItem (item,company,num,price,category) VALUES ('전자담배', 'TNG', 4, 70000, '성인');

INSERT INTO tItem (item,company,num,price,category) VALUES ('마우스', '논리텍', 3, 90000, '가전');

상품명이 중복되는 경우도 가끔 있어 ID 필드가 있으면 좋지만 논리를 간단히 하기 위해 상품의 이름인 item을 PK로 지정했다. 담배 회사는 T&G가 맞지만 오라클이 &를 특수 기호로 쓰고 있어 TNG로 썼다. 상품의 분류는 category 필드로 지정하며 유형별 상세 정보를 구할 수 있는 외래키이다.

4.주문 테이블

다음은 가장 중요한 주문 엔터티를 테이블로 정의한다. 주문은 회원이 상품을 구입하는 동작인데 이 둘은 전형적인 다:다 관계이다. 그래서 상품, 회원 두 테이블만으로는 주문 관계를 표현할 수 없으며 중간에 두 엔터티의 관계를 1:다로 표현하는 주문 엔터티가 필요하다.

주문은 누가 언제 어떤 상품을 구입했는지 모두 포함해야 한다. 또 제대로 배송했는지 상태를 기록하는 속성도 있어야 하고 메모 사항도 남길 수 있어야 한다. 생성 스크립트를 보고 모델링된 결과를 분석해 보자. 자동 증가열 필드와 현재 날짜를 구하는 방식의 차이로 인해 DBMS별로 생성 스크립트를 따로 만들었다.

-- 주문 테이블

SQL Server :

CREATE TABLE tOrder

(

orderID INT IDENTITY PRIMARY KEY, -- 주문 번호

member VARCHAR(20) NOT NULL, -- 주문자

item VARCHAR(20) NOT NULL, -- 상품

orderDate DATE DEFAULT GETDATE() NOT NULL, -- 주문 날자

num INT NOT NULL, -- 개수

status INT DEFAULT 1 NOT NULL, -- 1:주문, 2:배송중, 3:배송완료, 4:반품

remark VARCHAR(1000) NULL -- 메모 사항

);

오라클 :

CREATE TABLE tOrder

(

orderID INT GENERATED AS IDENTITY PRIMARY KEY, -- 주문 번호

member VARCHAR(20) NOT NULL, -- 주문자

item VARCHAR(20) NOT NULL, -- 상품

orderDate DATE DEFAULT SYSDATE NOT NULL, -- 주문 날자

num INT NOT NULL, -- 개수

status INT DEFAULT 1 NOT NULL, -- 1:주문, 2:배송중, 3:배송완료, 4:반품

remark VARCHAR2(1000) NULL -- 메모 사항

);

MariaDB :

CREATE TABLE tOrder

(

orderID INT AUTO_INCREMENT PRIMARY KEY, -- 주문 번호

member VARCHAR(20) NOT NULL, -- 주문자

item VARCHAR(20) NOT NULL, -- 상품

orderDate DATE DEFAULT CURDATE() NOT NULL, -- 주문 날자

num INT NOT NULL, -- 개수

status INT DEFAULT 1 NOT NULL, -- 1:주문, 2:배송중, 3:배송완료, 4:반품

remark VARCHAR(1000) NULL -- 메모 사항

);

-- 주문 데이터

INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('춘향','청바지','2019-12-3',3,2);

INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('향단','대추','2019-12-4',10,1);

INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('방자','전자담배','2019-12-2',4,1);

INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('향단','사과','2019-12-5',5,2);

INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('흥부','노트북','2019-12-5',2,1);

INSERT INTO tOrder (member,item,orderDate,num,status) VALUES ('방자','핸드폰','2019-11-1',1,3);

member가 주문한 회원이고 item이 구입한 상품이다. 이 두 속성을(또는 주문 날짜까지 포함해서) 복합키로 묶을 수 있지만 관리의 편의를 위해 orderID 필드에 일련번호를 부여했다. member는 회원을 가리키는 외래키이며 item은 상품과 연결하는 외래키이다.

주문 고객을 who, 주문 상품을 what 등으로 해도 되지만 기본키와 외래키의 이름을 일치시키면 연결된 필드를 찾기 쉽다. 외래키에 대해서는 참조 무결성을 위해 외래키 제약을 걸어 두는 것이 원칙이다. 원래는 다음과 같이 해야 한다.

member VARCHAR(20) NOT NULL REFERENCES tMember(member), -- 주문자

item VARCHAR(20) NOT NULL REFERENCES tItem(item), -- 상품

주문한 회원은 회원 테이블에 존재해야 하고 구입한 상품은 상품 테이블에 있어야 한다. 그러나 회원이 탈퇴할 수도 있고 상품을 다 팔면 판매 목록에서 내리기도 해 현실적으로는 그렇지 않은 경우가 많다. 이런 경우를 표현하기 위해 외래키 제약을 걸지 않았다.

흥부의 노트북 주문은 비회원 구입의 예이며 방자의 핸드폰 주문은 판매 완료 상품에 대한 구입 기록이다. 이것이 바로 이론과 현실의 차이이다. 논리적으로는 분명 외래키 관계이지만 이런 경우까지 포괄하기 위해 물리적인 제약은 걸지 않아야 한다.

주문 날짜인 orderDate는 레코드를 삽입하는 시점이 곧 주문 날짜이므로 디폴트를 오늘로 설정했다. remark 필드는 주문 및 배송에 대한 여러 가지 메모나 잡다한 정보를 저장한다. 배송 사고나 얄미운 진상 고객에 대한 기록을 관리하는 메모란이 필요하다.

이상으로 쇼핑물 관리 데이터베이스에 대한 모델링을 완료했다. 테이블 개수가 고작 4개에 불과하지만 제법 그럴듯한 관계를 구성한다. 이상의 테이블을 별도의 Shopping 데이터베이스에 생성하는 것도 괜찮지만 DB 바꿔가며 실습하기 번거로워 실습 편의상 Study에 생성했다.

다음 장부터 이 테이블로 고급 문법을 실습할 것이다. 열심히 이것 저것 시도하다 보면 실수로 지우거나 변경하여 엉망이 될 것이다. 이럴 때는 테이블을 모두 지워 버리고 스크립트를 다시 돌려 재초기화하면 된다. 테이블을 지울 때는 참조 무결성 유지를 위해, 만든 순서의 역순으로 지운다.

DROP TABLE tOrder, tItem, tCategory, tMember;

SQL Server, MariaDB는 여러 테이블을 콤마로 구분하여 한 번에 삭제할 수 있으며 왼쪽부터 차례대로 삭제한다. 오라클은 한꺼번에 삭제하는 기능을 제공하지 않아 하나씩 순서대로 지워야 한다.