How to Handle a Many-to-Many Relationship with PHP and MySQL
By Tony Marston2005-04-06
Introduction
|
|
What exactly is a many-to-many relationship? When your user tries to explain to you how a pair of business objects are related he might say "many of these can be related to many of those, and many of those can be related to many of these." If you attempt to draw such a relationship in an Entity Relationship Diagram (ERD) you will probably end up with something like this:
Those of us who are more experienced in database design will immediately see this as a totally illegal relationship. The only sort of relationship which is possible is a 'one-to-many', so the novice might redraw his ERD as follows:
That may look better, but again it is impossible to implement in any RDBMS that I have come across. You can have A-to-B in a 'one-to-many' relationship, or B-to-A in a 'one-to-many' relationship, but you cannot have both at the same time. So what are we to do?
The solution is to create a new table which will act as the 'many' in two 'one-to-many' relationships, with each of the original tables acting as the 'one'. This is shown in the following diagram, with the new table identified as 'X'.
Table 'X' is sometimes referred to as a 'link', 'intersection' or 'xref' (cross-reference) table.
Tutorial Pages:
» Intended Audience
» Introduction
» Database schema
» Form design
» Summary
|
