1

This is my current database design, for the moment just look the student, teacher and classes tables.

enter image description here

I'm trying to convert some tables into news using TPH Inheritance. For example.

Create a new table called Person where contains:

  • Id,
  • FullName,
  • EnrollmentDate (for student),
  • Email (for student),
  • HireDate (for teacher)

But is there remaining one field called ClassId, where it's contained in Student table. At this momment I don't know if should I create a join table to store the ClassId and PersonId, or just put the ClassId into the Person table. That's my big doubt.

Where do I have to put the ClassId field?

I have to mention that each student can only be in one class, not many. Teachers can have many classes as they want.

Darf Zon
  • 6,268
  • 20
  • 90
  • 149

1 Answers1

0

I think you didn't model the domain correctly:

First you must have students regardless of that they have class or not,
In current model, it seems that Student is a weak entity and depends on classes.

Courses are also independent of semester classes which might happen this year and don't happen next year.

But if you want to have TPH strategy to model the database this link might be of help to you.

You will have a Person table, then Teacher table which has a reference to Person, and Student which also has a reference to Person table.
And at last, the student table has a reference to semester courses through a joint table.

Jahan Zinedine
  • 14,616
  • 5
  • 46
  • 70
  • So, (Students & Teachers is one table called Person) and Classes (or courses) should be independent tables. Dependent tables would be CourseIntructor (for teachers) and Enrollment for students as Join tables. What do you think? – Darf Zon Jan 12 '13 at 20:10
  • Take a look at this link: file:///Users/jani/Downloads/ER%20Model%20for%20the%20University%20Database.doc, If you do a search on google you'll find more samples. – Jahan Zinedine Jan 12 '13 at 20:15