Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2010
    Posts
    53

    Symmetric Self Referencing Many to Many Relationship

    I've got this problem i've been mulling over for ages and can't seem to come up with an answer for it:

    I want to create a db that stores country information so i'll probably have a country tables as follows:

    countryid countryname etc
    1 france
    2 germany
    3 spain


    I also want to store information about what country borders another country. To my mind this is a self referencing many to many relationship so i create a borders table to resolve the many to many as follows:

    countrya countryb
    1 2
    1 3

    the problem with this is the relationship borders is symmetrical ie. if france borders germany then germany should also border france, yet with this design france can border germany without germany bordering france.

    any thoughts, ideas, comments, etc. would be most welcome.

    thanks

    j

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your "self referencing many to many relationship borders table" is fine

    all you have to do is make sure that whenever you store a pair of country FKs, the lower one goes first

    this lets you avoid storing the relationship twice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    53
    hi rudy,

    many thanks for your quick reply... i'm afraid i'm going to show my ignorance here:

    how does having the lower fk go first help me?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    how does having the lower fk go first help me?
    i'd be happy to tell you right after you explain in a bit more detail what your problem was again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Another way to do this is to name or number each border and treat the border itself as a set (ie. the set of adjacent countries):

    CREATE TABLE Borders (BorderNum INTEGER NOT NULL, CountryNum INTEGER NOT NULL, PRIMARY KEY (BorderNum, CountryNum));

    This has the possible advantage that there is only one column in which to look for a country.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    In your join table you will have columns for CountryA and CountryB.
    Which table comes first can be arbitrary, though if you use Rudy's suggestion of enforcing a constraint against CountryB ever being a lower value than CountryA you can prevent duplicate relationships such as:

    Code:
    Record	CountryA	CountryB
    1	USA		Canada
    2	Canada		USA
    3	USA		Mexico
    ...not that such duplicates would be a huge issue, as long as you UNION the table to itself to get the entire set of relationships from either direction, and use this UNION view in all your code joins:

    Code:
    select	CountryA, CountryB from CountryNeighbors
    UNION
    select	CountryB as CountryA, CountryA as CountryB from CountryNeighbors
    Yields:
    Code:
    CountryA	CountryB
    USA		Canada
    Canada		USA
    USA		Mexico
    Mexico		USA
    This result set would effectively show you all bordering countries for every country.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    If you just want to enforce the constraint that borders must be symmetrical then you could do this:

    CREATE TABLE Borders
    (countrya INTEGER NOT NULL,
    countryb INTEGER NOT NULL,
    PRIMARY KEY (countrya,countryb),
    FOREIGN KEY (countryb,countrya) REFERENCES Borders (countrya,countryb));

    INSERT INTO Borders VALUES (1,2),(2,1);

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas View Post
    If you just want to enforce the constraint that borders must be symmetrical ...
    that's a cute trick, but it more or less locks you in to double entries

    (by the way, in which database systems would inserting the (1,2) fail because the (2,1) isn't there yet? does this technique require suspending FK checking until after the transaction? this is getting messier by the minute...)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2010
    Posts
    53
    wow, this is some forum, thanks for all your replies... i think i'm getting left behind here :-)

    Quote Originally Posted by r937 View Post
    i'd be happy to tell you right after you explain in a bit more detail what your problem was again?
    sorry, rudy, yes i think your previous reply has sunk in.. keeping the lower id first prevents duplicate entries, right??

    my problem then is how do i successfully query the db to tell me who borders germany when france borders germany but germany doesnt border france... if you see what i mean??

    i guess blindman's union qry does this for me??

  10. #10
    Join Date
    Apr 2010
    Posts
    53
    Quote Originally Posted by dportas View Post
    Another way to do this is to name or number each border and treat the border itself as a set (ie. the set of adjacent countries):

    CREATE TABLE Borders (BorderNum INTEGER NOT NULL, CountryNum INTEGER NOT NULL, PRIMARY KEY (BorderNum, CountryNum));

    This has the possible advantage that there is only one column in which to look for a country.
    i can see that this would solve the problem but it kind of feels a bit wrong - a bit too border centric - i'm probably mixing the db design with the end application here - but this design feels like it would force me to be adding countries to borders, whereas i'd envisaged adding a country neighbours to countries, if you see what i mean?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    my problem then is how do i successfully query the db to tell me who borders germany
    SELECT countrya FROM borders WHERE countryb = 'germany'
    UNION ALL
    SELECT countryb FROM borders WHERE countrya = 'germany'

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2010
    Posts
    53
    Quote Originally Posted by r937 View Post
    SELECT countrya FROM borders WHERE countryb = 'germany'
    UNION ALL
    SELECT countryb FROM borders WHERE countrya = 'germany'

    yeah,

    in your/anyone's opinion is it better to, or what are the advantages/disadvantates of :

    a) using a union qry for the data retrieval, & storing lower fk first in the borders table to prevent duplication.

    compared with

    b) automatically adding duplicate entries to the border table by adding the opposite entry ie. (2,1) & (1,2) or (4,3) & (3,4) every an entry is made

    any thoughts, comments, etc. much appreciated,

    cheers

    j

  13. #13
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    One of the goals of normalization in database design is to reduce or eliminate duplicate data. (If you have duplicate data, what happens when you update/delete the first row, but forget to update/delete the second??? After the change, if you don't know the answer already, how do you then know which row is correct?)

    After all, it's very easy to add a check constraint to the table, so that CountryA must be less than CountryB. (and, in a case like this, where once entered, the table data will change rarely, it's not as if there would be a performance hit...)
    Last edited by loquin; 04-21-10 at 19:32.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937 View Post
    that's a cute trick, but it more or less locks you in to double entries
    Agreed. I'd never implement something like this, though it is interesting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by jx12345 View Post
    how do i successfully query the db to tell me who borders germany when france borders germany but germany doesnt border france... if you see what i mean??

    i guess blindman's union qry does this for me??
    Yes. Create my union query as a view, and you won't have to think about it again.

    Select * from UNIONVIEW where CountryA = 'Germany'

    That is all you would need.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •