Building an E-Commerce Site Part 1: Building a Product Catalog - Step 2: Creating the Product Catalog
( Page 5 of 8 )

We have the user account created, now let's create the product catalog tables. Let's say that our product catalog looks like this (yes we have a very small catalog :)

[Top]
|
+--[Fruits]
|  |
|  +--[Apples]
|  |  |
|  |  +-- Granny Smith
|  |  +-- Red Delicious
|  |  +-- Rome Beauty
|  |  +-- Apple Chips
|  |
|  +--[Citrus]
|  |  |
|  |  +-- Orange
|  |  +-- Lemons
|  |  +-- Grapefruit
|  |
|  +--[Berries]
|  |  |
|  |  +-- Blueberries
|  |  +-- Strawberries
|  |  +-- Raspberries
|  |
|  +-- Bananas
|
+--[Vegetables]
|  |
|  +-- [Lettuce]
|  |   |
|  |   +-- Butterhead
|  |   +-- Cutting
|  |   +-- Crisp
|  |
|  +-- [Mushrooms]
|  |   |
|  |   +-- Truffles
|  |   +-- Shiitake
|  |   +-- Enoki
|  |
|  +-- [Potatoes]
|  |   |
|  |   +-- Sweet Potatoes
|  |   +-- White Rose
|  |   +-- Russet
|  |   +-- Potatoe Chips
|  |
|  +-- Cucumbers
|
+--[Snacks]
   |
   +-- [Chips]
   |   |
   |   +-- Potato Chips
   |   +-- Apple Chips
   |   +-- Corn Chips
   |
   +-- [Icecream]
   |   |

   |   +-- Vanilla
   |   +-- Chocolate
   |
   +-- Popcorn

What we want is an easy way to manage this catalog in our database. Product categories are arranged in a tree. There is one root category called Top, under which all other categories will sit. Next, go the subcategories under those, etc. For our products, we want to be able to put a product into one or many categories to make it easy for the customer to find.

So in summary, we have what looks like this:

Reading this clockwise, we say that:

• A category contains zero or more (0|M) products
  eg. We have 3 products in the [Apples] category
  
• A product belongs to one or more (1|M) categories
  eg. Potato chips are in the [Potatoes] and [Chips] category
  
• A category contains one or more (1|M) categories
  eg. We have 3 categories under the [Fruits] category
  
• A category belongs to one and only one (1|1) category
  eg. The [Chips] category can only belong under snacks

NOTE: The 0|M, 1|M are just my own notations to represent the optionality and cardinality, if that doesn't make sense to you don't worry about it too much, you can look these terms up in a database book later.

NOTE: We can make categories a many-to-many relation upon itself, such that a category can have multiple sub-categories and belong to multiple parent-categories, but it's a too much to explain for this howto :) so we won't do it like that.

In terms of the database entity relations, we have 2:

1. the categories entity is a one-to-many relation upon itself
2. the products entity is a many-to-many relation with the categories entity

Now based on the relations that we have defined, we will need three tables to drive our product catalog:

1. categories

   This table will hold the names of our categories, as well as telling us where this category sits in our catalog.

   	Fieldname	Type	Description
   PK	id	int auto_increment	An internal identifier (ID) for this category
   IDX	parent_id	int	The ID of the parent category
   IDX	name	varchar(25)	A name for this category
   	description	varchar(255)	A description for this category

   Since the ID will only be used internally, we will be using a MySQL autonumber to generate them. Also, since the ID field will be used to identify records it will be the primary key of this table. We want to index the parent_id and name fields because we may want to perform searches or look up categories by them.

2. products

   This table will hold the information about our products, for example the name, description and price, etc. In real life you will have a lot more fields to hold other information about the product, but for the purposes of this guide, this is all you need.

   	Fieldname	Type	Description
   PK	id	int auto_increment	An internal identifier (ID) for this product
   IDX	name	varchar(25)	A name for this product
   	description	varchar(255)	A description for this product
   	price	float(5,2)	The price of this product

3. products_categories

Since we have a many-to-many relation between products and categories, we need a table to keep track of these relations. This table simply keeps pairs of product IDs and category IDs that relate to each other.

	Fieldname	Type	Description
PK	product_id	int	The ID for the product
PK	category_id	int	The ID for the category

Okay, so we know what tables we want, now let's write the SQL CREATE TABLE queries to create them in MySQL. Run the following commands (lines in /* */ are comments and will be ignored by MySQL):

mysql> /* create the categories table */
    -> CREATE TABLE categories (
    -> id int auto_increment not null,
    -> parent_id int not null,
    -> name varchar(25) not null,
    -> description varchar(255) not null,
    -> PRIMARY KEY (id),
    -> INDEX parent_id (parent_id),
    -> INDEX name (name)
    -> );




mysql> /* create the products table */
    -> CREATE TABLE products (
    -> id int auto_increment not null,
    -> name varchar(25) not null,
    -> description varchar(255) not null,
    -> price float(5,2) not null,
    -> PRIMARY KEY (id),
    -> INDEX name (name)
    -> );




mysql>/* create the products_categories table */
    ->CREATE TABLE
    ->products_categories (
    ->product_id int not null,
    ->category_id int not null,
    ->PRIMARY KEY (product_id, category_id)
    ->);

Let's take a look at what we've just created, let's list the tables with the following command:

mysql> SHOW TABLES;
+---------------------+
| Tables in mymarket  |
+---------------------+
| categories          |
| products_categories |
| products            |
+---------------------+

Now let's look each table with the DESCRIBE command:

mysql> DESCRIBE categories;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      |      | PRI | 0       | auto_increment |
| parent_id   | int(11)      |      | MUL | 0       |                |
| name        | varchar(25)  |      | MUL |         |                |
| description | varchar(255) |      |     |         |                |
+-------------+--------------+------+-----+---------+----------------+


mysql>DESCRIBE products;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      |      | PRI | 0       | auto_increment |
| name        | varchar(25)  |      | MUL |         |                |
| description | varchar(255) |      |     |         |                |
| price       | float(5,2)   |      |     | 0.00    |                |
+-------------+--------------+------+-----+---------+----------------+


mysql>DESCRIBE products_categories;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| product_id  | int(11) |      | PRI | 0       |       |
| category_id | int(11) |      | PRI | 0       |       |
+-------------+---------+------+-----+---------+-------+

Now that we have all our tables, let's move on and add some data.

>>> More PHP Articles          >>> More By Ying Zhang