4
\$\begingroup\$

I am building a doctor appointment app with Django and Django Rest Framework. The app will have the functionality for secretaries to add appointments to a Doctor's calendar, and each Doctor may have more than one clinic (and multiple secretaries).

I built this Calendar Model to store the availability information of a doctor, which needs to basically inform:

  • on a given day, at what time the doctor starts and finishes work
  • on a given day, at what time the doctor starts and finishes their break time (if they do).

Is this the optimal way to do it?

class Calendar(models.Model):
    doctor = models.ForeignKey(Doctor, on_delete=models.CASCADE, related_name="appointments")
    clinic = models.ForeignKey(Clinic, on_delete=models.CASCADE, related_name="appointments")
    monday_start =  models.TimeField()
    monday_end =  models.TimeField()
    monday_break_start =  models.TimeField()
    monday_break_end =  models.TimeField()
    # continues for every single day of the week
\$\endgroup\$
4
  • \$\begingroup\$ What happens when same doctor is attending multiple clinics? \$\endgroup\$
    – hjpotter92
    Oct 26, 2020 at 12:48
  • \$\begingroup\$ A doctor will have a unified calendar for all of their clinics... I guess I'm planning on blocking different times in the front end \$\endgroup\$
    – uber
    Oct 26, 2020 at 13:04
  • \$\begingroup\$ no, what i meant was: doctor D has clinics C1 and C2. Do you have 2 rows in Calendar with all those columns for D-C1 and D-C2 combinations? \$\endgroup\$
    – hjpotter92
    Oct 26, 2020 at 13:19
  • \$\begingroup\$ there'll be a Calendar for each doctor-clinic \$\endgroup\$
    – uber
    Oct 26, 2020 at 13:39

1 Answer 1

3
\$\begingroup\$

This is relatively simple solution you have here and it could in theory work, but in my experience in a real word you need something more flexible. Also you dont think about redundancy of the data.

What if a doctor for example works only one day in a week. Most of the columns would then be empty. Or if a doctor can work only in the morning? Then there would be no break_start or end in any of the calendar fields. What if, for example, the doctor works every second week? Then you will not have an easy way how to store this information. Also there would be a lot of redundant information. If you will have each doctor working from 8:00 AM to 4:00 PM, you will have this data stored there redundantly. Much less space is needed, if you can store such information as a relation to some kind of duration record.

If I were you, I would create more generic design where you can store up to 365 records (for each day in the year), for each calendar. Then each day would have a relation to a list of intervals, that could later be of required activity. That way you would end up with more flexible design.

The draft could look something like this:

class Calendar(models.Model):
    doctor = models.ForeignKey(Doctor, on_delete=models.CASCADE)
    clinic = models.ForeignKey(Clinic, on_delete=models.CASCADE)

class CalendarDay(models.Model):
    date = model.DateField()
    calendar = models.ForeignKey(Calendar, on_delete=models.CASCADE)

class TimeInterval(models.Model):
    activity = models.Integer(choices = [(1, 'Working'), (2,'Lunch Break'), (3, 'Afternoon Break') ...])
    start = models.TimeField()
    end = models.TimeField()
    calendar_day = models.ForeignKey(CalendarDay, on_delete=models.CASCADE)

In order to ensure data integrity, then you could add an unique constraint on the combination of date and calendar columns in the CalendarDay table. That way each calendar can have only one record for particular date in the CalendarDay table.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.