I would suggest an Appointment
table which stores the current appointments for each doctor. We can add some constraints on this table which limit the appointment start times to even ten-minute times (e.g. 9.00, 9.10, 9.20) plus add some other common sense checks like EndTime
after StartTime
and doctor can't have two appointments starting at the same time. Assume that you'd also like doctors to only work between 9am and 5pm, because everyone needs some work-life balance.
CREATE TABLE Appointment (
DoctorID char(1) NOT NULL,
[Date] date NOT NULL,
StartTime time(0) NOT NULL CONSTRAINT CHK_StartTime_TenMinute CHECK (DATEPART(MINUTE, StartTime)%10 = 0 AND DATEPART(SECOND, StartTime) = 0),
EndTime time(0) NOT NULL CONSTRAINT CHK_EndTime_TenMinute CHECK (DATEPART(MINUTE, EndTime)%10 = 0 AND DATEPART(SECOND, EndTime) = 0),
Status char(1) NOT NULL,
UserID char(1) NOT NULL,
Price int NOT NULL,
CONSTRAINT PK_Appointment PRIMARY KEY CLUSTERED (DoctorID, [Date], StartTime),
CONSTRAINT CHK_StartTime_BusinessHours CHECK (DATEPART(HOUR, StartTime) > = 9 AND DATEPART(HOUR, StartTime) < = 16),
CONSTRAINT CHK_EndTime_BusinessHours CHECK (DATEPART(HOUR, EndTime) > = 9 AND DATEPART(HOUR, DATEADD(SECOND, -1, EndTime)) < = 16),
CONSTRAINT CHK_EndTime_After_StartTime CHECK (EndTime > StartTime));
CREATE INDEX iDoctor_End ON Appointment (DoctorID, [Date], EndTime);
We can insert some data to this table to see what it looks like. Note that the third insert will fail because it is prevented by our constraint. The doctor can't have two appointments starting at the same time.
INSERT INTO Appointment VALUES ('A', '20170420', '09:00:00', '09:10:00', 'P', '1', '0');
INSERT INTO Appointment VALUES ('A', '20170420', '09:20:00', '09:40:00', 'C', '2', '10');
INSERT INTO Appointment VALUES ('A', '20170420', '09:00:00', '09:20:00', 'C', '2', '10');
Let's assume that you have a numbers table. If you don't many other people have described how to create one. If all else fails, this could create one for you but it's probably not the best way.
CREATE TABLE Numbers (Number int PRIMARY KEY CLUSTERED);
DECLARE @number int = 0;
WHILE @number < 1000
BEGIN
INSERT INTO Numbers VALUES (@number);
SET @number += 1;
END
Now if we want to see free slots for a particular doctor, all we need to do is specify which doctor, and how long the slot is that we're looking for:
DECLARE @doctorID char(1) = 'A';
DECLARE @length tinyint = 20;
WITH Slots AS (
SELECT StartTime = DATEADD(MINUTE, ((DATEPART(MINUTE, GETDATE())/10)+1+Number)*10, DATEADD(HOUR, DATEPART(HOUR, GETDATE()), CONVERT(smalldatetime, CONVERT(date, GETDATE())))),
EndTime = DATEADD(MINUTE, @length, DATEADD(MINUTE, ((DATEPART(MINUTE, GETDATE())/10)+1+Number)*10, DATEADD(HOUR, DATEPART(HOUR, GETDATE()), CONVERT(smalldatetime, CONVERT(date, GETDATE())))))
FROM Numbers)
SELECT TOP 15 DoctorID = @doctorID,
s.StartTime,
s.EndTime
FROM Slots AS s
WHERE NOT EXISTS (SELECT 1
FROM Appointment AS a
WHERE (CONVERT(time(0), s.StartTime) < a.EndTime AND CONVERT(time(0), s.EndTime) > a.StartTime)
AND a.DoctorID = @doctorID
AND a.[Date] = CONVERT(date, s.StartTime))
AND DATEPART(HOUR, s.StartTime) >= 9
AND DATEPART(HOUR, DATEADD(MINUTE, -1, s.EndTime)) <= 16
ORDER BY s.StartTime;
That looks a little awkward, so if anyone can improve that date logic happy to take suggestions.
If a doctor wants a break, then enter the break as an appointment and it won't be available for booking.
Note that the table constraints don't enforce non-overlapping appointments. This is possible but it's more complicated. If this were my system I'd think about some system (e.g. trigger) to finally verify that the appointment doesn't overlap with an existing one at the time of insert, but that's up to you.
Consideration about multiple relationships between entities
In real life, Patients and Physicians interact in several ways. The most obvious one is that you go to see your doctor when you're ill (that's one kind of relationship between Patient and Physician). But you, as a Patient, may refer one Physician to a friend of yours (who is also ill, and, as such a Patient), because you liked how it works. This is a ternary relationship involving two Patients (the referer, and the refered) and one Physician.
Non-binary relationships
Not all relationships are between two tables (such as in the previous case). Another instance: a Diagnosis is performed by a Physician to a Patient (and, you could add: at some time, in some place, after x, y and z tests, etc. as attributes to this relationship). Having one ternary relationship is not the same as having two relationships Physician makes Diagnostic
and Patient diagnosed Diagnostic
. It you ever only visited one doctor, no doubt about who diagnosed. As soon as you visit two doctors... who diagnosed what is much less clear.
This is not answering all your questions, but hope it helps.
Best Answer
As User, Doctor and Patient have very different attributes best you can do is split into 3 tables. As User is the first object you are accessing (registration-login), best Users will function as a father to Doctors and Patients tables.
For example - Users - UserName, FirstName, LastName, EMail, PhoneNumber, Company/Address/Active (Many keep encrypted Password in the DB as well).
You can add Role - 1 for Doctor, 2 for Patient (3 for Nurse, 4 for Clinic Manager and so on).
Doctor and Patient can have the same Ids as User or different so you need to add DoctorId/PatientId in Users or add UserId in Doctors/Patients. Again, as Users come first, it's best to go for an option where the Doctor Id can be found from the User.
For easier setup, you can create different Ids for Doctors and Patients, as Doctors have little and Patients many, you can just keep 1000 values to Doctors and 1001+ for Patients, or you can use different sequences / smart increment value, so Doctors can be 1,3,5 and Patients 2,4,6.
Now Doctors can have additional tables such as Skill Sets where Patient have Patient Records.
Note you can download many medical key-value static tables online, as updates are keep coming in the industry, best is to integrate your system with reliable services.
From experience, I believe managing the patient record and the doctor mail box will be your hardest task, as Doctors need to query the full patient history and every day, they also need the latest updates on their patients.