|
Okay, since I have found new lost faith in college and it's ability to teach I'm gonna go straight to the hunt.
The productId column in your jobOrder table can only store one entry. It can only be a foreign key to link to one other row. Not multiple. It's still really just an int on your side, just happens to (unless you are in mysql) have some constraints wrapped around it so that you can't set that int to anything you want, just stuff that actually exists in the other table.
So what you need when there are multiple-multiple links on both ends of the linkage is a glue table. A glue table is a table that sits inbetween two tables and provides links between them because a direct linkage isn't possible. Say for example if you were building a database of livejournal entries and a table that had possible emotions for those livejournal entries. Now, people can feel multiple emotions at one time (well, people with feelings can...) you could be happy and excited, angry and sad, angsty (I made this one up, can you tell?) and everything.
So what your schema would look like is this:
journalEntry {
journalId - PK
personId - FK to person table (not shown)
journalDate - Date
journalEntry - Text
}
emotion {
emotionId - PK
emotionName - Text
}
journalEmotionGlue {
journalEmotionGlueId - PK (I have a love/hate relationship with these PK's)
journalId - FK to journal table
emotionId - FK to emotion table
}
So that glue table in the middle is the one that allows you to link up multiple emotions to multiple journal entries. Without it, you couldn't have multiple emotions linked to your livejournal posting, so it would just be angst, angst, angst post after post, because a post could only do a link via a FK to a single emotion.
|