我正在做一个电子商务项目,并对存储产品的数据库设计感到困惑.我推测有三种方法可以制作数据库：
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.
将所有产品组合在一个表中,并允许某些值为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.
将类似的列字段组合在一个名为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?)
我非常感谢启蒙,谢谢
解决方法
我假设一个产品可以属于许多类别,一个类别(显然)有很多产品.这种关系称为多对多关系.
在这种情况下,您将拥有三个表：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,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;
显然,这些是最简单的表格模式.您需要将其他列添加到类别和产品表中 – 我只包含与关系相关的列.
编辑：我还在类别表中添加了一个parent_id列用于嵌套类别.创建一个单独的sub_categories表通常是一个坏主意 – 如果你想让一个子类别成为顶级类别,会发生什么？或相反亦然？你因缺少一个更好的短语而受到骚扰.
