Bank App 테이블 설계하기

테이블 생성

USER TABLE

CREATE TABLE USER (
user_id int primary key,
user_name varchar(20) not null
);

ACCOUNT TABLE

CREATE TABLE ACCOUNT (
user_id int,
account_number int primary key,
balance int not null,
user_name varchar(20),
foreign key (user_id) references USER(user_id),
foreign key (user_name) references USER(user_name)
);

HISTORY TABLE

CREATE TABLE HISTORY (
account_number int primary key,
w_balance int not null,
w_account_number varchar(20) not null,
d_balance int not null,
d_account_number varchar(20) not null,
created_at DATETIME not null
foreign key (w_account_number) references ACCOUNT(account_number),
foreign key (d_account_number) references ACCOUNT(account_number)
)

데이터 입력

INSERT INTO USER(user_id, user_name) 
values ('a1234', '홍길동');

INSERT INTO USER(user_id, user_name) 
values ('b1234', '임꺽정');

INSERT INTO ACCOUNT(user_id, account_number, balance, user_name)
values (1, '123-456789-10', 1000, '홍길동');

INSERT INTO ACCOUNT(user_id, account_number, balance, user_name)
values (2, '987-654321-01', 1000, '임꺽정');