Many to Many Relationships
You've probably built a database with a few tables that relied on Foreign Keys to provide a relationship to each other.
A product table might have a field called category_id to specify which of the categories in the category table this product belonged to.
What do you do if you want a product to be in multiple categories? Make a list of category_id's in the product table? That is, unfortunately, kind of common among beginner web developers. This immediatly causes issues to arise. That is not to say it can't work, but it is saying that you've just limited your database in a way that would be completely unacceptable to a project of any real size or importance.
So what should you be doing instead? Well, you should be using a join table that contains two fields: A product id and a category id. Below is what our three tables might look like.
Products Table:
name: tblProducts
fields: prod_id, prod_name
Category Table:
name: tblCategory
fields: cat_id, cat_name
Our Join Table:
name: tblProduct2Category
fields: cat_id, prod_id
What this will allow us to do is keep an index of which products are related to which categories. The product table no longer has use for a category_id field.
So let's assume some products and categories:
In tblProducts
prod_id prod_name
1 Small Blue Widget
2 Small Red Widget
3 Large Blue Widget
4 Large Red Widget
We'll make a few categories while we're here.
In tblCategory
cat_id cat_name
1 Large Widgets
2 Small Widgets
3 Blue Widgets
4 Red Widgets
Now the joining table will pull together our products and categories.
In tblProduct2Category
prod_id cat_id
1 2
2 2
3 1
4 1
1 3
3 3
2 4
4 4
Getting the Data
Now you might look at this and wonder how you would get the data out, but it really is simple, once you are comfortable with SQL JOINs.
My first bit of advise is to start simple. What do you want? Say we want all products from the 'Large' category. According to our joint table, that cat_id would be 1.
The query would start by selecting the category that we want, since that is the top level of data we are asking for. Using aliases helps keep the SQL readable.
SELECT c.cat_id, c.cat_name
FROM tblCategory AS c
WHERE c.cat_id = 1
This gives us the category information, but what about products? Let's expand on that query, joining in the tblProduct2Category table based on the cat_id. This will return all the rows from tblProduct2Category in which the tblProduct2Category cat_id is the same as the tblCategory cat_id, which includes the prod_id for the products in each category. We narrow our returned results down with the WHERE clause that specifies ONLY from category with the cat_id of 1.
SELECT c.cat_id, c.cat_name
FROM tblCategory AS c
LEFT JOIN tblProduct2Category AS p2c
ON p2c.cat_id = c.cat_id
WHERE c.cat_id = 1
But we still don't have all the product information, just the prod_id from the join table tblProduct2Category. Well, whats the problem? no we know the product id of all the products in the category we selected. Let's expand the query to it's final state.
SELECT c.cat_id, c.cat_name, p.prod_id, p.prod_name
FROM tblCategory AS c
LEFT JOIN tblProduct2Category AS p2c
ON p2c.cat_id = c.cat_id
LEFT JOIN tblProducts AS p
ON p.prod_id = p2c.prod_id
WHERE c.cat_id = 1
The result should look something like this, and should give you enough information about your category and product to display on the page. This simplified example shows just names and id. You'll want to include everything you need about your categories and products.
cat_id | cat_name | prod_id | prod_name
1 Large 3 Large Blue Widget
1 Large 4 Large Red Widget
Depending on your database, you might need to tweak the SQL a bit. I know that in MS Access, you will need to use parentheses with more than one join.
Some applications may desire that a product be in a main category first, and then secondary categories instead of just in multiple categories. What if a product is searched for completely unrelated to a category, and you load the category page? How do you determine which category the product belongs to if you have multiple choices? I'll leave this up to you to think about, there are several ways to do it.
____________
