locked
Many-to-many relationship RRS feed

  • Question

  • 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   

    The problem is that both Municipalities and Cities are related to Prefectures in a one-to-many relationship. So when Municipalities and Cities are related to one-another through the tblMunicipalityCity junction table there shouldn't be allowed to differ in terms of the Prefecture relationship - that is, both Municipalities and Cities should be related to the SAME Prefecture.

    So I think I shouldn't include the PrefectureID field as a FK in tblMunicipality and tblCity tables. Instead I should include it in tblMunicipalityCity junction table.

    Would you think this is a good solution?

    Thanks in advance, Corbex.

    • Moved by Kent Waldrop _ Thursday, September 1, 2011 1:08 PM It is a dabase design question (From:Transact-SQL)
    Thursday, August 25, 2011 12:33 PM

Answers

  • Your table structure would be something like

    Create Table tblPrefecture(
     PrefectureID int Not Null,
    -- Other columns go here
     Constraint PrefecturePK Primary Key (PrefectureID));
    Create Table tblMunicipality(
     MunicipalityID int Not Null,
     PrefectureID int Not Null,
    -- Other columns go here
     Constraint PKMunicipality Primary Key (PrefectureID, MunicipalityID),
     Constraint FKMunicipalityToPrefecture Foreign Key(PrefectureID) References tblPrefecture(PrefectureID));
    Create Table tblCity(
     CityID int Not Null,
     PrefectureID int Not Null,
    -- Other columns go here
     Constraint PKCity Primary Key (PrefectureID, CityID),
     Constraint FKCityToPrefecture Foreign Key(PrefectureID) References tblPrefecture(PrefectureID));
    Create Table tblMunicipalityCity(
     PrefectureID int Not Null,
     MunicipalityID int Not Null,
     CityID int Not Null,
     Constraint PKMunicipalityCity Primary Key (PrefectureID, MunicipalityID, CityID),
     Constraint FKMunicipalityCityToMunicipality Foreign Key(PrefectureID, MunicipalityID) References tblMunicipality(PrefectureID, MunicipalityID),
     Constraint FKMunicipalityCityToCity Foreign Key(PrefectureID, CityID) References tblCity(PrefectureID, CityID));
    

     

    That meets your design requirements:  1) A Prefecture may have 0, 1, or many Municipalities, 2) A Municipality must have exactly one Prefecture, 3) A Prefecture may have 0, 1, or many Cities, 4) A City must have exactly one Prefecture.  5) A Municipality may be mapped to 0, 1, or many Cities, 6) A City may be mapped ot 0, 1, or many Municipalities, and 7) If a City and Municipality are mapped to each other, they must both belong to the same Prefecture.

    Tom

     

    • Marked as answer by Corbex Thursday, August 25, 2011 4:02 PM
    Thursday, August 25, 2011 3:13 PM

