비즈니스 모델 설계 - Spring Assignment1 과제(feat.MySQL)

비즈니스에 따른 테이블 설계

이번 스프링 강의 중 과제를 제출해야 할 일이 생겼고, 블로그 작성으로 과제를 제출해야하 하여 포스팅 하게 되었다.

테이블 설계는 매우 까다롭고 어려운 작업이라고 생각한다.
이 어려운 테이블 설계를 요구에 맞춰서 커스텀 해주는 것이 이번 과제의 목표이다.

과제 내용 자체는 복잡한 로직은 아니지만, 1:N 관계와 N:N 관계를 잘 이해하고 있는지를 물어보는 과제라고 생각한다..

 

 

 

요구사항

이번 과제는 크게 3가지 테이블이 유기적으로 작업한다.

• User Table
• Account Table
• History Table

차근차근 하나씩 만들어 보며 진행을 해보자.

 

 

 

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());

 

select * from users_tb;

 

 

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 : 계좌를 등록한 회원의 id
  • User는 여러개의 계좌를 생성할 수 있으므로, 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원을 입금해준 테스트 코드이다.

- 결과

 
<center>
<img src="https://cdn.discordapp.com/attachments/1086144700672647192/1117706287183044719/image.png" width="400" height="100">
</center>
<center>select * from history_tb;</center>
&nbsp;


&nbsp;
<center>
<img src="https://cdn.discordapp.com/attachments/1086144700672647192/1117706563453460481/image.png" width="400" height="100">
</center>
<center>select * from account_tb;</center>
&nbsp;


&nbsp;
## Index 설정

History Table을 index 설정을 해주었다. 인덱스 설정하는 법은 알지만 왜 해야하고, 어떤 부분에서 강점을 가지고 있는지를 아직 잘 모르겠다. 공부가 필요한 것 같다. 다음 포스팅으로 리펙토링을 진행해야겠다.

```sql
create index w_number_id on history_tb(w_number_id);
create index d_number_id on history_tb(d_number_id);

 

show index from history_tb;

 

 

ERD