我正在做一个电子商务项目,对于存储产品的数据库设计感到困惑。我思考过可以有以下三种方式来创建数据库:
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. 将所有产品放在同一张表中,并允许某些值为空。
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. 在名为 "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?)
我非常希望获得启示,谢谢
id
、product_id
(外键)、name
和value
。您可以针对每个产品拥有多行记录(一对多)。 - Martin Bean