PHP eCommerce categories structure explained

Question

I am developing a simple online shop using PHP and mysql.

I need to understand the relation of categories to the products!

I have created a Table in mysql database called "categories" and 2 columns in that table called "cat_id" and "cat_name".

I have also created a Table that holds the products and its called "products" and two columns called "product_id" and "product_name".

now I need to know how I can link these two tables together?

could someone please clarify this for me please?

you would need to have a cat_id in your product table as well. — user1336827, Dec 27 '13 at 21:26
check my answer [here](http://stackoverflow.com/questions/20323748/database-design-items-in-category-sub-category-theme/20324888#20324888) I believe it covers what you need — mamdouh alramadan, Dec 27 '13 at 21:27
Depends if a product can be inside only one or more categories. In the first case (only one) add a foreign key into products which is a reference to categories (cat_id). In the other case you need a relation table with one product_id and cat_id on each line. — Jörg Mäder, Dec 27 '13 at 21:27
possible duplicate of [How to join two tables mysql?](http://stackoverflow.com/questions/3536283/how-to-join-two-tables-mysql) — Michael Hampton, Dec 28 '13 at 02:59

score 1 · Answer 1 · answered Dec 27 '13 at 21:28

If products can have many categories and categories can be assigned to many products. It's a ManyToMany relationship and therefore requires a link table.

I would create a table product_category with columns product_id and category_id

If a product can only have one category, you could add it as a foreign key to the product table as a column such as category_id.

score 0 · Answer 2 · answered Dec 27 '13 at 21:31

It depends upon your concept. Can products reside in more than one category at a time? If so, you'll need a link table. Otherwise, you can simply add a column to your products table, which contains the ID of the category in which it resides.

The former is more complicated since it involves a many-to-many relationship, which is something you should be avoiding as part of your normalization. You'd need to have a link table that looks something like this:

+------------+------------+----------+----------+
|   cat_id   |   prod_id  |  active  |   order  |
+------------+------------+----------+----------+
|     1      |     1      |    1     |     0    |
|     1      |     2      |    0     |     1    |
|     2      |     2      |    1     |     0    |
+------------+------------+----------+----------+

As you can see, we have four columns (you might only need two):

cat_id is the ID of the category (from categories)
prod_id is the ID of the product (from products)
active is a boolean value which indicates whether the product is currently active in this category
order defines the sort order of this product for the given category

In the sample data above, product ID 1 and 2 both appear in Category 1. However, Product ID 2 is not active. Product ID 2 also appears as the only product in Category 2.

PHP eCommerce categories structure explained

2 Answers2