1 Reply Latest reply on Jul 15, 2013 8:14 AM by philmodjunk

    Many to many relationships



      Many to many relationships


           In my relational database I used a many to many relationship between two foreign keys. The database works good so far but I was wondering if that is a bad approach and if so how should I fix it? Thanks!

        • 1. Re: Many to many relationships

               That would depend on the details of what you set up. Did you use a join table? How does the relationship need to function?

               With this set of relationships that produces the "Textbook" form of a many to many relationship:

               Table1::primarykey = Join::Foreignkey1
               Table2::primarykey = Join::ForeighKey2

               Any given record in Table 1 may be linked to any number of records in Table2 and vice versa.

               If you have this relationship:

               Table1::ForeignKey = Table2::ForeignKey

               It looks like a many to many relationship, but doesn't fit the "Textbook" definition of such. That doesn't, however, mean that it won't work or is somehow a bad idea. You just have to understand how it works and what aspects of that relationship won't work and see if those capabilities/limitations fit the needs of your database.

               With this relationship, any record in Table 1 will link to all records in Table2 with the same match field value. Any other record in Table 1 with the same value in the foreignKey field will also match to the same set of records in Table2. Where this can fail for you is if you have Record A that needs to link to records 1, 2, and 3 in the related table and yet Record B needs to link to records 3, 4, and 5. Record 3 can be part of one such group of records with the same foreign key, but not two unless you start employing multi-value keys--which is a second way to implement a "textbook" many to many relationship that has its uses, but is less flexible than using a join table.