CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Raul
Raul

Reputation: 154

How to structure the database to avoid duplicates in a table

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:

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('[email protected]', '[email protected]', 'Database I', 'Monday', 16) // FIRST INSERT
BOOKING('[email protected]', '[email protected]', 'Algorithms', 'Monday', 16) // DIFFERENT TEACHER AND COURSE
BOOKING('[email protected]', '[email protected]', '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.

Upvotes: 1

Views: 140

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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

Upvotes: 1

Gabriel Xunqueira
Gabriel Xunqueira

Reputation: 433

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))

Upvotes: 2

Related Questions