123

Tables (10)

available_schedules

  • schedule_id — BIGINT — Primary key — NOT NULL
  • doctor_id — INT — Foreign key → doctors.id — NOT NULL
  • business_id — INT — Foreign key → business.id — NOT NULL
  • department_id — INT — Foreign key → clinic_departments.id — NOT NULL
  • schedule_date — DATE — NOT NULL
  • start_time — TIME — NOT NULL
  • end_time — TIME — NOT NULL
  • slot_duration — INT — NOT NULL
  • max_appointments — INT — NOT NULL
  • is_available — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • INDEX — `idx_doctor_date`
  • INDEX — `idx_business_date`

business

  • id — int — Primary key — NOT NULL
  • name — varchar(100) — NOT NULL
  • category — varchar(50) — NOT NULL
  • phone — varchar(20)
  • roadname — varchar(255) — NOT NULL
  • address — varchar(255) — NOT NULL
  • price — varchar(50)
  • latitude — decimal(10,7) — NOT NULL
  • longitude — decimal(10,7) — NOT NULL
  • open_hours — varchar(255)
  • holidays — varchar(255)
  • is_active — tinyint(1) — NOT NULL

business_rules

  • business_id — int — Foreign key → business.id — NOT NULL
  • rule_id — int — Foreign key → entry_rules.id — NOT NULL

clinic_departments

  • id — int — Primary key — NOT NULL
  • clinic_id — int — Foreign key → business.id — NOT NULL
  • department_name — varchar(50)

doctors

  • id — int — Primary key — NOT NULL
  • business_id — int — Foreign key → business.id — NOT NULL
  • department_id — int — Foreign key → clinic_departments.id — NOT NULL
  • name — varchar(50) — NOT NULL
  • license_number — varchar(50) — NOT NULL
  • phone — varchar(20)
  • created_at — datetime — NOT NULL

entry_rules

  • id — int — Primary key — NOT NULL
  • rule_type — enum('weight_less_than','weight_more_than','species_allowed','species_blocked','animal_allowed','animal_blocked')
  • value — varchar(100) — NOT NULL
  • description — varchar(255)

pet

  • pet_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • name — varchar(30) — NOT NULL
  • species — varchar(20)
  • breed — varchar(50)
  • gender — enum('male','female','unknown') — NOT NULL
  • birth_date — date
  • neutered — tinyint(1) — NOT NULL
  • profile_image_url — varchar(255)
  • health_memo — text
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

reservation_log

  • log_id — BIGINT — Primary key — NOT NULL
  • reservation_id — BIGINT — NOT NULL
  • changed_by_user_id — INT
  • change_type — ENUM('created', 'updated', 'canceled', 'auto-cancel') — NOT NULL
  • old_status — VARCHAR(50)
  • new_status — VARCHAR(50)
  • old_datetime — DATETIME
  • new_datetime — DATETIME
  • memo — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • INDEX — `idx_reservation_id`
  • INDEX — `idx_changed_by_user`

reservations

  • reservation_id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.user_id — NOT NULL
  • pet_id — bigint — Foreign key → pet.pet_id — NOT NULL
  • doctor_id — int — Foreign key → doctors.id — NOT NULL
  • department_id — int — Foreign key → clinic_departments.id — NOT NULL
  • business_id — int — Foreign key → business.id — NOT NULL
  • reservation_time — datetime — NOT NULL
  • status — enum('waiting','confirmed','canceled','completed') — NOT NULL
  • created_at — timestamp
  • updated_at — timestamp

users

  • user_id — bigint — Primary key — NOT NULL
  • password_hash — varchar(255) — NOT NULL
  • name — varchar(20) — NOT NULL
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL
  • phone — varchar(20) — NOT NULL
  • email — varchar(254) — NOT NULL