CODEWITHSUNDEEP
asp.net-mvc-3database-designef-code-first
Joe
Joe

Reputation: 4183

Multiple relationships on a table

SQL Server 2012 MVC3 EF4.3.1 Code First project.

I have a Teacher and Student table with a one to many relationship. The Teacher’s tables Id will be used as the account number so its Id numbering needs to be separate from the Student’s. I would like to create a Person table (containing shared properties such as First, Last, Phone, Email) to reduce redundancy on the properties. Person will also have a one to many relationship to an Address table.

I’ve thought of trying a Table per Hierarchy model with Teacher and Student inheriting from Person but then the Id sets would not be separate and I would have to have a one to many relationship internally on the Person table. I could generate the ID’s through code but is an internal one to many doable or practical?

Another scenario would be to setup Person as a child table with a one to one between and Teacher and Person and a one to one between Student and Person but I’m not sure how or if it’s possible to have two separate one to one’s on a table.

Is there a practical way to do what I want or should I not worry about the redundancy and not use a Person table? If I went that route would it be possible to have two separate one to many relationships to an Address table (Teacher-Address and Student-Address)? Or for that matter a one to many (Teacher-Address, teacher may have an additional shipping address) and one to one (Student-Address)?

Thank you

Upvotes: 0

Views: 210

Answers (4)

user1494736
user1494736

Reputation: 2400

Being a teacher and being a student are roles of people, not types of people. You should have a table for People, a table TeachCourse to say that a Person is the teacher of a course (which in some cases are multiple teachers), a table AssistCourse to say which persons are attending a class as a student. You might have people that teach a course and assist another course, and that wasn't properly modeled in your first version.

You can also create a ContactInformation or ShippingInformation table for People to specify all their data (Some people may have multiple phones, or emails to).

Upvotes: 0

Rick Eyre
Rick Eyre

Reputation: 2475

I can see two possibilities:

One: Go with your Student and Teacher inheriting from a base table of Person and not worry about the 'redundancy'. It's not a redundancy because your relating a Student and a Teacher not a Person to a Person and so in your database and DOM the Person table and Person class know nothing of the Teacher to Student relationship, it only knows that its a person. The teacher and student relationships are stored in there respective types, not the person type. Also, look at Table per Type instead of Table per Heiarchy. It's much cleaner and crisper looking in the database and you don't get all the information of each type in the heiarchy in one table.

Two: Create a table that specifically holds information that both Students and Teachers share and have that related to both the Student and Teacher table separately. You could call it something like "ContactInformation".

Upvotes: 0

rboarman
rboarman

Reputation: 8214

You could also do a Person table with an IsTeacher flag.

Upvotes: 1

duffymo
duffymo

Reputation: 309028

Another way to do it is to have a one to one between a Person and a Role table. Teacher and Student are merely roles in this arrangement. A given Role can be fulfilled by many Person instances.

Upvotes: 2

Related Questions