1

I have a kinda unique situation here, I have 3 models: Degree, Student and Consultant.

There is a many to many rel between a student and a consultant.
Also there is a many to many rel between degree and student.

I could have 2 pivot table for each one of those above relations,
BUT what important here is, is that: a student in a specific degree can have 1 consultant.
In other words creating 2 separate pivot table, doesn't give me the information which consultant is for what degree for a specific user.

I can think of 2 solutions here:

The first one is creating a pivot table with 3 foreign keys like: 

student_id  |  degree_id  |  consultant_id

this way I could know who is the student's consultant for a specific degree.

But is this way, a standard way of architecturing database?
Also, Should I create a Pivot model for this?
What would be the code snippet look like?

The other approach I can think of is that create 2 separate pivot tables, and reference id of one pivot table in the other one.
So this would look like something like:
degree_student: student_id | degree_id
consultant_student: student_id | consultant_id | **degree_student_id**

Again, is this a standard approach?
Should I create a Pivot model for this?

For final discussion, I would embrace any other solution suggestions that are better(/standard).

Note: I have checked here and here but couldn't satisfy my requirements.

Mehrdad Shokri
  • 1,974
  • 2
  • 29
  • 45

1 Answers1

0

a student in a specific degree can have 1 consultant

Your first design doesn't enforce that. The student_id could differ from the degree_student's student_id. There could be multiple consultant_ids for a degree_student_id. Also, consultant_id would have to be nullable.

Similar to your 1st design:

--     student student_id is in degree degree_id witout a consultant  
-- AND consultant_id IS NULL  
-- OR  student student_id is in degree degree_id with consultant consultant_id  
consultant_degree_student: degree_id  |  student_id  |  consultant_id  
(composite) PK (primary key) (degree_id, student_id)  
nullable consultant_id

Similar to your 2nd design (that has an implicit degree_student_id field):

-- degree_student_id identifies student student_id being in degree  degree_id  
degree_student: degree_student_id  |  degree_id  |  student_id  

--     degree_student_id identifies student student_id being in degree degree_id  
-- AND student student_id is in degree degree_id with consultant consultant_id  
degree_student_consultant: degree_student_id  |  consultant_id

Similar to those with no extra id & no nullable fields:

-- student student_id is in degree degree_id  
degree_student: degree_id  |  student_id  

-- student student_id is in degree degree_id with consultant consultant_id
consultant_degree_student: degree_id  |  student_id  |  consultant_id  
PK (primary key) (degree_id, student_id)

The composite PKs imply there can only be one record with a given (degree_id, student_id) pair, so consultant_id is single-valued per pair. Normalization happens to tell us that these tables don't have problems that it can solve.

You have to logically trade off number of tables, number of ids, nulls & simplcity/complexity of table meanings (predicates), along with other things physical performance. A typical SQL design would use the 1st. You need to find & follow a published reference for information modeling & relational database design. (PS Not at all a "unique situation".)

philipxy
  • 14,867
  • 6
  • 39
  • 83