Designing an E-Commerce Database - MySQL

Question

I'm doing an e-commerce project and am confused about the database design for storing products. There are 3 ways I've speculated the database can be made:

1. There can be separate tables for each product category.

Table: Categories
------------------
cat_ID
cat_name

Table: Sub_Categories
---------------------
sub_cat_ID
categories_cat_ID
sub_cat_name

Table: Books
-------------
book_ID
sub_categories_sub_cat_ID
book_title
book_author
book_ISBN
book_price
etc

Table: Clothes
---------------
clothes_ID
sub_categories_sub_cat_ID
clothes_name
clothes_color
clothes_size
clothes_description
clothes_price
etc

Table: Perfumes
----------------
perfumes_ID
sub_categories_sub_cat_ID
perfume_name
perfume_size
perfume_weight
perfume_description
perfume_price
etc

2. Group all products together in one table and allow some values to be null

Table: Categories
------------------
cat_ID
cat_name

Table: Sub_Categories
---------------------
sub_cat_ID
categories_cat_ID
sub_cat_name

Table: Products
---------------
product_ID
sub_categories_sub_cat_ID
title
description
price
author (can be null for everything except books)
size
weight (can be null for everything except perfumes)
ISBN (can be null for everything except books)
color (can be null for everything except clothes)
etc

3. Group similar column fields together in a table called products, and provide separate tables for specific data.

Table: Categories
------------------
cat_ID
cat_name

Table: Sub_Categories
---------------------
sub_cat_ID
categories_cat_ID
sub_cat_name

Table: Products
----------------
product_ID
sub_categories_sub_cat_ID
title
description
price

Table: Books
-------------
products_product_id
sub_categories_sub_cat_ID
author
publisher
ISBN

Table: Perfumes
----------------
products_product_id
sub_categories_sub_cat_ID
size
weight

Table: Clothes
--------------
products_product_id
sub_categories_sub_cat_ID
color
size (this can be a one to many relationship to cater to multiple sizes of one product?)

I would really appreciate enlightenment, thank you

Answer 1

I assume a product can belong to many categories, and a category (obviously) has many products in it. This relationship is called a many-to-many relation.

In this instance, you would have three tables: categories, products, and categories_products. The first two tables are self-explanatory. The third table stores a relation between the two with two foreign keys. The tables would look like this:

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `categories_products` (
  `category_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  KEY `category_id` (`category_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `categories_products`
  ADD CONSTRAINT `categories_products_ibfk_2`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `categories_products_ibfk_1`
    FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

Obviously these are the table schemas at their simplest. You will need to add your additional columns to the categories and products table—I’ve only included the columns relavant to the relation.

EDIT: I also added a parent_id column to the categories table for nesting categories. It’s generally a bad idea to create a separate sub_categories table—what happens if you want to make a sub-category a top-level category? Or vice versa? You’re buggered for want of a better phrase.