All replies

  • It sounds like the best solution would be to have PrefectureID in tblMunicipality, and have MunicipalityID in tblCity.  This would remove the need for any additional tables, and should preserve the relationships that you have defined.

    Thursday, August 25, 2011 1:06 PM
  • I would do this by making the primary key of tblMunicipality be a composite key of PrefectureID and MunicipalityID (In whatever order will work best for the queries you expect to be doing).  Do the same thing for tblCity making a composite primary key of PrefectureID and CityID.  Then the primary key of tblMunicipalityCity is a composite key of PrefectureID, MunicipalityID, and CityID with two foreign keys, one to tblMunicipality of PrefectureID and MunicipalityID, and one to tblCity of PrefectureID and CityID.  Then you are guarenteed that any City/Municipality match must have the same PrefectureID.

    Tom

    Thursday, August 25, 2011 1:20 PM
  • I didn't look closely into the problem, but I suggest to drop tbl prefix from your table naming convention. Tbl has nothing except polluting the model, making it type more characters and obscuring it. v prefix for views are OK, but tbl for tables always make me shudder.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, August 25, 2011 1:23 PM
    Answerer
  • Tom thanks for your answer!

    So what fields would tblMunicipalityCity be made of? I'm a bit confused! I understand your suggestion on composite primary keys for tblMunicipality and tblCity.

    Thanks, Corbex

    Thursday, August 25, 2011 1:34 PM
  • Thanks Naomi! I don't use the tbl prefix in my models. I only use it when communicating for other people to clearly see tables and fields, etc. Not in real life though!

    Thanks again for your suggestion!

    Thursday, August 25, 2011 2:05 PM
  • Thanks Tim! What about the many-to-many relationship between tblMunicipality and tblCity? Municipalities can have more than one Cities and Cities more than one Municipalities.

    Thanks again, Corbex

    Thursday, August 25, 2011 2:06 PM
  • Good! I don't think you need it to communicate here, though :)
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, August 25, 2011 2:06 PM
    Answerer
  • If I have understood your query well, you need 5 tables in total -

    1. Prefectures : Master Table
    2. Municipalties : Master Table # Having PrefectureID # Because A Municipality belongs to a single Prefecture
    3. Cities : Master Table # Having MunicipalityID # Because A City belongs to a single Municipality and thereby getting related to the same PrefectureID
    4. Prefectures_Cities : Relationship Table # CityID being Unique for each PrefectureID
    5. Cities_Municipalties : Relationship Table # I have a doubt here. Please clarify these statements - A City belongs to a single Municipality and A City can have one or more Municipalities

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Thursday, August 25, 2011 2:52 PM
  • Your table structure would be something like

    Create Table tblPrefecture(
     PrefectureID int Not Null,
    -- Other columns go here
     Constraint PrefecturePK Primary Key (PrefectureID));
    Create Table tblMunicipality(
     MunicipalityID int Not Null,
     PrefectureID int Not Null,
    -- Other columns go here
     Constraint PKMunicipality Primary Key (PrefectureID, MunicipalityID),
     Constraint FKMunicipalityToPrefecture Foreign Key(PrefectureID) References tblPrefecture(PrefectureID));
    Create Table tblCity(
     CityID int Not Null,
     PrefectureID int Not Null,
    -- Other columns go here
     Constraint PKCity Primary Key (PrefectureID, CityID),
     Constraint FKCityToPrefecture Foreign Key(PrefectureID) References tblPrefecture(PrefectureID));
    Create Table tblMunicipalityCity(
     PrefectureID int Not Null,
     MunicipalityID int Not Null,
     CityID int Not Null,
     Constraint PKMunicipalityCity Primary Key (PrefectureID, MunicipalityID, CityID),
     Constraint FKMunicipalityCityToMunicipality Foreign Key(PrefectureID, MunicipalityID) References tblMunicipality(PrefectureID, MunicipalityID),
     Constraint FKMunicipalityCityToCity Foreign Key(PrefectureID, CityID) References tblCity(PrefectureID, CityID));
    

     

    That meets your design requirements:  1) A Prefecture may have 0, 1, or many Municipalities, 2) A Municipality must have exactly one Prefecture, 3) A Prefecture may have 0, 1, or many Cities, 4) A City must have exactly one Prefecture.  5) A Municipality may be mapped to 0, 1, or many Cities, 6) A City may be mapped ot 0, 1, or many Municipalities, and 7) If a City and Municipality are mapped to each other, they must both belong to the same Prefecture.

    Tom

     

    • Marked as answer by Corbex Thursday, August 25, 2011 4:02 PM
    Thursday, August 25, 2011 3:13 PM
  • Hi Vinay

    A Prefecture has many Municipalities and Cities.

    A Municipality either belongs to a City (if the city is big) OR includes a number of Cities (if the Municipality is big and the cities/towns are small).

    A Municipality belongs to a single Prefecture.

    A City either has one or more Municipalities (if it's a big one) OR belongs to a single Municipality (if the city/town is small and the Municipality big).

    A City belongs to a single Prefecture.

    As you see the relationship between Municipalities and Cities is many-to-many. That's why I think a junction MunicipalityCity table is needed.

    Moreover, when a Municipality and a City are related to each other they must belong to the same Prefecture. So I'm not sure whether I have to include PrefectureID as a FK to both Municipality and City tables OR instead include it in the junction MunicipalityCity table.

    I know it's a bit complicated!...

    Thanks for your help, Corbex

    Thursday, August 25, 2011 3:33 PM
  • Thank you very much! Your quite clear, very helpful!

    Cheers, Corbex

    Thursday, August 25, 2011 4:02 PM
  • Tom's reply seems to be correct !
    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Friday, August 26, 2011 4:47 AM
  • I'd like to thank you all for help! You're all great!

    Corbex
    Friday, August 26, 2011 10:21 AM