MSSQL 2005 many-to-many relationship ?

[HernanB]

hi, <br />
<br />
I'll explain it by using an example<br />
<br />
if i have two tables PRODUCTS and PROVIDERS.<br />
Base on the SQL theory I would need to create a third table (to avoid many-to-many relationship).<br />
<br />
BUt I really don't understand what if i join these tables ( and get many-to-many) and use them as so ?<br />
<br />
every single producto will his own vendor ... where the problem ??<br />
<br />
maybe I am confused with the relational-number theory...<br />
<br />
<br />
THanks so much and GOD always bless you<br />

 

[TheMadProfessor]

The main problem that arises in trying to keep it to two tables is that it creates nightmares for maintaining consistant data. If your PRODUCT table includes a separate row for every provider carrying a given product, a change to a product's elements would require updating multiple rows instead of a single one. (The entire reasoning for this gets complex - do a Google for 'database normalization' if you want to get into the whys and wherefores)

 

[Schmitty]

If you just join the two tables with a many-to-many relationship without using a interface table then you query results will be not only be huge but it will be very easy to write a query that gets into an infinite loop (keeps running forever).

To safely create a many-to-many relationship you need to create a third table (called an "interface table") which controls the relationship. This interface table will basically just hold PRODUCT_ID, PROVIDER_ID and any additional fields that have to do with the relationship itself (like if you wanted to give the relationship a name for some reason).

But, if you want one product for one provider, then that's actually a one-to-one relationship in which you don't need a interface table. You can just add a PRODUCT_ID field to the providers table or visa/vera.

 

[djpitagora]

if a product only has one vendor as you are saying then why is it a many-to-many? If however a product can have more then one vendor you will have to make a 3rd table.