Designing an E-Commerce Database - MySQL

chef_carol

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...

Originally asked by a7omiton on Stack Exchange (stackoverflow) (CC BY-SA 3.0).

uphilltech

Great question — answering as the UphillTech team:

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...

Full answer by Martin Bean: https://stackoverflow.stackexchange.com/a/14625864 (CC BY-SA 3.0).

Need it done for your business? UphillTech.