DB模式的预约应用程序：是什么医生，约会，时隙，患者之间的正确关系？ [英] DB Schema for Appointment Booking App: What is the correct relationship between Doctors, Appointments, TimeSlots, Patients?

问题描述

[将更新的模式： http://i.imgur.com/fX9CGNh.png ]

我负责开发的是专为小型医疗机构的预约系统。这是Rails 3.2的应用程序。

我有困难的设计数据库架构这对我来说很有意义。

问： 由于以下信息，什么是医生，病人，约会，椅子和time_slots之间的正确关系的

患者需要预约了医生的办公室。根据约会的类型，每个约会定于一个或多个相邻time_slots，以及是否有可能安排在time_slots具有相同START_TIME两项任命和END_TIME被任命类型决定。 （双预订是基于约会的类型允许的。）

应用规格：

1. 在注册用户通过网站预约申请表进行预约。
2. 在约会占用相邻time_slots的某一pre设置总量。这是通过预约类别/类型决定。这个长度可以由管理员，以及每个TIME_SLOT的长度进行调整。
3. 要帮助加快该请求的过程中，不可用/已预订的时间，从预约申请表上的日历隐藏。
4. 在管理面对的界面，管理员可以确认预约请求，并进行预约，而且他们还可以更新，创建和删除定期约会。
5. 所有的约会都保持在一个椅子 - 就像一个牙医的椅子。一个办公室有多个椅子。每个座椅一名患者对于给定的预定时间段。
6. 预约有日期字段，一天，长appointment_type时间，double_bookable？ （由appointment_type测定）。 Time_slots有START_TIME和END_TIME和日期。
7. 在这里只有一个在此办公的医生。但是，某些类型的任用 - 这是要求不高的文档的时间，可重复预订。在本质上，两个齿清洗可以为同一时隙预约的，只要它们在**独立椅子**保持。

我的关系

 办公用品和LT;的ActiveRecord :: Base的
的has_many：医生

医生＆LT;的ActiveRecord :: Base的
的has_many：患者
belongs_to的：办公室

患者LT;的ActiveRecord :: Base的
belongs_to的：医生
的has_many：约会

预约＆LT;的ActiveRecord :: Base的
belongs_to的：病人
的has_many：filled_time_slots

FilledTimeSlot＆LT;的ActiveRecord :: Base的
belongs_to的：预约
belongs_to的：TIME_SLOT

时隙＆LT;的ActiveRecord :: Base的
的has_many：filled_time_slots
belongs_to的：椅子

椅LT;的ActiveRecord :: Base的
的has_many：time_slots
 

解决方案

我想补充一个布尔字段，时隙和写一个自定义的验证，允许在时隙有条件双预订。下面协会。 （在你的时隙迁移布尔字段，你可以摆脱FilledTimeSlots表。）

 办公用品和LT;的ActiveRecord :: Base的
  的has_many：医生

医生＆LT;的ActiveRecord :: Base的
  的has_many：患者，通过：任命
  的has_many：约会
  belongs_to的：办公

患者LT;的ActiveRecord :: Base的
  的has_many：医生，通过：任命
  的has_many：约会

预约＆LT;的ActiveRecord :: Base的
  belongs_to的：病人
  belongs_to的：医生
  belongs_to的：椅子
  belongs_to的：时隙

时隙＆LT;的ActiveRecord :: Base的
  validates_with：可用性，除非：appointments.nil？
  validates_with：工作日
  的has_many：约会
  的has_many：椅子，通过：任命
那么＃你可以在你的验证指定最多2把椅子根据预约类型，像这样：

高清可用性
  如果self.chairs.count == 0
     self.booked？ ==假
  ELSIF self.chairs.count == 2
     self.booked？ ==真
  ELSIF self.chairs.count == 1和self.appointment.type ==清洗
     self.booked？ ==假
  其他
      self.booked？ ==真
  结束
结束


椅LT;的ActiveRecord :: Base的
  的has_many：约会
  belongs_to的：时隙，通过：任命
结束
 

------------马克斯·威廉姆斯交替回答------------------

 医生
   的has_many：约会

 患者
   的has_many：约会

 办公室
   的has_many：椅子

 椅子
   ＃键字段：office_id
   belongs_to的：办公
   的has_many：约会

 时隙
  ＃键字段：starts_at，：ends_at

约定
  ＃键字段：doctor_id，chair_id，time_slot_id，patient_id
  belongs_to的：医生
  belongs_to的：椅子
  belongs_to的：TIME_SLOT
  belongs_to的：病人
 

[ UPDATED SCHEMA: http://i.imgur.com/fX9CGNh.png ]

I'm tasked with developing an appointment booking system that is designed for a small medical office. It is a Rails 3.2 app.

I'm having difficulty designing a database schema that makes sense to me.

Question: Given the following information, what is the correct relationship between doctors, patients, appointments, chairs and time_slots?

Patients need to make appointments with a doctor's office. Depending on the type of appointment, each appointment is scheduled for one or more adjacent time_slots, and whether or not there can be two appointments scheduled for time_slots with the same start_time and end_time is determined by appointment type. (Double-booking is allowed based on the type of appointments.)

App Specs:

1. Registered users make appointments via an appointment request form on the website.
2. Appointments take up a certain pre-set amount of adjacent time_slots. This is determined by appointment category/type. This length can be adjusted by the admin, as well as the length of each time_slot.
3. To help speed up the request process, unavailable/already-booked times are hidden from the calendar on the appointment request form.
4. On the admin-facing interface, administrators can confirm an appointment-request and make an appointment, and they can also update, create and delete scheduled appointments.
5. All appointments are held in a "chair"--like a dentist's chair. An office has multiple chairs. One patient per chair for a given booked time slot.
6. Appointments have fields for date, time of day, length appointment_type, double_bookable? (determined by appointment_type). Time_slots have a start_time and end_time, and a date.
7. There is only one doctor in this office. But, certain types of appointments--that are less demanding of the doc's time-can be double-booked. In essence, two teeth cleanings can be booked for the same time slot, as long as they are held in **separate chairs**.

My Relationships:

Office < ActiveRecord::Base
has_many :doctors

Doctor < ActiveRecord::Base
has_many :patients
belongs_to :offices

Patient < ActiveRecord::Base
belongs_to :doctor
has_many :appointments

Appointment < ActiveRecord::Base
belongs_to :patient
has_many :filled_time_slots

FilledTimeSlot < ActiveRecord::Base
belongs_to :appointment
belongs_to :time_slot

TimeSlot < ActiveRecord::Base
has_many :filled_time_slots
belongs_to :chair

Chair < ActiveRecord::Base
has_many :time_slots

解决方案

I would add a boolean field to TimeSlot and write a custom validation allowing for conditional double-booking on TimeSlot. Associations below. (With the boolean field in your migration for TimeSlots, you could get rid of the FilledTimeSlots table.)

Office < ActiveRecord::Base
  has_many :doctors

Doctor < ActiveRecord::Base
  has_many :patients, through: :appointments
  has_many :appointments
  belongs_to :office

Patient < ActiveRecord::Base
  has_many :doctors, through: :appointments 
  has_many :appointments

Appointment < ActiveRecord::Base
  belongs_to :patient
  belongs_to :doctor
  belongs_to :chair
  belongs_to :timeslot

TimeSlot < ActiveRecord::Base
  validates_with :availability, unless: "appointments.nil?"
  validates_with :workday
  has_many :appointments
  has_many :chairs, through: :appointments 
#you could then specify a maximum of 2 chairs in your validation depending on the appointment type, something like this:

def availability
  if self.chairs.count == 0
     self.booked? == false
  elsif self.chairs.count == 2
     self.booked? == true
  elsif self.chairs.count == 1 and self.appointment.type == "cleaning"
     self.booked? == false
  else
      self.booked? == true
  end
end


Chair < ActiveRecord::Base
  has_many :appointments
  belongs_to :timeslot, through: :appointment
end

------------ Alternate Answer by Max Williams ------------------

 Doctor
   has_many :appointments

 Patient
   has_many :appointments

 Office 
   has_many :chairs  

 Chair
   #key-fields: office_id
   belongs_to :office
   has_many :appointments  

 TimeSlot
  #key-fields: starts_at, :ends_at  

Appointment
  #key-fields: doctor_id, chair_id, time_slot_id, patient_id
  belongs_to :doctor
  belongs_to :chair
  belongs_to :time_slot  
  belongs_to :patient

这篇关于DB模式的预约应用程序：是什么医生，约会，时隙，患者之间的正确关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！