토이프로젝트 - 데이터베이스

// 병원
create table hospital(
     id int not null auto_increment primary key,
     name varchar(4000) not null,
     address varchar(4000) not null,
     hp varchar(4000) not null,
     createdAt timestamp not null default current_timestamp
);

// 유저
create table users(
    id int not null auto_increment primary key,
    name varchar(2000) not null,
    pw varchar(2000) not null,
    gender enum ('남', '여'),
    age int not null,
    address varchar(2000) not null,
    address_detail varchar(2000) not null,
    hp varchar(2000) not null,
    first enum ('Y', 'N'),
    second enum ('Y', 'N'),
    role enum ('사용자', '관리자') default '사용자',
    createdAt timestamp not null default current_timestamp
);

// 예약
create table reservation(
    id int not null auto_increment primary key,
    users_id int not null,
    hospital_id int not null,
    available_date_id int not null,
    available_time_id int not null,
    constraint RESERVATION_FK foreign key(users_id) references users(id),
    constraint RESERVATION_FK2 foreign key(hospital_id) references hospital(id),
    constraint RESERVATION_FK3 foreign key(available_date_id) references available_date(id),
    constraint RESERVATION_FK4 foreign key(available_time_id) references available_time(id)
);

// 백신
create table vaccine(
    id int not null auto_increment primary key,
    name varchar(4000) not null,
	hospital_id int not null,
    quantity int not null,
    constraint vaccine_FK foreign key(hospital_id) references hospital(id)
);


// 가능한 날짜
create table available_date(
    id int not null auto_increment primary key,
    hospital_id int not null,
    date varchar(4000) not null,
    quantity int not null,
    constraint AVAILABLE_DATE_FK foreign key(hospital_id) references hospital(id)
);

// 가능한 시간
create table available_time(
    id int not null auto_increment primary key,
    available_date_id int not null,
    time varchar(4000) not null,
    quantity int not null,
    constraint AVAILABLE_TIME_FK foreign key(available_date_id) references available_date(id)
);