CODEWITHSUNDEEP
databasedatabase-design
user70014
user70014

Reputation: 59

Model a Course table with one Instructor and multiple Tutors

That is basically the question. What strategy would you follow to model a table Course given that it can only have one Instructor and multiple Tutors?

The key aspect here is that Instructor and Tutor have almost the same properties, except for one or two.

Upvotes: 1

Views: 62

Answers (1)

Tanmay Delhikar
Tanmay Delhikar

Reputation: 1387

You can design like this:

  1. Course table has fields like course_id, name, duration etc. with course_id as the primary key.
  2. Instructor table has fields like instructor_id, name, contact, course_id with course_id is the foreign key.
  3. Tutor table has fields like tutor_id, name, contact etc.
  4. Tutor_Extension table has fields like sno, tutor_id, course_id with tutor_id and course_id as foreign keys.

This way your table is normalized and easy to query.

Note: Tutor_Extension columns can be duplicated itself in Tutor table, but since it also replicates other string data as well in Tutor table, it is better to pull out only id's of Tutor table into Tutor_Extension table.

enter image description here

Upvotes: 1

Related Questions