SSWUGtv
With Stephen Wynkoop
In this edition Laura Rose shares tips about how to work with people who take more than they give?
Watch the Show
Join Tables
Ali writes in asking, “Would you please share some example about Join Tables in Ms. SQL Server 2005- 2008?” Ali, Today is the day.
If I understand your question correctly a join table is the physical representation of joining two tables with a many to many relationship. This means that Zero, One, or Many records in Table A may be joined to Zero, One, or Many records in Table B. This relationship is implemented through an additional table having a natural key consisting of the primary keys from both Table A and Table B.
A many to many physical relationship diagram joining many Manufacturers to many Products could look like the following. Notice that the Manufacturer_Product table has the primary keys from both the Manufacturer table and the Product table.
I would like to point out that in any relational database (at least SQL Centric) this is the technique used to implement a physical many to many relationship. Other products or relational engines may appear to do so, but you don’t really know what is going on behind the covers.
A common naming convention for join tables is to include the names of all the tables participating in the join separated by an underscore.
Personally, for a many to many Join Table, I prefer to use the composite key values from the parent tables as the primary key. this results in a unique constraint as well as a nice index for joining between the three tables involved. If you had two records with the same keys from Manufacturer and Product you end up with an invalid combination since a single Product may only be related to a single Manufacturer one time. The only exception to this is to add another relationship to the table or a differentiated attribute such as time segments (start and end dates).
Please drop a note to btaylor@sswug.org with your comments and/or questions and we’ll respond to them either here in the newsletter, online at Facebook or maybe even in SSWUGtv.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)