Database Design Question 2

[rb74]

Hi all,

I am designing a database for a graduate program in SQL Server 2000. At the minimum, the faculty of this program can have the status of one of the following titles: Instructor, EMentor, Internship Mentor, Field Mentor, and Supervisor.

Each student is assigned an EMentor, Internship Mentor, Field Mentor and Supervisor, not necessarily all at once.

So far I have designed the faculty table with Instructor, EMentor, Internship Mentor, Field Mentor, & Supervisor fields with a bit datatype (to show whether he/she currently has that status) to avoid assigning a faculty member multiple ID's for multiple titles.

My question comes when assigning a student mentors and a supervisor. Is it normal to have a relationship between two tables where the primary key in one table is making a relationship with another table for different fields?

Ex.

FacultyID PK(Faculty Table) with EMentor FK in Student Table
FacultyID PK(Faculty Table) with InternshipMentor FK in Student Table


Thanks,

Rob

 

[psemianonymous]

Yes, you can do that, it will work fine. Just be sure that when you construct your SQL, in the FROM clause you must include the (Faculty Table) twice--so you'll have to use aliases, i.e.

SELECT Student.*, A.*, B.*
FROM Faculty as A, Faculty as B
WHERE Student.EMentor = A.FacultyID AND
Student.InternshipMentor = B.FacultyID


something like that. That's the only hangup, as far as I know.

 

[psemianonymous]

Blah, that's what you get for not looking over it--the FROM clause is missing the Student table. So be it.

 

[rb74]

Foolio12,

Thank you. I appreciate your insight. I thought this method would work, but I needed someone to verify it for me.


Thanks,

Rob

 

[harebrain]

Actually, this sounds more like a many-to-many relationship:

A student can have zero, one, or many faculty mentors.
A faculty member can be a mentor to zero, one, or many students.

So, the approach you've given is wrong if you want normal. To properly represent this model, you need three* tables: FacultyMember, Student, and FacultyStudentRelationship. The FSR table would contain the FacultyMemberId, the StudentId (both are foreign keys migrated from the base tables) and the RelationshipType (which could contain, e.g., "EMentor".) This way, you wouldn't have to alter the database schema whenever a new mentoring/supervising relationship appears.

*Some people would reasonably argue that this can be done with only two tables because both faculty and students are people: there need only be one People table, perhaps with a Faculty/Student attribute. The FSR table, then, would be a reflexive relationship from the People table to the People table. But I suspect reflexive relationships are beyond the scope of this problem.

 

[psemianonymous]

A student can have zero, one, or many faculty mentors.
A faculty member can be a mentor to zero, one, or many students.

A different way of saying this is:
A faculty member can be a EMentor, or not.
A faculty member can be a Internship Mentor, or not.
A faculty member can be a Field Mentor, or not.
A faculty member can be a Supervisor, or not.

A student can have either zero or one EMentor's.
A student can have either zero or one Internship Mentor's.
A student can have either zero or one Field Mentor's.
A student can have either zero or one Supervisor's.

An EMentor can have many students.
An Internship Mentor can have many students.
A Field Mentor .... many ...
A Supervisor ... many ...


So it works as is--a one:many relationship for each of the mentor types. You can combine the types into a "mentor type" field, and get your many:many relationship. But it's not necessarily wrong to do the table structure as-is, without the many:many relationship.

 

[harebrain]

I beg to differ: you have a repeating group, which is not normal.

And just look at how many verbal hoops you're forced to create, compared to my two, simple statements that model the existing entities/relationships and any future additional Faculty/Student relationships.

Consider this excerpt of your statements:
A student can have either zero or one EMentor's.
A student can have either zero or one Internship Mentor's.

You've totally missed the boat here: these statements only look like entity/relationship modeling statements. Unless you plan on a Students table, an EMentors table, an InternshipMentors table, etc., these statements do not describe the model.

I didn't say it wouldn't work; I maintain that it's not the right way to do this. And as I stated in my previous post, the mistake will be manifested when new relationships are added and the schema has to be altered.

 

[psemianonymous]

All right, I think I see what you're saying, and I concede you're more right than I am, up to this point.

I don't think this situation is as obviously denormalized as a "field1, field2, field3, field4, ... , field50" situation, as there can be subtle differences between the mentor-student relationships and the supervisor-student relationships...

...but it can only benefit to store the data in a separate 'intermediate' table. So I was wr-

wr-

Sorry, I can't say it.


Pete

 

[rb74]

Harebrain,

You raise valid points, and I agree with your points. I initially had the tables setup in this fashion, but when I discovered that a faculty member can hold multiple titles, I got flustered, and did not think this through.

Here is my plan:
A) Build a separate table to house all of the current and future titles a faculty member can attain.
B) Build a Faculty Title table to hold the faculty-title relationships
C) Build a FacultyStudent table to hold the faculty-student relationships

Thanks again,

Rob

 

[harebrain]

That sounds like a plan, Rob!

Happy Thanksgiving to all!

harebrain