April 10, 2010 at 6:09 am
Hi,
This is a very noobish question to ask, but how do I implement a many-to-many relationship in SQL Server via the designer? Should the intersection table have uniqueidentifiers (well the same field data type as the tables to join) and be PKs?
So:
Part1
ID (PK Uniqueidentifier)
PartIntersection
ID (PK Uniqueidentifier, draw relationship from ID in Part1 to PartIntersection
MaterialID (PK Uniqueidentifier, draw relationship from MaterialID in Material to PartIntersection MaterialID)
Material
MaterialID (PK Uniqueidentifier)
ID (FK Uniqueidentifier, but just a normal field)
Is this correct?
Thanks
April 10, 2010 at 6:25 am
I don't see why you would put that last one ine there (ID (FK Uniqueidentifier, but just a normal field)).
The 2 tables linked together need an id. Then the mm relationship table needs to have both ids to make the link. No other id field is required for that process.
April 10, 2010 at 6:41 am
info-849042 (4/10/2010)
how do I implement a many-to-many relationship in SQL Server
This shold be done at modeling phase independent of the technology of choice therefore you do it as you do it for any rdbms, by adding a bridge table...
When your logical model looks like...
TableA <<===>> TableB
...you implement it as
TableA <===>> TableAB <<===> TableB
For better visualization picture TableA as being Employees table and TableB as being Skills table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 10, 2010 at 9:26 am
Ok but what I am confused about is that the Employee table has a PK, so that will link to the intersecting table. The intersecting table have the PK of the other table in the relationship, and will this link to the PK of the other table?
So, EmployeeID <==> (Intersecting table PK) EmployeeID
SkillID <==> SkillID (in other table part of relationship
And all the keys in the intersecting table are PKs, correct?
April 10, 2010 at 11:41 am
in any database system many to many relationship can be achieved only using third mapping table.
April 10, 2010 at 11:47 am
for example -
employee and skill many to many relation-
employee
empId [pk]
empName
Skills
skillId [Pk]
SkillName
EmployeeSkills
EmpId[Pk]
SkillId[Pk]
April 10, 2010 at 1:23 pm
vidya_pande (4/10/2010)
for example -employee and skill many to many relation-
employee
empId [pk]
empName
Skills
skillId [Pk]
SkillName
EmployeeSkills
EmpId[Pk]
SkillId[Pk]
Exactly!
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 15, 2010 at 7:54 am
Wouldn't both fields in the intersecting table be PKs?
April 15, 2010 at 8:04 am
Yes... it cannot really be otherwise.
You can obvioulsy add more fields when you need to, but the 2 most important fields are the 2 pks.
April 15, 2010 at 9:51 am
Ninja's_RGR'us (4/15/2010)
Yes... it cannot really be otherwise.You can obvioulsy add more fields when you need to, but the 2 most important fields are the 2 pks.
A purist data modeler would say that there is no reason to add columns other than the ones needed to link both tables but - in the real world, who knows?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 15, 2010 at 9:54 am
Here's 1 exemple. I needed to keep the date updated / added and by whom on that table. On top of that I needed a deleted flag and a version id.
Maybe my design was a little of but it worked for me. Now keep in mind that those mm relationships almost became another object so that could be a bit different that what the op is trying to do.
April 16, 2010 at 8:02 am
Ninja's_RGR'us (4/15/2010)
Here's 1 exemple. I needed to keep the date updated / added and by whom on that table. On top of that I needed a deleted flag and a version id.
That's a fair example of how "purism" bumps head-on with "real world".
I think your design perfectly fits described business requirements - flawless.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 28, 2010 at 11:50 am
I've now read this thread twice, as I'm building a new relational database and have a couple of instances where I need to create such a table. I understand the design concept, but I'm doing this in MS Access rather than SQL Server, and the Access GUI won't let me create a table with two PKs. I've done some reading that indicates I should be able to create such a table with a Data Definition Language query (or statement?), but I'm really confused about what the syntax might be.
hedera
======
Nature bats last.
April 28, 2010 at 2:01 pm
You don't need 2 pk in the MM table. The PK is both columns at the same time. Then you have 1 fk for each of the table "joined" tables.
April 28, 2010 at 2:38 pm
Yes, I understand that I need a PK composed of 2 fields, each of which is an FK for another table. But my question is: in MS Access, how do I DO that??
hedera
======
Nature bats last.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply