How do I correctly design a many-to-many relationship in SQL Server?

Question

How do I correctly design a many-to-many relationship in SQL Server?

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 1

PaulB-TheOneAndOnly (4/16/2010)
Ninja's_RGR'us (4/15/2010)
Here's 1 exemple. I needed to keep the date updated / added and by whom on that table. On top of that I needed a deleted flag and a version id.
That's a fair example of how "purism" bumps head-on with "real world".

It seems to me that a table with two foreign keys is not especially different to any other table. I don't see why anyone ("purist" or otherwise) would say that it shouldn't have other columns too. But maybe I'm just not understanding who the "purists" might be in this case?

I mention it only because I can see your remark being seized upon by an ignorant mob waving the "design theory doesn't work in practice" banner. Regretably those people seem to haunt any half-intelligent discussion on database design matters these days. The scientific and practical minded among us have to choose words carefully if we don't want to encourage them.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

hedera (4/28/2010)
Yes, I understand that I need a PK composed of 2 fields, each of which is an FK for another table. But my question is: in MS Access, how do I DO that??

I don't see where you're having issues.

You can edit the pk in the index section of the table (insert a line and add the 2nd column).

Or another way using the gui is to highlight both columns at the same time and hit the pk icon.

Is this where you're having issues?

hedera Right there with Babe Points: 732 More actions · Answer 3

Yes, that is where I'm having issues. Access 2007 doesn't let me highlight 2 columns at once, I've tried several times. I'll try editing the PK in the index section of the table and see if that works. Thanks for the suggestion.

hedera
======
Nature bats last.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

hedera (4/28/2010)
Yes, that is where I'm having issues. Access 2007 doesn't let me highlight 2 columns at once, I've tried several times. I'll try editing the PK in the index section of the table and see if that works. Thanks for the suggestion.

I'm not an Access guy but I think Access Help has the right answer... please search help for Map many-to-many relationships in a database diagram (ADP) where they go in detail about how to create a "junction" table.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

hedera Right there with Babe Points: 732 More actions · Answer 5

Thanks, Paul, I'll look for that - that isn't something my searches in Access help have turned up so far...

hedera
======
Nature bats last.

hedera Right there with Babe Points: 732 More actions · Answer 6

OK, I got my junction table completed, and it's stupid enough that I should have thought of it myself. :blush:

Access 2007 DOES let you create a composite PK. You have to use the CTRL key to highlight the fields you want...

Thanks to the kind people who tried to give me advice.

hedera
======
Nature bats last.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 7

Wanted to go back to the issue of the columns to PK themselves. Sometimes the answer is to only PK the intersecting columns. You do this when you don't want the entries to repeat. If I want to keep track of which lawyers have passed the bar in which states, I would not want the same name/state combination to repeat, as it would have no meaning and would complicate query design.

On the other hand, a Blockbuster wouldn't care if the same customer rented the same movie over and over again. Keying on the intersecting columns would frustrate this w/o adding another column to be part of the key. But in this case the better answer is to create a third identity column.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

hedera (5/2/2010)
Thanks to the kind people who tried to give me advice.

Glad to help - Thank you for the feedback 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.