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!
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.
Retrieving data ...