How to structure the database to avoid duplicates in a table

Question

I am doing this exercise where I have to build an sql database (MySql) to store information on private lessons offered by teachers. These are the rules:

• There is just one predifined week with slot times from Monday to Friday (15-19)
• Each teacher can teach 1 or more courses
• Each course can be teached by multiple teachers
• One user can book just one lesson in a selected hour
• One teacher can book just one lesson in a selected hour

This is how I implemented it:

USER(Name, Surname, Email (PK), Password)
TEACHER(Name, Surname, Email (PK), Password) 
COURSE(Title (PK))
SLOTTIME(Day (PK), Hour (PK))

TEACHES(EmailTeacher, TitleCourse) all attributes are PK, FK(EmailTeacher -> Teacher, TitleCourse -> Course)
BOOKING(EmailUser, EmailTeacher, TitleCourse, Day, Hour) all attributes are PK, FK((EmailUser -> User), 
(EmailTeacher, TitleCourse -> Teaches), (Day, Hour -> SlotTime))

This solution causes me two problems, or at least they are the ones I identified:

1. I can have the same User booked with different Teacher and different Course at the same day and hour
2. I can have the same User booked with the same Teacher at the same day and hour but with different Course

Here is an example:

BOOKING('raul@gmail.com', 'michael@gmail.com', 'Database I', 'Monday', 16) // FIRST INSERT
BOOKING('raul@gmail.com', 'anthony@gmail.com', 'Algorithms', 'Monday', 16) // DIFFERENT TEACHER AND COURSE
BOOKING('raul@gmail.com', 'michael@gmail.com', 'Database II', 'Monday', 16) // SAME TEACHER AND DIFFERENT COURSE

What I want to obtain is a table where the rules indicated above are respected, but I can't figure out how to implement it.

Answer 1

I would say you need a table for teacher bookings with a unique constraint on teacher and day hour. The constraint will prevent teachers from booking more than one hour at a time. Whether you want it to be the PK or not depends on your preferences, I personally dislike compound constraints (a PK is semantically also a unique constraint, but a unique constraint doesn't always need to be the PK).

BOOKING(EmailTeacher (PK), Day(PK), Hour(PK), TitleCourse) or 
BOOKING(ID (PK),  EmailTeacher, Day, Hour, TitleCourse)

With this setup I would rename your booking table to SUBSCRIPTION, to have two different names for two different entities (and make conversations about them easier). Boooking should be linked from Subscription and either from teacher or teaches (that raises a philosophical question ;) ).

On the SUBSCRIPTION table you need a unique constraint on (user,day, hour) to prevent users from booking more than one slot of the same interval.

SUBSCRIPTION(EmailUser(PK), EmailTeacher(PK), Day(PK), Hour(PK)) or
SUBSCRIPTION(EmailUser(PK), BookingId(PK))

Answer 2

Sort-out the logical design first, then go into details.

-- Teacher TCH exists.
--
teacher {TCH}
     PK {TCH}

-- Course CRS exists.
--
course {CRS}
    PK {CRS}

-- Time slot TIM exists.
--
slot {TIM}
  PK {TIM}

-- Teacher TCH teaches course CRS.
--
teacher_course {TCH, CRS}
            PK {TCH, CRS}

FK1 {TCH} REFERENCES teacher {TCH}
FK2 {CRS} REFERENCES course  {CRS}

-- Teacher TCH booked time slot TIM for course CRS.
--
teacher_slot_course {TCH, TIM, CRS}
                 PK {TCH, TIM}
                 SK {TCH, TIM, CRS}

FK1 {TCH, CRS} REFERENCES teacher_course {TCH, CRS}

FK2 {TIM} REFERENCES slot {TIM}

-- Student (user) USR exists.
--
user {USR}
  PK {USR}

-- Student USR signed-up for course CRS.
--
user_course {USR, CRS}
         PK {USR, CRS}

FK1 {USR} REFERENCES user    {USR}
FK2 {CRS} REFERENCES course  {CRS}

-- Student USR booked time slot TIM
-- for course CRS with teacher TCH.
--
user_slot_course_teacher {USR, TIM, CRS, TCH}
                      PK {USR, TIM}

        FK1 {USR, CRS} REFERENCES
user_course {USR, CRS}

                FK2 {TCH, TIM, CRS} REFERENCES
teacher_slot_course {TCH, TIM, CRS}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key