Many-to-Many Relationships
The other common type of relationship is the many-to-many relationship. This kind of relationship
is implemented when records in both tables of the relationship can have multiple matching
records in the other. In our scenario, this happens for the product and category tables, because
we know that a product can exist in more than one category (one product with many categories),
and a category can have more than one product (one category with many products).
This happens because we decided earlier that a product could be in more than one category.
If a product could only belong to a single category, you would have another one-to-many
relationship, just like the one that exists among departments and categories (where a category
can??™t belong to more than one department).
If you represent this relationship with a picture, as shown previously in Figure 5-2, but
with generic names this time, you get something like what is shown in Figure 5-3.
Department Ta Table Cate le Category Ta ory Table le
Nat Nature re
Re Regional ional
Sea easonal onal
Animal
Flower
French
Italian
Iri Irish
Chri Christma tmas
Valentine Valentine??™s ??™s
CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 116
Figure 5-3. The many-to-many relationship between categories and products
Although logically the many-to-many relationship happens between two tables, databases
(including MySQL databases) don??™t have the means to physically implement this kind of relationship
by using just two tables, so we cheat by adding a third table to the mix.
Pages:
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204