Need help with many-to-many relationships....

Question

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?

Answer 1

You have a many-to-many relationship, and need a third table. Instead of the Current and Requested columns existing in the Students table, they should exist in the new table. That table could be something like this:

StudentAdvisors
StudentAdvisorID, SID, Current, Requested, Year, Term

Answer 2

The typical approach I've used & been taught for many-many relationships is what amounts to a translation table between the two tables.