CODEWITHSUNDEEP
djangodatabaseweb-applicationscalendar
Shimagoro
Shimagoro

Reputation: 11

DB design for appointment scheduler

I want to build a web-based appointment scheduler. Currently I am using Django but I think it's a general question. Say there are teachers and students. And there is a calendar with a bunch of 1 hour time slots. Each teacher can designate which time slots they are available. And students can book teachers time slots.

On the view side, a student can first select a teacher, then a full calendar for that teacher is shown with their availability, just like Calendly.

For teachers and students, I feel I can have one user table and have one column indicating whether a user is a teacher or a student.

The question is how should I design a table for appointments. The idea I currently have is to create a table where each row is a single time slot which has starting time and date, a teacher id, and optionally a student id. If student id is null, that means that time slot of the teacher is available.

Is it a good approach?

Upvotes: 1

Views: 1075

Answers (1)

dani herrera
dani herrera

Reputation: 51655

Yes, it looks like a good design. Don't forget to declare a unique together constraint to avoid dups. Also, on TimeSlot, the student should be nullable to allow students to join a free timeSlot. Here it is:

  • Teacher:
    • id (pk)
    • name, ...
  • Student:
    • id (pk)
    • name, ...
  • TimeSlot:
    • id (pk)
    • teacher_id (not null, fk to teacher)
    • student_id (nullable #!Important , fk to student)
    • date (nut null)
    • starting_hour (nut null)
    • Constraint unique together #!Important: teacher_id, date, starting_hour.

On django you can declare it as many-to-many relationships with extra fields

from django.db import models

class Student(models.Model):
    name = models.CharField(max_length=128)

    def __str__(self):
        return self.name

class Teacher(models.Model):
    name = models.CharField(max_length=128)
    slots = models.ManyToManyField(Student, through='TimeSlot')

    def __str__(self):
        return self.name

class TimeSlot(models.Model):
    student = models.ForeignKey(
        Student,
        blank = True,  #!Important
        null =True,    #!Important
        on_delete=models.SET_NULL)
    teacher = models.ForeignKey(Teacher, on_delete=models.CASCADE)
    date = models.DateField()
    starting_hour = models.IntegerField(max_length=64)

class Meta:
    constraints = [
        models.UniqueConstraint( #!Important
            fields=['teacher', 'date', 'starting_hour'],
            name='unique one teacher by slot')
    ]

Upvotes: 1

Related Questions