Designing products catalog - database structure

To make products catalog user-friendly, let us review how to create a category-based products catalog with unlimited category nesting.

In other words, we will have to implement following features for our online catalog:

1. Categories list is stored in a separate database table CATEGORY.
2. Products list is stored in a single database table PRODUCT.
3. A single product can be placed to a certain category.
4. Category nesting is unlimited.

Let us review these issues one by one:

1. Category list is stored in the CATEGORY table with the following structure:
   
   
   
   Here categoryID is an integer auto increment primary key, which indicates a category.
   
   Other fields contain information about the category required for showing it in back end and storefront, e.g. name, description and thumbnail.
   
   products_count and products_count_admin columns contain information on how many products are there in a certain category.
   products_count indicates a number of products within the category and all its child categories - this number of products is shown in the storefront to customers to make navigation easier.
   products_count_admin shows how many products are there in the current category excluding child categories, and this information is shown in the back end.
   These fields are updated each time you add or delete products.
   
   
2. Product list is stored in the PRODUCT table with the following structure:
   
   
   
   productID is an integer auto increment primary key, which unambiguously indicates a product.
   
   Other columns contain product information, e.g. product name, stock information, enabled/disabled for sale, description.
   
   
3. Products distribution among the category tree.
   
   categoryID column in the PRODUCT table is a foreign key, which stores information to which category does the product belong.
   
   For example, if "mp3-players" category's categoryID is 536, and "Apple iPod" product record's categoryID equals 536, this means that "Apple iPod" appears in the "mp3 players" category of your online store.
   
    In case categoryID equals zero (0), it assumes that the product does not belong to any category.
   
   
4. Category nesting.
   
   To implement unlimited category nesting we add parent column to the CATEGORY table. This column stores categoryID of a certain category's parent category.
   Category's parent fields equaling zero (0) means that this is a root category.
   
   For example, there are three categories:
   “A” with categoryID = 1, parent = 0 (which means this is a root category)
   “B” with categoryID = 2
   “C” with categoryID = 3
   If we set “parent” value for category “B” to 1, this will make “B” subcategory (child category) of “A” (“A” will be parent of “B”). And then if we set “C” ’s parent to 2, we shall make “C” subcategory of “B”. Thus we shall get following categories tree structure: “A” -> “B” -> “C”.
   If parent value of a category is 0 (zero), it indicates that this category is root category (“A” in our example).
   
   Please take a look at the following screenshot with the sample category tree. It shows how parent values refer to parent category IDs:
   
   
   
   In the next paragraphs we will review how to show and manage such designed category tree.