医生调度数据库设计 [英] Doctor scheduling database design
问题描述
我在用户界面下找到了此功能，用于调度功能。
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 structure with the below columns
- DoctorId
- Date
- AvalableFrom
- AvalilableTo
- 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.
我的数据库架构中是否存在任何问题或更好的表设计可以帮助我做到这一点？
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?
编辑：我正在使用的屏幕是为了帮助医院工作人员知道什么时候有主治医生，因此他们可以为患者预约时间或告知其他可用时间
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
推荐答案
编辑：我误解了这个问题。
I misunderstood the question.
您的设计很好-表格中有多行反映了多个偶数没有错。您可能会考虑的唯一改进是将AvailableFrom和AvailableTo设为日期时间值，而不是时间，因此您可以删除日期列。这可以帮助您应对整个午夜的可用性。
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.
其余答案与问题无关-它是基于对该问题的误解。
The rest of the answer does NOT relate to the question - it's based on a misunderstanding of the issue.
首先，您需要知道医生的工作时间是什么时候；这可能很简单（每天9-5），也可能很复杂（星期一9-5，星期二不可用，星期三-星期五9-12：30不可用）。您可能还需要记录休息时间-例如每天的午餐，因此您不必在午餐时间安排约会；我假设不同的医生会在不同的时间休息。
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.
接下来，您可能不想记录可用性，而是要记录每天的约会。当他们的日程安排表明他们正在工作时，以及没有预定的约会时，都会有医生在场。
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
这篇关于医生调度数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
