[ACCEPTED]-many-to-many relationship in database design-relationship

Accepted answer
Score: 18

There's nothing inherently wrong with having 4 a many-to-many relationship, you'll just 3 need to create a Junction Table (which is what it sounds 2 like you're referring to with articlesTags) to facilitate 1 that relationship.

Score: 7

You're seeing the difference between a conceptual 30 database design (the N:N relationship) and 29 it's physical realisation. No matter how 28 you model your N:N relationship, you'll 27 need the aforementioned Junction Table to 26 make it work.

There's nothing wrong with 25 modeling a real world relationship as close 24 to the real world as you can as a general 23 statement. Clarity is king.

When it comes 22 to any performance question in any system 21 the answer usually boils down to "it depends".

If 20 your performance problem is related to WRITES 19 then a highly NORMALISED structure is best 18 and you'll want that Junction table. You'll 17 end up writing a lot less data and that 16 can speed things up substantially (though 15 you may burn that advantage by having to 14 do lookups before you create the inserts). Reading 13 from the individual normalised tables can 12 be very fast too.

If your problem is related 11 to analytical READS then a DENORMALISED 10 structure is best. Joins can be very performance 9 intensive if the tables are large and the 8 indices spread out. You'll sacrifice a 7 lot of space to gain a lot of time.

In general, you 6 want to look at the specifics of your situation 5 and weigh the pros and cons of each approach 4 before deciding on a solution. Personally, I've 3 always found it better to focus on Clarity 2 in the initial stages and refactor for performance 1 if I discover a problem later.

Score: 4

A many-to-many relationship exists in a 4 relationnal model, it's just an abstraction 3 of the mind. When you'll implement it there 2 will be a articles_to_tags table where you'll 1 have :

fk_article(INTEGER) fk_tag(INTEGER)

cf http://en.wikipedia.org/wiki/Many-to-many_(data_model)

Score: 3

There is no problem in using Many to Many 4 relationships. It is often required.

And 3 yes, it is not possible to create a many 2 to many relation, without using a third 1 table.

Score: 2

There's no problem with having a many-to-many 2 relationship if that's what the data require, but 1 you'll want a 3rd table to represent it.

More Related questions