System design for Appointment Booking System

Question

I am developing an Patient appointment booking system and following is the schema that I have come out with. Could you verify and let me know if the entities and their relation are correct. I am looking at a basic system where when the user logs in he or she can book an appointment for a doctor and patient. He should be able to see the list of doctors and their available time slot while booking.

**Patient**
Id
FirstName
LastName
DateOfBirth
Gender
Phone
Email
Address
City

**Apppointment**


  Id
    AppointmentTypeId
    Date
    Time
    Notes
    PatientId
    PractionersId

AppointmentType Id Name

Practioner

Id
FirstName
LastName
PractionerTypeId

PractionerType

Id
PractionerType

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

If you're going for star schema you might want to move the notes off of the Appointment table as this looks like your 'fact' table. If you got massive textstrings in it it could cause performance issues for you in future as pretty much any conceivable query would have to go through that table. Of course depends how big its gonna get. Some tips on star schemas here

edited May 23 '17 at 12:33

Community

1
1

answered Dec 21 '16 at 12:32

Jim

569
6
15

Hi JIm, if i move the notes out then where it should reside. Could you site the example of the star schema in this case. I did go through the link you shared but not making much sense to me at this stage. Maybe an example of how it is done will give me a better understanding – Tom Dec 21 '16 at 12:50
Well just like you have appointment type on another table, you might want to have appointment details table with the notes on. http://datawarehouse4u.info/Data-warehouse-schema-architecture-star-schema.html might be a better summary of a star schema – Jim Dec 21 '16 at 13:42

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

You are missing a PractitionerAvailableHours table.

PractitionerAvailableHours table

PractitionerId

AvailableDate

AvailableHours

That info paired with the appointments already made by that Practitioner should give you the available agenda for each one.

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 21 '16 at 12:39

Adrián E

1,683
2
14
24

Should it be AvailableHours or Available time. What datatype should it be. – Tom Dec 21 '16 at 17:26
It depends on your needs. But you could use a Time (or the time part of a datetime) and have as many rows in that table as available slots the PractitionerHas. You should define the slots as you see fit though 15min or 30min slot should suffice. That would be part of your UI. In the DB you would have one row for each slot available, and the Appointments should take as many slots as required. – Adrián E Dec 21 '16 at 17:44

System design for Appointment Booking System

2 Answers2