I have a column in one table that needs to be associated with two or more columns in another table. How do I do this and keep the database normalized. Example (Analogy for best representation):
two tables - one table "Questions" second table "NFL Teams"
Table Question has a column that asks a question such as name four teams that have not been to the superbowl?"
How can I relate the NFL Table (4 teams) to this question. because this question would have 4 foreign keys associated with it (Lions, packers, jets, giants - I made up the question and I know the answer is no way accurate, so lets not get in to football with this question, its just so you can understand what I am trying to accomplish :D)
How do I do this and keep the database relational and normalized without having one table that rules them all or have columns in a table like "team1", "team2", etc... because I know that is wrong as well.
Please help, its been bugging me for days.
Grazie,
The Argyle Ghost
Loading
Argyle GhostPosted Feb 1, 2011, 7:54 PM
Thats what I did. I created a third table that ties the QuestionID to the TeamID as FK's. Silly me, I thought for some reason that I would be breaking some law of Databases by doing that. :D
Carrie MedinePosted Jan 31, 2011, 3:30 PM
You will need a third table that contains your answers, i.e. using your example:
Table - Answers
FK - QuestionID
FK - TeamID
You would have a separate row for each eligible team, so the primary key for the Answers table is both the QuestionID & TeamID.
Cheers :)