[Reference] 2024-03-17 Project ERD 0.0.2 작성

[chanhocode]

TABLE	content
USER	유저 관련
MEDICATION	유저 복용중인 약 관련
DISEASE	유저 지병 관련
RESERVE	예약 관련
HOSPITAL	병원 관련
BUSINESS_HOURS	병원 영업시간 및 휴일 관련
ADMIN	관리자 관련
CLOSED	병원 휴가 관련
NOTICE	공지 관련
ROOM	진료실 관련
CONSULTATION	의사 일주일 진료 시간 및 오프 관련
CLINIC	진료 항목 관련
BREAK	진료실 휴진 관련
DOCTOR	의사 관련
MAJOR	의사 전공 관련

[hyeonpark-5]

MYSQL 테이블 생성

• user

  CREATE TABLE User(
  user_id INT NOT NULL,
  password VARCHAR(255) NOT NULL,
  nickname VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  gender CHAR(1) NOT NULL, 
  birth_date DATE NOT NULL,
  phone VARCHAR(20) NOT NULL
  address VARCHAR(255) NOT NULL
  banned TINYINT(1) NOT NULL,
  signout DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY(user_id),
  FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id)
  );

• medication

  CREATE TABLE mdication(
  medication_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  manufacturer VARCHAR(255) NOT NULL,
  PRIMARY KEY(medication_id),
  FOREIGN KEY(user_id) REFERENCES user(user_id)
  );

• disease

  CREATE TABLE disease(
  disease_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY(disease_id),
  FOREIGN KEY(user_id) REFERENCES user(user_id)
  );

• reserve

  CREATE TABLE reserve(
  reserve_id INT NOT NULL,
  symptom TEXT,
  reservation DATETIME,
  reservation_status TINYINT(1) NOT NULL,
  confirmed DATETIME NOT NULL,
  PRIMARY KEY(reserve_id),
  FOREIGN KEY(user_id) REFERENCES user(user_id),
  FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id)
  );

• hospital

  CREATE TABLE hospital(
  hospital_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  tel VARCHAR(20) NOT NULL,
  owner VARCHAR(255) NOT NULL,
  registration DATE NOT NULL,
  address VARCHAR(255) NOT NULL,
  corporate VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME,
  PRIMARY KEY(hospital_id)
  );

• business hours

  CREATE TABLE business_hours(
  business_hours_id INT NOT NULL,
  week ENUM NOT NULL,
  opening TIME,
  closed  TIME,
  holiday TINYINT(1) NOT NULL,
  PRIMARY KEY(business_hours_id),
  FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id)
  );

• admin

CREATE TABLE admin(
    admin_id INT NOT NULL,
    admin_name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL,
    last_login DATETIME NULL,
    create_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    PRIMARY KEY(admin_id),
    FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id)
);

• closed

  CREATE TABLE closed(
  closed_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  date DATE NOT NULL,
  create_at DATETIME, NOT NULL,
  PRIMARY KEY(closed_id),
  FOREIGN KEY(admin_id) REFERENCES admin(admin_id),
  FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id)
  );

• notice

  CREATE TABLE notice(
  notice_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  create_at DATETIME NOT NULL,
  update_at DATETIME,
  PRIMARY KEY(notice_id),
  FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id)
  FOREIGN KEY(admin_id) REFERENCES admin(admin_id)
  );

• room

  CREATE TABLE room(
  room_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  area VARCHAR(30) NOT NULL,
  floor VARCHAR(10) NOT NULL,
  call_number VARCHAR(20) NOT NULL,
  PRIMARY KEY(room_id),
  FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id)
  FOREIGN KEY(doctor_id) REFERENCES doctor(doctor_id)
  );

• consult

  CREATE TABLE consultation(
  consultation_id INT NOT NULL,
  week ENUM NOT NULL,
  opening TIME,
  closed TIME,
  off TINYINT,
  PRIMARY KEY(consultation_id),
  FOREIGN KEY(room_id) REFERENCES room(room_id)
  );

• clinic

  CREATE TABLE clinic(
  clinic_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  PRIMARY KEY(clinic_id),
  FOREIGN KEY(room_id) REFERENCES room(room_id)
  );

• break

  CREATE TABLE break(
  break_id INT NOT NULL,
  off TINYINT(1) NOT NULL,
  break date NOT NULL,
  opening TIME,
  close TIME,
  created_at DATETIME NOT NULL,
  PRIMARY KEY(break_id),
  FOREIGN KEY(room_id) REFERENCES room(room_id)
  );

• doctor

  CREATE TABLE doctor(
  doctor_id INT NOT NULL,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  position VARCHAR(255) NOT NULL,
  gender CHAR(1) NOT NULL,
  birthdate DATE NOT NULL,
  phone VARCHAR(20) NOT NULL,
  address VARCHAR(20) NOT NULL,
  banned TINYINT(1) NOT NULL,
  signout DATE,
  created_at DATETIME NOT NULL,
  updated_at DATETIME,
  PRIMARY KEY(doctor_id),
  FOREIGN KEY(hospital_id) REFERENCES hospital(hospital_id)
  );

• major

  
  CREATE TABLE major (
  major_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  degree VARCHAR(255) NOT NULL,
  school VARCHAR(255) NOT NULL,
  graduate DATE NOT NULL,
  PRIMARY KEY(major_id),
  FOREIGN KEY(doctor_id) REFERENCES doctor(doctor_id)

);