Reputation: 59
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
Reputation: 1387
You can design like this:
Course
table has fields like course_id, name, duration etc. with course_id
as the primary key.Instructor
table has fields like instructor_id, name, contact, course_id with course_id
is the foreign key.Tutor
table has fields like tutor_id, name, contact etc.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.
Upvotes: 1