many to many relationship

Question

If I have two tables - table beer and table distributor, each one have a primary key and a third table that have the foreign keys and calls beer_distributor

enter image description here

Is it adequate a new field (primary key) in this table? The other way is with joins, correct? To obtain for example DUVEL De vroliijke drinker?

not sure what your standards are but we would tend to add '_rel' to the beer_distributor table name to indicate that its merely a relationship table detailing links between two other tables. Discourages folks from being tempted to put info in there that might better live in one of the other two tables. Not saying that other info shouldn't live there, for example if you had more than one distributor of a beer the price might differ for example and this value could live in the rel table. — MadMurf, Apr 28 '11 at 02:00
@MadMurf +1 for naming conventions. Personally, I've named junction table as above, separating related table names with an underscore. Only junction tables would have an underscore because any "multi-word" table names would be camelCase instead. What matters is that you pick one and always stick with it. — Wiseguy, Apr 28 '11 at 02:05
Cheers @Wiseguy, the standard in our place for DB2 zOS tables has underscores in table names rather than camelCase so hence the "_REL". I'd prefer your way but that decision was made in the 70s. :-) — MadMurf, Apr 28 '11 at 02:08
@MadMurf If that decision was made decades ago, so be it. It just shows you're succeeding at the "always stick with it" part. :-) — Wiseguy, Apr 28 '11 at 02:11

Wiseguy · Answer 1 · 2011-05-20T16:35:45.347

You've definitely got the right idea. Your beer_distributor table is what's known as a junction table. JOINs and keys/indexes are used together. The database system uses keys to make JOINs work quickly and efficiently. You use this junction table by JOINing both beer and distributor tables to it.

And, your junction table should have a primary key that spans both columns (a multiple-column index / "composite index"), which it looks like it does if I understand that diagram correctly. In that case, it looks good to me. Nicely done.

Jason McCreary · Accepted Answer · 2011-04-28T02:10:46.440

2

I would put a primary key in the join table beer_distributor, not a dual primary key of the two foreign keys. IMO, it makes life easier when maintaining the relationship.

UPDATE

To emphasize this point, consider having to change the distributor ACOO9 for beer 163. With the dual primary key, you'd have to remove then reinsert OR know both existing values to update the record. With a separate primary key, you'd simply update the record using this value. Comes in handy when building applications on top this data. If this is strictly a data warehouse, then a dual primary key might make more sense from the DBA perspective.

UPDATE beer_distributor SET distributor_id = XXXXX WHERE beer_id = 163 AND distributor_id = AC009

versus

UPDATE beer_distributor SET distributor_id = XXXXX WHERE id = 1234

edited Apr 28 '11 at 02:10

answered Apr 28 '11 at 01:51

Jason McCreary

71,546
23
135
174

Looks good as is IMHO, what would be the advantages of adding another column for primary key when maintaining the table? – MadMurf Apr 28 '11 at 01:58
1

I don't think an UPDATE in a junction table makes sense. Either the relationship exists, or it doesn't. If it changes, that's a different relationship, so delete that one and add the new one. If you're talking about if the distributor's `distributor_id` changes, just `UPDATE` where `distributor_id` is the value you're looking for. _Side note: if your records' IDs are subject to change, you're just asking for trouble when you must update across the system. Just don't do it._ :-) – Wiseguy Apr 28 '11 at 02:10
2

To rephrase concisely, I personally would not want to add a third column just for primary key as you suggest. – Wiseguy Apr 28 '11 at 02:13
@Wiseguy, I'm not talking about the `distributor_id` changing, I'm talking about the *relationship* changing. You can treat it like a new relationship - `DELETE`/`INSERT`. Or that the exiting relationship changed - `UPDATE`. To each their own. – Jason McCreary Apr 28 '11 at 02:13
1

@Jason McCreary Okay thanks, I gotcha. However, I still say that would constitute a different relationship entirely. Also, a new primary key would serve no purpose here. I don't think you would ever have the primary key's id used anywhere, so you'd still need to check both columns for the values you're looking to replace. – Wiseguy Apr 28 '11 at 02:16
1

And that's the beauty and the curse of programming - *there's more than one way to do it.* – Jason McCreary Apr 28 '11 at 02:19
1

lol, very true. But see the update to my last comment after you posted this one. I still think that's valid. BTW, fun, intelligent discussion. I hope I don't seem like a jerk. – Wiseguy Apr 28 '11 at 02:22
No worries. So long as you don't force your view, you're not a jerk to me. From a DBA perspective I completely agree with you. But from my application development experience, a composite key or `DELETE`/`INSERT` approach has continually proven to be a pain. – Jason McCreary Apr 28 '11 at 02:34
Yay, friends! I'd be interested to hear about your experiences with this, though, because I just don't see how adding a separate primary key would help at all. If we are to continue this discussion, perhaps we should do it elsewhere and not clutter up this thread any further. – Wiseguy Apr 28 '11 at 02:43
2

@ similar question: http://stackoverflow.com/questions/3092422/does-a-join-table-association-table-have-a-primary-key-many-to-many-relations – anvd Apr 28 '11 at 02:48

many to many relationship

2 Answers2

UPDATE

Linked

Related