Designing an E-Commerce Database - MySQL

Question

I am involved in an e-commerce project and am confused by the design of a database for storing products. There are three ways to use the database:

1. There may 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 let some values ​​be zero

 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 a product and specify 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 be very grateful for the enlightenment, thanks

Answer 1

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

In this case, you will have three tables: categories , products and categories_products . The first two tables are self-explanatory. The third table stores the relationship between the two with two foreign keys. The tables 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 schema tables on their simplest. You will need to add your additional columns to the categories and products table-Ive tables, including only the columns corresponding to the relationship.

EDIT: I also added a parent_id column to the categories table for nesting categories. It's usually a bad idea to create a separate sub_categories table - what happens if you want to make a subcategory of a top-level category? Or vice versa? You were mistaken for the lack of a better phrase.

Answer 2

I think it depends on the products for using method 1 or 2. I would never use method 3.

If your products are completely different from books, perfumes and clothes, I would use method 1

One side effect: why use 2 tables for your directories? Use one table and add the Parent_ID column so that you can use unlimited subdirectories in the future.

eg:

 table: categories |id|description|parentid| |1 |books |NULL | |2 |clothes |NULL | |3 |perfumes |NULL | |4 |Sci-Fi |1 | |5 |Comedy |1 | |6 |Jeans |2 | |7 |Sweater |2 | |8 |Underwear |2 | |9 |Long sleeve|7 | |10|Roses |3 | 

• Books, clothes en Perfumes do not have a parent (these are the main categories).
• Sci-Fi and Comedy is a subcategory of books (ID 1).
• Jeans, a sweater and underwear are a subcategory of clothing (ID 2).
• Long Sleeve is a subcategory of a sweater (ID 7).
• Roses is a subcategory of perfumes (ID 3).