월구독으로 제공하는 AI 서비스 시스템을 개발하려고 합니다. 아래와 같은 두 개 화면의 요건이 제공되어 있습니다. 기능을 구현할 수 있는 주요 엔터티와 주요 속성을 설계하세요.
저는 다음과 같이 설계를 해봤습니다.
샘플 데이터를 같이 살펴보면 다음과 같습니다.
회원결제기록에 대해서는 고민이 필요합니다. Free 요금제에 대해서 결제기록을 남겨야 할까? 개인적으로는 결제라는 행위가 없었으므로 남길 필요가 없어 보입니다. 하지만, 개발측과 업무에 따라 다음과 같이 0원으로 남겨야 할 수도 있겠죠. 이에 따라 다양한 장점과 단점이 공존하게 될거라 생각합니다. 대표적인 장점은 특정 회원이 Free를 사용하다 Light로 요금제를 변경했다면, 그러한 변화를 월별로 보여주는 처리가 쉽다는 장점이 있을 것입니다. 대표적인 단점은 실제 행하지 않은 결제 데이터가 남아, 순수 결제 건수를 카운트 할 때 혼선이 발생할 수 있습니다. 어느쪽이 전체적으로 시스템에 유리한지 판단해볼 필요가 있습니다.
재밌는 작업을 하나 해봤습니다. GPT를 통해 테이블 CREATE SQL과 INSERT SQL도 만들어보는 것이죠. GPT에 테이블 하나와, 그에 따른 엑셀 샘플 데이터를 주면서 대화창에 보여주면서 작업을 진행했습니다. 다음과 같은 스크립트를 비교적 쉽게 얻을수 있었네요.
CREATE DATABASE remodb;
USE remodb;
CREATE TABLE 회원 (
회원ID VARCHAR(10) NOT NULL
,PRIMARY KEY(회원ID)
);
INSERT INTO 회원 (회원ID) VALUES ('M01'), ('M02'), ('M03');
INSERT INTO 회원 (회원ID) VALUES('M04'); # 실수로 누락.
CREATE TABLE 상품 (
상품ID VARCHAR(10) NOT NULL,
상품명 VARCHAR(20),
결제주기 VARCHAR(20),
구독료액 INT,
PRIMARY KEY(상품ID)
);
INSERT INTO 상품 (상품ID, 상품명, 결제주기, 구독료액) VALUES
('I01', 'Free', '월', 0),
('I02', 'Light', '월', 5000),
('I03', 'Standard', '월', 10000);
CREATE TABLE 기능 (
기능ID VARCHAR(10) NOT NULL PRIMARY KEY,
기능명 VARCHAR(30),
제공단위 VARCHAR(10)
);
INSERT INTO 기능 (기능ID, 기능명, 제공단위) VALUES
('E01', 'AI감상', '회'),
('E02', 'AI음성경로', '분'),
('E03', 'AI번역', '자'),
('E04', 'AI주식분석', '회');
CREATE TABLE 상품별기능 (
상품ID VARCHAR(10),
기능ID VARCHAR(10),
제공수량 INT,
PRIMARY KEY (상품ID, 기능ID),
FOREIGN KEY (상품ID) REFERENCES 상품(상품ID),
FOREIGN KEY (기능ID) REFERENCES 기능(기능ID)
);
INSERT INTO 상품별기능 (상품ID, 기능ID, 제공수량) VALUES
('I01', 'E01', 100),
('I01', 'E02', 100),
('I01', 'E03', 50000),
('I01', 'E04', 100),
('I02', 'E01', 400),
('I02', 'E02', 400),
('I02', 'E03', 200000),
('I02', 'E04', 400),
('I03', 'E01', 1000),
('I03', 'E02', 1000),
('I03', 'E03', 500000),
('I03', 'E04', 1000);
CREATE TABLE 회원상품구독 (
회원ID VARCHAR(10),
구독시작일시 DATE,
상품ID VARCHAR(10),
구독상태 VARCHAR(20),
구독종료일시 DATE,
PRIMARY KEY (회원ID, 구독시작일시, 상품ID), # PK 순서를 GPT가 잘못 구성함.
FOREIGN KEY (회원ID) REFERENCES 회원(회원ID),
FOREIGN KEY (상품ID) REFERENCES 상품(상품ID)
);
INSERT INTO 회원상품구독 (회원ID, 구독시작일시, 상품ID, 구독상태, 구독종료일시) VALUES
('M01', '2024-01-04', 'I02', '구독중', NULL),
('M02', '2024-03-31', 'I01', '구독중', NULL),
('M03', '2024-05-10', 'I03', '구독중', NULL),
('M04', '2024-02-10', 'I02', '구독해지', '2024-06-29');
# FK 정보를 GPT에 제공하지 않아, GPT가 임의로 FK를 설정함.
# FK 관련은 수작업으로 변경
CREATE TABLE 회원결제기록 (
회원ID VARCHAR(10),
구독시작일시 DATE,
상품ID VARCHAR(10),
결제일시 DATE,
결제액 INT,
결제수단 VARCHAR(20),
결제수단구분 VARCHAR(20),
결제카드번호 VARCHAR(20),
PRIMARY KEY (회원ID, 상품ID, 구독시작일시, 결제일시),
FOREIGN KEY (회원ID, 구독시작일시, 상품ID) REFERENCES 회원상품구독(회원ID, 구독시작일시, 상품ID)
# FOREIGN KEY (회원ID) REFERENCES 회원(회원ID),
# FOREIGN KEY (상품ID) REFERENCES 상품(상품ID)
);
INSERT INTO 회원결제기록 (회원ID, 구독시작일시, 상품ID, 결제일시, 결제액, 결제수단, 결제수단구분, 결제카드번호) VALUES
('M01', '2024-01-04', 'I02', '2024-01-04', 5000, '결제완료', '카드', '1234-789'),
('M01', '2024-01-04', 'I02', '2024-02-04', 5000, '결제완료', '카드', '1234-789'),
('M01', '2024-01-04', 'I02', '2024-03-04', 5000, '결제완료', '카드', '1234-789'),
('M01', '2024-01-04', 'I02', '2024-04-04', 5000, '결제완료', '카드', '1234-789'),
('M01', '2024-01-04', 'I02', '2024-05-04', 5000, '결제완료', '카드', '1234-789'),
('M01', '2024-01-04', 'I02', '2024-06-04', 5000, '결제완료', '카드', '1234-789'),
('M01', '2024-01-04', 'I02', '2024-07-04', 5000, '결제완료', '카드', '1234-789'),
('M01', '2024-01-04', 'I02', '2024-08-04', 5000, '결제완료', '카드', '1234-789'),
('M02', '2024-03-31', 'I01', '2024-03-31', 10000, '결제완료', '카드', '2345-578'),
('M02', '2024-03-31', 'I01', '2024-04-30', 10000, '결제완료', '카드', '2345-578'),
('M02', '2024-03-31', 'I01', '2024-05-31', 10000, '결제완료', '카드', '2345-578'),
('M02', '2024-03-31', 'I01', '2024-06-30', 10000, '결제완료', '카드', '2345-578'),
('M02', '2024-03-31', 'I01', '2024-07-31', 10000, '결제완료', '카드', '2345-578'),
('M02', '2024-03-31', 'I01', '2024-08-31', 10000, '결제완료', '카드', '2345-578'),
('M03', '2024-05-10', 'I03', '2024-05-10', 10000, '결제완료', '카드', '2345-578'),
('M03', '2024-05-10', 'I03', '2024-06-10', 10000, '결제완료', '카드', '2345-578'),
('M03', '2024-05-10', 'I03', '2024-07-10', 10000, '결제완료', '카드', '2345-578'),
('M03', '2024-05-10', 'I03', '2024-08-10', 10000, '결제완료', '카드', '2345-578'),
('M04', '2024-02-10', 'I02', '2024-02-10', 5000, '결제완료', '카드', '3456-987'),
('M04', '2024-02-10', 'I02', '2024-03-10', 5000, '결제완료', '카드', '3456-987'),
('M04', '2024-02-10', 'I02', '2024-04-10', 5000, '결제완료', '카드', '3456-987'),
('M04', '2024-02-10', 'I02', '2024-05-10', 5000, '결제완료', '카드', '3456-987'),
('M04', '2024-02-10', 'I02', '2024-06-10', 5000, '결제완료', '카드', '3456-987');
SQL
복사
이상입니다.! 기회가 되면 모델을 더 발전시키고 상황에 맞는 SQL 추출도 해보면 좋겠네요.