Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

many to many relationship Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:05 AM
Points: 7, Visits: 15
Hi, why do we need a junction table in a many to many relationship? Why can't everything be in just 2 tables?
Post #1567638
Posted Monday, May 5, 2014 11:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 21, 2014 4:16 PM
Points: 339, Visits: 794
I've never seen any way to implement it in a relational database without a junction table.

Even the Wikipedia definition includes the statement "...it is necessary to implement such relationships physically via a third junction table..." http://en.wikipedia.org/wiki/Many-to-many_(data_model)

The reason why you don't want to use just two tables is because if you did then you'd be duplicating lots of data.
Post #1567648
Posted Monday, May 5, 2014 11:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:05 AM
Points: 7, Visits: 15
But if you set multiple columns as primary keys so if you are entering something new the server will tell you if its duplicated, wouldn't that be ok?

I'm sorry, I'm new to SQL.
Post #1567653
Posted Monday, May 5, 2014 12:07 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 743, Visits: 4,745
Why not just use a junction table? Normally, the junction table holds the foreign keys from the two parent tables and then any information that is specific to the relationship (not the other two tables).

In a nutshell, it would be a LOT easier to query a properly designed database. (Been there, seen the mess that makes!) What's your argument against using a junction table? Just that it requires some space?
Post #1567659
Posted Monday, May 5, 2014 12:07 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 15,661, Visits: 28,048
For you to have a lot of one thing that relates to one or more other things, you have to have a way to map between in them that allows for multiple entries on both sides.

If we had a situation where you have a primary key in table A and a related column in table B, you can then ensure that there will only ever be one value in Table A. But, in Table B, you can have lots and lots of values for Table A, but only ONE for each value of Table B. You can reverse that relationship too. One value in Table B with lots of values in Table B. But, to make lots of values in Table A relate to lots of values in Table B, more than one relationship, right? You have to have a way to match a primary key from each table. That mechanism is the interim table.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1567660
Posted Monday, May 5, 2014 12:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:17 AM
Points: 266, Visits: 134
Here's an example of a common many-to-many relationship.

Assume we have many products, each which can be categorized in more than one category. Our primary keys are numeric IDs (ProductID and CategoryID respectively).

dbo.Product (ProductID INT PRIMARY KEY, Name VARCHAR(50), ... )

ProductID Name
1 Pizza
2 Tacos
3 Curry

dbo.Category (CategoryID INT PRIMARY KEY, Name VARCHAR(50), ... )

CategoryID Name
1 Dinners
2 Frozen Foods
3 Ethnic Foods

There is no place you can put a single foreign key on either of the two tables that does not constrain you to a one-to-many relationship. If you create a category foreign key on the product, then that given product record can only be associated with one category. If you create a product foreign key on category, then only one product can ever appear in that category!

That's why we create a junction table (informally known as a join table or a cross-reference table). We can create the primary key as a composite of the two primary keys from the tables we are relating. This allows us to assign products to as many categories as we'd like without allowing duplication (no need or reason to assign the same product to the same category more than once).

CREATE TABLE dbo.ProductCategory
(
ProductID INT NOT NULL CONSTRAINT FK_ProductCategory_Product FOREIGN KEY REFERENCES dbo.Product(Product_ID)
, CategoryID INT NOT NULL CONSTRAINT FK_ProductCategory_Category FOREIGN KEY REFERENCES dbo.Category(Category_ID)
, CONSTRAINT PK_ProductCategory PRIMARY KEY CLUSTERED (ProductID, CategoryID)
)

We can now relate our 3 sample products to each of our sample categories.

ProductID CategoryID
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

Hope this explains things in a clear manner.

Post #1567674
Posted Monday, May 5, 2014 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:05 AM
Points: 7, Visits: 15
This helps a lot, thank you everyone!
Post #1567684
Posted Monday, May 5, 2014 1:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:07 AM
Points: 1,651, Visits: 4,705
aajavaherian (5/5/2014)
Hi, why do we need a junction table in a many to many relationship? Why can't everything be in just 2 tables?

So we know what you're suggesting, show us an example schema of a many-to-many relationship between 2 tables using only 2 tables.

With a normalized relational schema, you can only implement many-to-many relationship using 2 tables, if it's also a self-referencing relationship. For example, a Person table can have a Person_Relationship table linking employees that report to managers or children related to parent.

However, if you have a Person table and an Address table, and a person can have multiple addresses, and an address can be shared by multiple people, then you need a 3rd Person_Address table.
Post #1567687
Posted Monday, May 5, 2014 1:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:05 AM
Points: 7, Visits: 15
if i was describing lets say classes and students where each student can be in more than one class and each class can have multiple students. Or how about just using Table A by itself. I'm sorry, I'm new to SQL.

Table A
ID Student Class
1 Bob math
2 Tim math
3 Linda english
4 Linda science
Post #1567696
Posted Monday, May 5, 2014 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 13,296, Visits: 12,147
aajavaherian (5/5/2014)
if i was describing lets say classes and students where each student can be in more than one class and each class can have multiple students. Or how about just using Table A by itself. I'm sorry, I'm new to SQL.

Table A
ID Student Class
1 Bob math
2 Tim math
3 Linda english
4 Linda science


You have just created the same table as posted in the example above, except you added an ID column. The biggest difference is you have created it without normalization. What happens when Linda changes her name? How would you validate the text entries for this type of thing? You want to store the primary key of the other tables, not copies of the values.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1567709
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse