마케터를 위한 SQL : 연습용 데이터베이스 구축 (CREATE, INSERT)

Sean Lee·2022년 8월 20일
1

마케터를 위한 SQL

목록 보기
3/7
post-thumbnail

연습용 데이터베이스 모델링

본 블로그 시리즈는 데이터베이스 모델링이 위주가 아니다보니 향후 실습을 위해 필요한 최소의 배경과 상황 설명을 위한 모델링 내용을 담았습니다.
이번 글은 향후 진행될 SQL 연습에서 활용될 데이터베이스를 구축하는 과정입니다. 글을 읽고 계시는 마케터 혹은 비전공자 여러분께서 지금 반드시 숙지해야 될 내용은 아니기 때문에 건너뛰셔도 괜찮습니다. 모델링과 관련된 설명이 다소 부족할 수 있습니다.

데이터베이스 모델링이란?
현실 세계의 데이터를 단순화 시켜 컴퓨터 세계의 데이터베이스로 옮기는 변환 과정이며, 비즈니스의 요구 조건, 상황 및 배경을 반영한 데이터베이스의 설계라고 이해해도 좋습니다.

배경

SQL연습을 위해 제가 방금 만들어낸 회사인 "에브리바디"는 개인이 필요한 비타민 종류에 따라 그에 맞는 한달치 비타민을 집이나 직장에 정기적으로 보내주는 서비스를 준비하고 있습니다. 이에 회사는 웹사이트를 구축하기 위한 데이터베이스가 필요한 상황입니다.

조건

  • 회사는 구독 경제 모델(콘텐츠형 : OTT / 정기배송형 : 면도날, 간식, 화장품 등 / 렌탈형 : 비데, 정수기, 자동차 등 / SaaS형 : 소프트웨어 구독 서비스) 중 정기 배송형 모델을 적용중인 회사를 가정합니다.
  • 최소한의 테이블로 {회원정보, 빌링정보, 결제정보, 상품정보}가 필요합니다.
  • 회사는 고객에게 주기적으로 배송해야 하는 상품을 가지고 있습니다.
  • 회사는 고객에게 상품별 월단위 요금을 받고 있으며 결제는 신용카드를 통한 자동결제만 지원합니다.
  • 마지막 접속일로부터 1년이 경과하면, 고객은 휴면고객으로 전환됩니다.
  • 결제 주기 정보 (연단위 결제 월단위 결제 등) 정보가 포함되어야 합니다.
  • 단 한번도 구매하지 않은 고객이 존재할 수 있습니다.
  • 카드정보 등록시 카드사, 카드번호, 만기일, 비밀번호 앞2자리 정보는 반드시 입력되어야 합니다.
  • 동일한 상품을 주기적으로 구매한 고객이 존재할 수 있습니다.
  • 카드정보를 입력하지 않은 고객이 존재할 수 있습니다.
  • 하나 이상의 카드 정보를 등록해둘 수 있습니다.
  • 여러 카드정보가 있는 경우, 결제 우선순위 정보가 포함되어야 합니다.
  • 주문은 반드시 1개 이상의 상품을 포함해야 합니다.
  • 주문시 수령인의 이름, 주소, 전화번호 정보는 반드시 입력되어야 합니다.
  • 마케팅 활동을 위해 할인에 따른 상품의 판매 가격이 변경될 수 있습니다.
  • 모든 기록의 생성과 수정 시각정보를 기록합니다.

(방금 급하게 지어낸 DB 모델인 만큼.. 디테일이나 정규화는 좀 떨어질 수 있습니다. 양해부탁드립니다.🙏 물류 및 재고 관련 내용은 포함하지 않았습니다.)



데이터베이스 및 테이블 생성을 위한 SQL (CREATE 명령어)

-- 데이터베이스 생성
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`
);


데이터 테스트 입력을 위한 SQL (INSERT 명령어)

데이터의 입력 순서
1. 회원 > 2. 빌링 > 3. 상품 > 4. 주문 순서

1. 회원 데이터 입력

웹사이트에서 회원 가입 발생시 고객이 입력하는 데이터가 저장된다고 가정합니다.
고객이 회원 가입 페이지에서 가입에 필요한 항목을 입력한 뒤 "가입"을 누르면 입력된 정보를 바탕으로 아래의 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') 입력

입력이 완료된 화면 (줌인)



2.빌링 데이터 입력

가입한 고객이 카드정보를 등록하는 상황을 가정합니다.
고객과 빌링 정보의 관계는 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')을 입력

입력완료 화면 (줌인)



3. 상품 데이터 입력

상품데이터는 업체가 고객에게 제공하고자 하는 재화(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')을 입력하겠습니다.

입력완료 화면 (줌인)



4. 주문 데이터 입력

주문은 고객이 상품을 구매할때 발생되는 데이터입니다. 따라서 고객이 특정되어야 하며, 고객이 구매하는 상품이 특정되어야 하는 테이블 입니다. 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')을 입력

입력완료 화면 (줌인)


데이터 보안

개인정보 보호법에 따라 데이터베이스에 저장된 정보 중 개인을 식별할 수 있는 정보 (성명, 주민등록번호, 휴대전화 번호 등)에 대한 암호화를 해야합니다. 하지만 분석의 편의를 위해 암호화를 하지 않은 상태에서 향후 분석을 진행하도록 하겠습니다. 앞으로 연습하게 될 데이터베이스의 데이터는 모두 가짜로 지어낸 데이터로 현실의 데이터가 전혀 반영되지 않았습니다.


정리

  1. 데이터베이스 모델링에 대한 간략한 설명 (구독형 비타민 서비스 "에브리바디")
  2. 데이터베이스 생성 (CREATE DATABASE)
  3. 테이블 생성 (CREATE TABLE)
  4. 데이터 입력 (INSERT INTO)
    • 회원 데이터 입력
    • 빌링 데이터 입력
    • 상품 데이터 입력
    • 주문 데이터 입력
  5. 데이터 보안 : 개인정보 보안법에 따라 개인을 식별할 수 있는 정보는 암호화를 해야한다.

감사합니다 :)

profile
배우기만 하고 생각하지 않으면 얻는 것이 없고, 생각만 하고 배우지 않으면 위태롭다.

0개의 댓글