设计电子商务数据库 - MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14625701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Designing an E-Commerce Database - MySQL
提问by a7omiton
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:
我正在做一个电子商务项目，对存储产品的数据库设计感到困惑。我推测可以通过 3 种方式创建数据库：
1. There can be separate tables for each product category.
1. 每个产品类别可以有单独的表格。
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
2.将所有产品组合在一张表中，并允许某些值为空
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.
3. 将相似的列字段组合在一个名为 products 的表中，并为特定数据提供单独的表。
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
我真的很感激启蒙，谢谢
采纳答案by Martin Bean
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:
在这种情况下，你将有三个表：categories，products，和categories_products。前两个表是不言自明的。第三个表用两个外键存储两者之间的关系。这些表看起来像这样：
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 categoriesand productstable—I've only included the columns relavant to the relation.
显然，这些是最简单的表模式。您需要将您的附加列添加到categories和products桌子，从来只包括列初步认识到的关系。
EDIT: I also added a parent_idcolumn to the categoriestable for nesting categories. It's generally a bad idea to create a separate sub_categoriestable—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.
编辑：我还在表格中添加了一parent_id列categories用于嵌套类别。创建单独的sub_categories表通常是个坏主意——如果您想将子类别设为顶级类别，会发生什么情况？或相反亦然？你因为想要一个更好的短语而烦恼。
回答by AgeDeO
I think it depends on the products to use method 1 or 2. I would never use method 3.
我认为这取决于使用方法1或2的产品。我永远不会使用方法3。
If your products are completly different like Books, Perfumes and Clothes I would use method 1
如果您的产品完全不同，例如书籍、香水和衣服，我会使用方法 1
One sidenote: Why use 2 tables for your catagories? Use one table and add a column Parent_ID that way you can use unlimited subcatagories in the future.
旁注：为什么要为您的类别使用 2 个表格？使用一个表并添加一列 Parent_ID，这样您将来就可以使用无限的子类别。
for example:
例如：
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 (those are the main categories).
- Sci-Fi and Comedy is a subcategory of Books (ID 1).
- Jeans, Sweater and Underwear is a subcategory of clothes (ID 2).
- Long Sleeve is a subcategory of Sweater (ID 7).
- Roses is a subcategory of perfumes (ID 3).
- Books, Clothes en Perfumes 没有父类（这些是主要类别）。
- 科幻和喜剧是书籍 (ID 1) 的一个子类别。
- 牛仔裤、毛衣和内衣是衣服的一个子类别（ID 2）。
- 长袖是毛衣 (ID 7) 的一个子类别。
- 玫瑰是香水的一个子类别（ID 3）。
