This third table,
called a junction table (also known as a linking table or associate table) and two one-to-many
relationships will help achieve the many-to-many relationship. The junction table is used to
associate products and categories, with no restriction on how many products can exist for a
category or to how many categories a product can be added. Figure 5-4 shows the role of the
junction table.
Figure 5-4. The many-to-many relationship among categories and products
Note that each record in the junction table links one category with one product. You can
have as many records as you like in the junction table, linking any category to any product.
The linking table contains two fields, each one referencing the primary key of one of the two
linked tables. In our case, the junction table will contain two fields: a category_id field and a
product_id field.
Cate Category ory Table Junction nction Table Pro Produ duct ct Table
Cate Category 1
ory Cate Category 1
ory Cate Category 2
ory Pro Produ duct 1
ct Pro Produ duct 1
ct Cate Category 1
ory Pro Produ duct 2
ct Cate Category 1
ory Pro Produ duct 3
ct Cate Category 2
ory Pro Produ duct 3
ct Pro Produ duct 2
ct Pro Produ duct 3
ct Cate Category ory Table Pro Produ duct ct Table
Cate Category 1
ory Cate Category 2
ory Cate Category 3
ory Pro Produ duct 1
ct Pro Produ duct 2
ct Pro Produ duct 3
ct Pro Produ duct 4
ct Pro Produ duct 5
ct CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 117
Each record in the junction table will consist of a product and category ID pair (product_id,
category_id), which will be used to associate a particular product with a particular category.
Pages:
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205