oracle 医生排班数据库设计
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16669197/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Doctor scheduling database design
提问by Billa
I got this below user interface for scheduling functionality.
我在用户界面下面得到了这个用于调度功能。
I need to setup the doctors available time for some days for each week of the month.
我需要为每个月的每个星期设置几天的医生可用时间。
How should i create the table for handling this.
我应该如何创建表格来处理这个问题。
I thought i can create a table structurewith the below columns
我想我可以table structure用下面的列创建一个
- DoctorId
- Date
- AvalableFrom
- AvalilableTo
- 医生编号
- 日期
- 可用自
- 可用于
But here the problem is I will be adding 2 rows for the same doctor if he visit two different timings in same day.
但这里的问题是，如果同一位医生在同一天访问两个不同的时间，我将为他添加 2 行。
Is there any issue in my database schema or any better table design will help me to do this simple?
我的数据库模式是否有任何问题，或者任何更好的表设计可以帮助我做到这一点？
With this table i should be able to detect a doctor is available or not for the particular day and time
使用此表，我应该能够检测特定日期和时间的医生是否可用
How best i can improve my table design?
如何最好地改进我的桌子设计？
Edit:The screen which i am working is, to help the Hospital Staff to know when the visiting doctor is available, so they can book the appointment for the patient for a time or inform other available time
编辑：我正在工作的屏幕是为了帮助Hospital Staff to know when the visiting doctor is available他们，因此他们可以为患者预约一段时间或通知其他可用时间
回答by Neville Kuyt
EDIT: I misunderstood the question.
编辑：我误解了这个问题。
Your design is fine - there's nothing wrong with having multiple rows in a table reflecting multiple evens. The only refinement you might consider is have AvailableFrom and AvailableTo to be datetime values, rather than time, so you can remove the "date" column. This helps you deal with availability spanning midnight.
您的设计很好 - 表中的多行反映多个事件并没有错。您可能考虑的唯一改进是将 AvailableFrom 和 AvailableTo 设为日期时间值，而不是时间，因此您可以删除“日期”列。这有助于您处理跨越午夜的可用性。
The rest of the answer does NOT relate to the question - it's based on a misunderstanding of the issue.
答案的其余部分与问题无关 - 它基于对问题的误解。
Firstly, you need to know when a doctor's working hours are; this might be simple (9 - 5 every day), or complex (9-5 Mondays, not available Tuesdays, 9-12:30 Wednesday - Friday). You may also need to record break times - lunch, for instance - on each day, so you don't schedule an appointment over lunch; I'm assuming different doctors will take their breaks at different times.
首先，你需要知道医生的工作时间是什么时候；这可能很简单（每天 9 - 5），也可能很复杂（周一 9-5，周二不可用，周三 - 周五 9-12:30）。您可能还需要记录休息时间 - 例如午餐 - 每天，这样您就不会在午餐时间安排约会；我假设不同的医生会在不同的时间休息。
Next, instead of recording "availability", you probably want to record "appointments" for each day. A doctor is available when their schedule says they are working, and when they don't have a scheduled appointment.
接下来，您可能想要记录每一天的“约会”，而不是记录“可用性”。当他们的日程表显示他们正在工作时，或者当他们没有预约时，医生就会有空。
So, your schema might be:
因此，您的架构可能是：
Doctors
--------
DoctorID
....
DoctorSchedule
------------
DoctorID
DayOfWeek
StartTime
BreakStartTime
BreakEndTime
EndTime
DoctorAppointment
----------------
DoctorID
Date
AppointmentStartTime
AppointmentEndTime
回答by David Aldridge
I would go with dividing the day into segments of 15 or 30 minutes, and creating a record for every doctor for each slot that they are available. It takes care of uniqueness very well.
我会将一天分成 15 或 30 分钟的部分，并为每个医生的每个可用时段创建一个记录。它很好地照顾了独特性。
An appointment can be a single record and the time slots that it covers can reference the appointment record.
约会可以是单个记录，它涵盖的时间段可以引用约会记录。
Because it records non-appointment availability time in just the same way as appointment time, queries against this method are generally very simple -- for example, to query for available time slots of a given length, or to calculate how much of a doctor's time was not used for appointments, or the average length of appointments, etc..
由于它以与预约时间相同的方式记录非预约可用时间，因此针对此方法的查询通常非常简单——例如，查询给定长度的可用时间段，或计算医生的时间未用于约会，或约会的平均长度等。
The table would be something like:
该表将是这样的：
create table staff_time(
staff_id integer,
time_slot date,
allocation_id)
allocation_id references an appointment or training time or other allocation, or nothing if the slot is free.
allocation_id 引用约会或培训时间或其他分配，如果空档空闲则不引用。
The date data type includes a time component on Oracle.
日期数据类型包括 Oracle 上的时间组件。
回答by NAVEED SHAHZAD
- doctors: (This table will have details about the doctor) patients: (This table will have details about the patient)
- specialization: (It defines the specialization of a doctor e.g. Dentist, Dermatologist etc.)
- appointment_schedule: (Doctor will create an appointment schedule)
- booking_status (Has different status for booking an appointment)
- bookings (when a user books an appointment for a particular schedule and for a particular date and time)
- 医生：（此表将包含有关医生的详细信息）患者：（此表将包含有关患者的详细信息）
- 专业化：（它定义了医生的专业化，例如牙医、皮肤科医生等）
- 预约时间表：（医生会制定预约时间表）
- booking_status（有不同的预约状态）
- 预订（当用户为特定时间表和特定日期和时间预订约会时）
-- Table structure for table 'appointment_schedule'
CREATE TABLE IF NOT EXISTS 'appointment_schedule' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'doctors_id' int(11) NOT NULL,
'working_days' varchar(50) NOT NULL,
'morning_time_start' time DEFAULT NULL,
'morning_time_end' time DEFAULT NULL,
'morning_tokens' int(11) NOT NULL,
'afternoon_time_start' time DEFAULT NULL,
'afternoon_time_end' time DEFAULT NULL,
'afternoon_tokens' int(11) NOT NULL,
'evening_time_start' time DEFAULT NULL,
'evening_time_end' time DEFAULT NULL,
'evening_tokens' int(11) NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
-- --------------------------------------------------------
-- Table structure for table 'bookings'
CREATE TABLE IF NOT EXISTS 'bookings' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'doctors_id' int(11) NOT NULL,
'appointment_schedule_id' int(11) NOT NULL,
'patients_id' int(11) NOT NULL,
'diseases_description' text NOT NULL,
'datetime_start' datetime NOT NULL,
'datetime_end' datetime NOT NULL,
'status_id' int(11) NOT NULL DEFAULT '1',
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
-- Table structure for table 'booking_status'
CREATE TABLE IF NOT EXISTS 'booking_status' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(25) NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
-- Dumping data for table 'booking_status'
INSERT INTO 'booking_status' ('id', 'name') VALUES
(1, 'Pending for Approval'),
(2, 'Approved & Booked'),
(3, 'Cancelled by User'),
(4, 'Visited'),
(5, 'User failed to Visit');
-- --------------------------------------------------------
-- Table structure for table 'doctors'
CREATE TABLE IF NOT EXISTS 'doctors' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(50) NOT NULL,
'specialization_id' int(11) NOT NULL,
'clinic_name' varchar(50) NOT NULL,
'address' varchar(1000) NOT NULL,
'qualification' varchar(50) NOT NULL,
'rating' int(11) NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
-- Table structure for table 'patients'
CREATE TABLE IF NOT EXISTS 'patients' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'first_name' varchar(50) NOT NULL,
'last_name' varchar(50) NOT NULL,
'address' varchar(500) NOT NULL,
'contact' varchar(100) NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
-- Table structure for table 'specialization'
CREATE TABLE IF NOT EXISTS 'specialization' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(100) NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
