비즈니스에 따른 테이블 설계
이번 스프링 강의 중 과제를 제출해야 할 일이 생겼고, 블로그 작성으로 과제를 제출해야하 하여 포스팅 하게 되었다.
테이블 설계는 매우 까다롭고 어려운 작업이라고 생각한다.
이 어려운 테이블 설계를 요구에 맞춰서 커스텀 해주는 것이 이번 과제의 목표이다.
과제 내용 자체는 복잡한 로직은 아니지만, 1:N 관계와 N:N 관계를 잘 이해하고 있는지를 물어보는 과제라고 생각한다..
![](https://cdn.discordapp.com/attachments/1086144700672647192/1113414535768969246/8e3cacd8a26d4c7c.jpeg)
요구사항
이번 과제는 크게 3가지 테이블이 유기적으로 작업한다.
- User Table
- Account Table
- History Table
차근차근 하나씩 만들어 보며 진행을 해보자.
![](https://cdn.discordapp.com/attachments/1086144700672647192/1117669068485427240/image.png)
![](https://cdn.discordapp.com/attachments/1086144700672647192/1117669526167883826/image.png)
User Table
기본적으로 우리가 은행 계좌를 만들 경우 입력하는 정보를 러프하게 만들어 볼 생각이다.
필요한 부분을 고민을 해보자.
id
: 회원의 일련번호int
: 정수형으로 받는다.auto_increment
: 자동으로 id를 1씩 증가하며 만들어준다.primary key
: 회원의 일련번호는 만드시 하나여야 하므로 기본키로 해준다.
name
: 회원의 이름varchar(50)
: char형으로 이름을 만든다.not null
: 회원 정보는 null을 허용하지 않는다.
user_id
: 회원가입시 아이디varchar(100)
: char형으로 아이디를 만든다.not null
: 회원 정보는 null을 허용하지 않는다.
password
: 회원가입시 비밀번호varchar(100)
: char형으로 비밀번호를 만든다.not null
: 회원 정보는 null을 허용하지 않는다.
phone
: 핸드폰 번호varchar(20)
: char형으로 비밀번호를 만든다.not null
: 회원 정보는 null을 허용하지 않는다.
create_at
: 회원가입한 일자timestamp
: 현재 일자로 등록not null
: 회원 정보는 null을 허용하지 않는다.
User Table SQL
위 요구사항을 토대로 users의 테이블을 만들어 줬다.
- User Table 생성
create table users_tb (
id int auto_increment primary key,
name varchar(50) not null,
user_signid varchar(100) not null,
password varchar(100) not null,
phone varchar(20) not null,
create_at timestamp not null
);
- User Table 더미데이터 넣기
insert into users_tb(name, user_signid, password, phone, create_at) values('Shirley', 'Shirley123', '1111', '01012341234', now());
insert into users_tb(name, user_signid, password, phone, create_at) values('Raymon', 'Raymon123', '2222', '01011112222', now());
insert into users_tb(name, user_signid, password, phone, create_at) values('Maria', 'Maria123', '3333', '01033334444', now());
insert into users_tb(name, user_signid, password, phone, create_at) values('Charles', 'Charles123', '4444', '01055556666', now());
insert into users_tb(name, user_signid, password, phone, create_at) values('Jodie', 'Jodie123', '5555', '01077778888', now());
![](https://cdn.discordapp.com/attachments/1086144700672647192/1117681197720555560/image.png)
Account Table
여기서부터 문제가 생긴다. Account Table
은 User Table
과 관계를 고민해야 한다.
유저는 계좌를 여러개를 가질 수 있다. 즉, 1: N 관계다.
User 1 : Account N
id
: 계좌의 일련번호int
: 정수형으로 받는다.auto_increment
: 자동으로 id를 1씩 증가하며 만들어준다.primary key
: 계좌의 일련번호는 만드시 하나여야 하므로 기본키로 해준다.
number
: 계좌 번호varchar(50)
: char형으로 이름을 만든다.unique
: 계좌번호는 반드시 하나여야 하므로 unique하다.not null
: 계좌 정보는 null을 허용하지 않는다.
password
: 계좌 비밀번호varchar(10)
: char형으로 비밀번호를 만든다.not null
: 계좌 정보는 null을 허용하지 않는다.
balance
: 계좌에 잔액bigint
: 잔액은 크기를 최대한 큰 데이터 형으로한다.default 1000
: 계좌 생성시 은행원이 고객에게 1000원을 받아 계좌를 생성해준다.not null
: 계좌 정보는 null을 허용하지 않는다.
create_at
: 회원가입한 일자timestamp
: 현재 일자로 등록not null
: 계좌 정보는 null을 허용하지 않는다.
user_id
: 계좌를 등록한 회원의 idUser
는 여러개의 계좌를 생성할 수 있으므로, 1 : N 관계이다. 즉,Account Table
에서FK
를 생성해야한다.
Account Table SQL
위 요구사항을 토대로 Account Table을 만들어 줬다.
- Account Table 생성
create table account_tb (
id int auto_increment primary key,
number varchar(50) unique not null,
password varchar(10) not null,
balance bigint default 1000 not null,
create_at timestamp not null,
user_id int,
foreign key (user_id) references users_tb(id)
);
- Account Table 더미데이터 넣기
insert into account_tb(number, password, create_at, user_id) values('1111', '1111', now(), 1);
insert into account_tb(number, password, create_at, user_id) values('2222', '2222', now(), 2);
insert into account_tb(number, password, create_at, user_id) values('3333', '3333', now(), 3);
insert into account_tb(number, password, create_at, user_id) values('4444', '4444', now(), 4);
insert into account_tb(number, password, create_at, user_id) values('5555', '5555', now(), 5);
- 결과
select * from account_tb;
History Table
History Table은 계좌의 이체 기록을 남겨두는 테이블이다.
id
: 기록의 일련번호int
: 정수형으로 받는다.auto_increment
: 자동으로 id를 1씩 증가하며 만들어준다.primary key
: 기록의 일련번호는 만드시 하나여야 하므로 기본키로 해준다.
amount
: 계좌에 전송 금액bigint
: 잔액은 크기를 최대한 큰 데이터 형으로한다.not null
: 계좌 정보는 null을 허용하지 않는다.
d_balance
: 입금 후 계좌 잔액bigint
: 잔액은 크기를 최대한 큰 데이터 형으로한다.
w_balance
: 출금 후 계좌 잔액bigint
: 잔액은 크기를 최대한 큰 데이터 형으로한다.
d_number_id
: 입금 계좌 정보FK
로Account Table
에number
를 가져와야 한다.
w_number_id
: 출금 계좌 정보FK
로Account Table
에number
를 가져와야 한다.
create_at
: 회원가입한 일자timestamp
: 현재 일자로 등록not null
: 기록 정보는 null을 허용하지 않는다.
History Table SQL
위 요구사항을 토대로 Account Table을 만들어 줬다.
- History Table 생성
create table history_tb (
id int auto_increment primary key,
amount bigint,
d_balance bigint,
w_balance bigint,
d_number_id int,
w_number_id int,
create_at timestamp not null,
foreign key (d_number_id) references account_tb(id),
foreign key (w_number_id) references account_tb(id)
);
- History Table 테스트
start transaction;
set @amount := 100;
set @d_number_id := 1;
set @w_number_id := 2;
update account_tb set balance = (select d.balance from (select balance from account_tb where id = @d_number_id) d) + @amount where id = @d_number_id;
update account_tb set balance = (select w.balance from (select balance from account_tb where id = @w_number_id) w) - @amount where id = @w_number_id;
set @d_balance := (select balance from account_tb where id = @d_number_id);
set @w_balance := (select balance from account_tb where id = @w_number_id);
insert into history_tb(amount, d_balance, w_balance, d_number_id, w_number_id, create_at) values(@amount, @d_balance, @w_balance, @d_number_id, @w_number_id, now());
commit;
1번 계좌가 2번 계좌한테 100원을 입금해준 테스트 코드이다.
- 결과
![](https://cdn.discordapp.com/attachments/1086144700672647192/1117706287183044719/image.png)
![](https://cdn.discordapp.com/attachments/1086144700672647192/1117706563453460481/image.png)
Index 설정
History Table을 index 설정을 해주었다. 인덱스 설정하는 법은 알지만 왜 해야하고, 어떤 부분에서 강점을 가지고 있는지를 아직 잘 모르겠다. 공부가 필요한 것 같다. 다음 포스팅으로 리펙토링을 진행해야겠다.
create index w_number_id on history_tb(w_number_id);
create index d_number_id on history_tb(d_number_id);
![](https://cdn.discordapp.com/attachments/1086144700672647192/1117707512188239994/image.png)
ERD
![](https://cdn.discordapp.com/attachments/1086144700672647192/1117709628520812584/image.png)
'SW > Data Base' 카테고리의 다른 글
비즈니스 모델 설계 - Spring Assignment1 과제(feat.MySQL) (0) | 2023.06.12 |
---|---|
[DB] Schema & Query Design (0) | 2022.01.16 |
[DB] Transaction Isolation Level (트랜잭션 고립 수준) (0) | 2022.01.16 |
[DB] Transaction Concurrency control (동시성 제어) (0) | 2022.01.16 |
[DB] Transaction (0) | 2022.01.16 |