Need help with many-to-many relationships....
- by yuudachi
I have a student and faculty table. The primary key for student is studendID (SID) and faculty's primary key is facultyID, naturally. Student has an advisor column and a requested advisor column, which are foreign key to faculty. That's simple enough, right?
However, now I have to throw in dates. I want to be able to view who their advisor was for a certain quarter (such as 2009 Winter) and who they had requested.
The result will be a table like this:
Year | Term | SID | Current | Requested
------------------------------------------------
2009 | Winter | 860123456 | 1 | NULL
2009 | Winter | 860445566 | 3 | NULL
2009 | Winter | 860369147 | 5 | 1
And then if I feel like it, I could also go ahead and view a different year and a different term.
I am not sure how these new table(s) will look like. Will there be a year table with three columns that are Fall, Spring and Winter? And what will the Fall, Spring, Winter table have?
I am new to the art of tables, so this is baffling me...
Also, I feel I should clarify how the site works so far now.
Admin can approve student requests, and what happens is that the student's current advisor gets overwritten with their request. However, I think I should not do that anymore, right?