none
options to solve many to many relationship in SQL

    Question

  • We have many to many relationship that requires more than one bridge table. What will be the best option for designing these relational tables? We have pet and adult relationship but also relationship between pet because they are blood related and live together. Then, the relationship between adults living together or  married. The relationship is many to many for several tables what will be the best option to design these relational tables. Thanks in advance.

    Friday, April 21, 2017 2:11 PM

All replies

  • You have one relationship table for every relationship in your model.  One way to think about relational modeling is that every table is a set of true statements about your domain.  So you have one table that records statements of the form: "Spot is related to Fluffy", and separate table for statements of the form: "Fluffy lives with Spike".

    Technically in the Relational model LivesWith is a binary relation, or a subset of the Cartesian product of two sets (both of which happen to be the set of pets), and so is modeled as a separate table.

    David


    Microsoft Technology Center - Dallas
    My blog



    Friday, April 21, 2017 3:45 PM
  • If you're looking to stick to 3rd normal form (3NF) or higher you'll want to implement an associate entity, aka... intersection table, junction table, join table, etc. (the list goes on https://en.wikipedia.org/wiki/Associative_entity). I've found a few good examples that can better explain it and one that pertains very much to what you're talking about (see the IBM link).

    https://www.ibm.com/support/knowledgecenter/en/SSRAR8_8.8.0/com.ibm.ima.using/appendixes/bdm_awm_transf_rules/T1.html

    https://docs.oracle.com/cd/B40099_02/books/ConfigApps/ConfigApps_TablesColumns6.html


    • Edited by JJordheim Friday, April 21, 2017 4:21 PM
    Friday, April 21, 2017 4:21 PM
  • Thank you very much David for your reply, I understand the relationship between tables (one to one, one to many, and many to many) and my question is specific how to resolve many-to-many relationship other than the traditional known way by using a bridge table. Is there any other way you know of, hear about? thanks 
    Friday, April 21, 2017 6:00 PM
  • >Is there any other way you know of, hear about?

    Not a good way.  There are lots of bad ways that people occasionally invent, trying to be clever, like using a single table for multiple different kinds of relationships.  Or even using a single table for all relationships in a database.  But these always end in tears.

    David


    Microsoft Technology Center - Dallas
    My blog

    Friday, April 21, 2017 8:57 PM