CODEWITHSUNDEEP
database-designdatabase-schema
JeremyMcGee
JeremyMcGee

Reputation: 3

Multi Day Appointment Database Design

I am working on a designing a database for a multi day appointment program. This is what I am doing. I have 40 "spots" that clients will be able to book for x amount of days. I am having troubles deciding the best way to store this information. I am considering that if a row has not been created for a "spot" on a particular day, then I would know this "spot" is free. Versus creating a row for each "spot" for everyday for maybe a year then having a boolean column if the "spot" is free. My next problem is how I am going to make a particular appointment store multiple days. I am considering have a column that would store the primary key for the day before it. If this column was empty I would know that either this appointment was for the first day of many or just a a one day appointment. This is hard to translate into words I can rephrase if needed.

Upvotes: 0

Views: 1701

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Since a spot must always be booked for the whole day, I'd go with the first approach, similarly to this:

enter image description here

The BOOKING.DAY is a date without time.

To check if given spot is booked for the given day, simply search for the existence of the corresponding row in BOOKING. To make an appointment for multiple days, insert one row in APPOINTMENT and several corresponding rows in BOOKING.

NOTE: This model won't enforce the consecutiveness of the days of the same appointment. If this is important, you'll have to enforce it through non-declarative means (e.g. triggers or at the application level).

NOTE: If you don't have any information associated with the appointment other than the client, you can remove the APPOINTMENT table altogether and connect the CLIENT directly to BOOKING.

Alternative design, where BOOKING contains a period (and not just one day) can also be done, but that complicates avoiding overlapping periods (it would probably require a combination of triggers and careful locking).

Upvotes: 2

Related Questions