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

Answer 1

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

Answer 2

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.