Having a database structure that allows a product to be associated with more than one category is a very common scenario in any eCommerce website. However, after working on a couple of truely awful bespoke solutions from other developers recently, whose methods to store and retreive such data were so convoluted, have inspired me to write this article.
Story such data need to not be overly complicated. The following, simple, table structure is required (in a real ecommerce system you would definately have additional fields – these have been omitted here for thae sake of the example):
The products_category table is a simple linking table that allows a many-to-many relationship between the product and category table. It contains to two primary keys to ensure every combination of product and category is unique. for example, this table will contain many unique number pairs and a row may be 1,4 or product_id 1 and category_id 4. The files to create and populate this table structure with sample data can be found here.
Now it is simplay a case of running a series of MySQL statements (I’d advise converting them to stored procedures for more security and better application seperation) to retreieve the appropriate data. For example:
Products Within a Certain Category (E.g. category_id 1):
SELECT p.product_id, p.name FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id WHERE pc.category_id = '1';
Count Products Within a Certain Category (E.g. category_id 1):
SELECT COUNT(p.product_id) As myCount FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id WHERE pc.category_id = '1';
…and that’s it. Extremely simple, can be expanded to any eCommerce system and not convoluted at all 🙂