본 블로그 시리즈는 데이터베이스 모델링이 위주가 아니다보니 향후 실습을 위해 필요한 최소의 배경과 상황 설명을 위한 모델링 내용을 담았습니다.
이번 글은 향후 진행될 SQL 연습에서 활용될 데이터베이스를 구축하는 과정입니다. 글을 읽고 계시는 마케터 혹은 비전공자 여러분께서 지금 반드시 숙지해야 될 내용은 아니기 때문에 건너뛰셔도 괜찮습니다. 모델링과 관련된 설명이 다소 부족할 수 있습니다.
데이터베이스 모델링이란?
현실 세계의 데이터를 단순화 시켜 컴퓨터 세계의 데이터베이스로 옮기는 변환 과정이며, 비즈니스의 요구 조건, 상황 및 배경을 반영한 데이터베이스의 설계라고 이해해도 좋습니다.
SQL연습을 위해 제가 방금 만들어낸 회사인 "에브리바디"는 개인이 필요한 비타민 종류에 따라 그에 맞는 한달치 비타민을 집이나 직장에 정기적으로 보내주는 서비스를 준비하고 있습니다. 이에 회사는 웹사이트를 구축하기 위한 데이터베이스가 필요한 상황입니다.
(방금 급하게 지어낸 DB 모델인 만큼.. 디테일이나 정규화는 좀 떨어질 수 있습니다. 양해부탁드립니다.🙏 물류 및 재고 관련 내용은 포함하지 않았습니다.)
-- 데이터베이스 생성
CREATE DATABASE everyone;
-- 데이터베이스 사용
USE everyone;
CREATE TABLE `products` (
`product_id` INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`product_code` VARCHAR(255) NULL,
`product_name` VARCHAR(255) NULL,
`product_category` VARCHAR(255) NULL,
`sale_price` VARCHAR(255) NULL,
`marketing_ratio` VARCHAR(255) NULL,
`list_price` VARCHAR(255) NULL,
`description` VARCHAR(255) NULL,
`unit_cost` VARCHAR(255) NULL,
`pay_type` VARCHAR(255) NULL,
`status` VARCHAR(255) NULL,
`modified_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE `users` (
`user_id` INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`phone` VARCHAR(255) NULL,
`email` VARCHAR(255) NULL,
`user_status` VARCHAR(255) NULL,
`user_class` VARCHAR(255) NULL,
`marketing_agree` BOOLEAN NOT NULL DEFAULT 0,
`social_login` VARCHAR(255) NULL,
`last_logged_at` DATETIME NULL,
`modified_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE `orders` (
`order_id` INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` INT(255) NOT NULL,
`product_id` INT(255) NOT NULL,
`order_price` VARCHAR(255) NULL,
`order_number` VARCHAR(255) NULL,
`order_name` VARCHAR(255) NOT NULL,
`order_email` VARCHAR(255) NULL,
`order_phone` VARCHAR(255) NOT NULL,
`order_address` VARCHAR(255) NOT NULL,
`order_city` VARCHAR(255) NOT NULL,
`order_country` VARCHAR(255) NOT NULL,
`postal_code` VARCHAR(255) NOT NULL,
`subscription` VARCHAR(255) NULL,
`order_status` VARCHAR(255) NULL,
`modified_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE `billing` (
`billing_id` INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` INT(255) NOT NULL,
`credit_card_vendor` VARCHAR(255) NOT NULL,
`credit_card_number` VARCHAR(255) NOT NULL,
`credit_card_pin` VARCHAR(255) NOT NULL,
`credit_card_status` BOOLEAN NOT NULL DEFAULT 1,
`credit_card_expire` VARCHAR(255) NOT NULL,
`billing_primary` BOOLEAN NOT NULL DEFAULT 1,
`modified_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE `products` ADD CONSTRAINT `PK_PRODUCTS` PRIMARY KEY (
`product_id`
);
ALTER TABLE `users` ADD CONSTRAINT `PK_USERS` PRIMARY KEY (
`user_id`
);
ALTER TABLE `orders` ADD CONSTRAINT `PK_ORDERS` PRIMARY KEY (
`order_id`,
`user_id`,
`product_id`
);
ALTER TABLE `billing` ADD CONSTRAINT `PK_BILLING` PRIMARY KEY (
`billing_id`,
`user_id`
);
ALTER TABLE `orders` ADD CONSTRAINT `FK_users_TO_orders_1` FOREIGN KEY (
`user_id`
)
REFERENCES `users` (
`user_id`
);
ALTER TABLE `orders` ADD CONSTRAINT `FK_products_TO_orders_1` FOREIGN KEY (
`product_id`
)
REFERENCES `products` (
`product_id`
);
ALTER TABLE `billing` ADD CONSTRAINT `FK_users_TO_billing_1` FOREIGN KEY (
`user_id`
)
REFERENCES `users` (
`user_id`
);
데이터의 입력 순서
1. 회원 > 2. 빌링 > 3. 상품 > 4. 주문 순서
웹사이트에서 회원 가입 발생시 고객이 입력하는 데이터가 저장된다고 가정합니다.
고객이 회원 가입 페이지에서 가입에 필요한 항목을 입력한 뒤 "가입"을 누르면 입력된 정보를 바탕으로 아래의 INSERT 쿼리가 실행됩니다.
INSERT INTO users(
name
, phone
, email
, user_status
, user_class
, marketing_agree
, social_login
, last_logged_at
)
VALUES(
'Sean Lee'
, '010-1234-1234'
, 'wansunlee@gmail.com'
, 'active'
, '일반고객'
, 1
, 'kakao'
, '2022-08-28'
);
SQL 키워드 (Key Word)
=> INSERT (DML), INTO, VALUES
SQL 속성 (Attribute)
=> name, phone, email, user_status, user_class, marketing_agree, social_login, last_logged_at
SQL쿼리 해석
=> user 테이블의 8개 속성에 대한 값('Sean Lee', '010-1234-1234', 'wansunlee@gmail.com', 'active', '일반고객', 1, 'kakao', '2022-08-28') 입력
입력이 완료된 화면 (줌인)
가입한 고객이 카드정보를 등록하는 상황을 가정합니다.
고객과 빌링 정보의 관계는 1:{0 or 1 or many}으로 한명의 고객은 카드 정보가 없거나, 하나의 카드정보만 있거나, 여러개의 카드정보가 있는 상황을 가정합니다. 앞서 생성된 고객의 user_id 는 1 입니다.
INSERT INTO billing(
user_id
, credit_card_vendor
, credit_card_number
, credit_card_pin
, credit_card_status
, credit_card_expire
, billing_primary
, modified_at
)
VALUES(
1
,'AB카드'
, '1234-1234-1234-1234'
, '00'
, 1
, 0826
, 1
, '2022-08-28'
);
SQL 키워드 (Key Word)
=> INSERT (DML), INTO, VALUES
SQL 속성 (Attribute)
=> user_id, credit_card_vendor, credit_card_number, credit_card_pin, credit_card_status, credit_card_expire, billing_primary, modified_at
SQL쿼리 해석
=> billing 테이블의 8개 속성에 대한 값(1,'AB카드', '1234-1234-1234-1234', '00', 1, 0826, 1, '2022-08-28')을 입력
입력완료 화면 (줌인)
상품데이터는 업체가 고객에게 제공하고자 하는 재화(Product)나 서비스(Service)에 대한 정보를 담고 있는 데이터입니다. 상품 정보를 바탕으로 고객은 주문과 구매를 할 수 있습니다.
-- 마케팅 프로모션 미적용 상품 (할인율 0%)
INSERT INTO products(
product_code
, product_name
, product_category
, sale_price
, marketing_ratio
, list_price
, description
, unit_cost
, pay_type
, status
, modified_at
)
VALUES(
'vitamin_c_001'
, '고구려은단 비타민 C'
, 'vitamin_c'
, '30000'
, '0' -- 할인율 상세
, '30000'
, '비타민 C 월결제 상품'
, '22000'
, 'monthly'
, 'on_sale'
, '2022-08-28'
),
(
'vitamin_c_001'
, '[5% 할인] 고구려은단 비타민 C'
, 'vitamin_c'
, '28500'
, '5' -- 할인율 상세
, '30000'
, '연결제 구매시 비타민 C 월간 5%할인 상품'
, '22000'
, 'annual'
, 'on_sale'
, '2022-08-28'
);
SQL 키워드 (Key Word) : INSERT (DML), INTO, VALUES
SQL 속성 (Attribute) : product_code, product_name, product_category, sale_price, marketing_ratio, list_price, description, unit_cost, pay_type, status, modified_at
SQL쿼리 해석 : products 테이블의 11개 속성(product_code, product_name, product_category, sale_price, marketing_ratio, list_price, description, unit_cost, pay_type, status, modified_at)에 대한 11개의 값('vitamin_c_001', '고구려은단 비타민 C', 'vitamin_c', '30000', '0', '30000', '비타민 C 월결제 상품', '22000', 'monthly', 'on_sale', '2022-08-28')과 ('vitamin_c_001', '고구려은단 비타민 C', 'vitamin_c', '30000', '0', '30000', '비타민 C 월결제 상품', '22000', 'monthly', 'on_sale', '2022-08-28')을 입력하겠습니다.
입력완료 화면 (줌인)
주문은 고객이 상품을 구매할때 발생되는 데이터입니다. 따라서 고객이 특정되어야 하며, 고객이 구매하는 상품이 특정되어야 하는 테이블 입니다. user_id를 통해 고객을 특정하고, product_id를 통해 상품을 특정합니다. 앞서 생성한 user_id 값은 1이며, product_id 값은 2(5% 할인버전)입니다.
INSERT INTO orders(
user_id
, product_id
, order_price
, order_number
, order_name
, order_email
, order_phone
, order_address
, order_city
, postal_code
, order_price
, subscription
, order_status
, modified_at
)
VALUES(
1
, 2
, '28500'
, '20220828_1'
, 'Sean'
, 'wansunlee@gmail.com'
, '010-1234-1234'
, '서울특별시 마포구 양화로 지하 55'
, '서울시'
, '123-123'
, '22000'
, 'annual'
, 'paid'
, '2022-08-28'
);
SQL 키워드 (Key Word)
=> INSERT (DML), INTO, VALUES
SQL 속성 (Attribute)
=> user_id, product_id, order_number, order_name, order_email, order_phone, order_address, order_city, postal_code, order_price, subscription, order_status, modified_at
SQL쿼리 해석 : orders 테이블의 13개 속성에 대한 값(1, 2, '20220828_1', 'Sean', 'wansunlee@gmail.com', '010-1234-1234', '서울특별시 마포구 양화로 지하 55', '서울시', '123-123', '22000', 'annual', 'paid', '2022-08-28')을 입력
입력완료 화면 (줌인)
개인정보 보호법에 따라 데이터베이스에 저장된 정보 중 개인을 식별할 수 있는 정보 (성명, 주민등록번호, 휴대전화 번호 등)에 대한 암호화를 해야합니다. 하지만 분석의 편의를 위해 암호화를 하지 않은 상태에서 향후 분석을 진행하도록 하겠습니다. 앞으로 연습하게 될 데이터베이스의 데이터는 모두 가짜로 지어낸 데이터로 현실의 데이터가 전혀 반영되지 않았습니다.
감사합니다 :)