Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Corbex
Starting Member
3 Posts |
 Posted - 2011-08-25 : 05:43:16
|
| Hi everybody!I have a many-to-many relationship problem:The facts:- A Prefecture has many Municipalities. A Municipality belongs to a single Prefecture. (one-to-may relationship)- A Prefecture has many Cities. A City belongs to a single Municipality.(one-to-may relationship) Now- A Municipality can have one or more Cities. - A City can have one or more Municipalities. That is, Municipalities and Cities have a many-to-many relation.I think of creating a tblMunicipalityCity junction table to serve the many-to-many relationship:...................................... tblMunicipalityCity...................................... -------------------[tblPrefecture] PrefectureID --------> PrefectureID [tblMunicipality] MunicipalityID ----> MunicipalityID [tblCity] CityID --------------------> CityID Would you think this is a good solution? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-25 : 05:45:46
|
| if you've many to many relationship best way is to add a relationship table containing fks from both the related tables------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Corbex
Starting Member
3 Posts |
Posted - 2011-08-25 : 05:54:51
|
| Thank you visakh16!Yes that's what I think too. The problem is the tblPrefecture table as it relates with a one-to-many relationship to both the tblMunicipality and tblCity tables. I think I shouldn't include the PrefectureID field as a FK in tblMunicipality and tblCity tables but instead include it in tblMunicipalityCity junction table.What do you think?Thanks again, Corbex. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-25 : 05:58:37
|
| yep that would make more sense as its equally related to city/muncipality------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Corbex
Starting Member
3 Posts |
Posted - 2011-08-25 : 06:04:40
|
| Thanks once again visakh16!You're very helpful!Corbex |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-25 : 06:28:36
|
| no probsyou're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Many-to-many relationship