mysql - 多个一对多关系的正确架构？

Question

我正在尝试弄清楚如何为数据库中的一行正确创建多个一对多关系。

假设我有一个产品表。

CREATE TABLE `product` (
`productID` int(11) NOT NULL AUTO_INCREMENT,
`productName` varchar(45) NOT NULL,
PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

产品可以具有一对一的类别关系或一对多的关系。

CREATE TABLE `category` (
`categoryID` int(11) NOT NULL AUTO_INCREMENT,
`categoryName` varchar(45) NOT NULL,
PRIMARY KEY (`categoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我创建了一个带有外键的查找表，但我认为它不会起作用，因为可能存在与各种不同类别组关联的产品。

CREATE TABLE `category_to_product` (
`categoryID` int(11) NOT NULL,
`productID` int(11) NOT NULL,
KEY `categoryID` (`categoryID`),
KEY `productID` (`productID`),
CONSTRAINT `category_to_product_ibfk_2` FOREIGN KEY (`productID`) 
REFERENCES `product` (`productID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `category_to_product_ibfk_1` FOREIGN KEY (`categoryID`) 
REFERENCES `category` (`categoryID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

编辑

<小时/>

我的数据库中的查询是基于级别的，每个更高级别的查询都依赖于前一个级别。级别 1 将是基本级别查询，并且包含最一般的信息，假设此类别是电子产品。

2 级查询将是一个稍微更精细的查询，并且将是电子产品的子类别，例如电子产品->电视。 将其视为面包屑。这本身似乎很难做到，但在这种情况下，产品需要能够与多个不同的类别组相关联，其中可以包含同一级别或不同级别。我希望这是有道理的。

<小时/>

我将如何为这种类型的关系构建数据库？最初，我将类别级别分隔在查找表中的不同列下，效果很好，但有太多 NULL 值。

Answer 1

您的方法非常通用。产品在模型中没有任何固定属性(名称除外)，但具有一组可选的关联属性。所以可能会有颜色和 Material 未知的裤子。或者是棉质的电脑和锡质的裤子。一个有二十种颜色的 Xbox 镜子和一辆既轻又重的自行车。

您甚至看不到红色和绿色是颜色，塑料和金属是 Material 。

所以你必须从头开始:你真正需要什么？不是有“电脑”、“洗衣机”这样的产品组吗？然后，每个组可以具有强制性属性，例如计算机的 CPU 和洗衣机的能源等级，以及可选属性，例如颜色，可以给出但不被认为是必需的，或者像计算机的屏幕，它可能具有或不是。然后，每个产品都属于一个产品组，并且您会知道可以或必须为其输入哪些属性(以及您可以通过哪些属性过滤某些产品)。

然后想想你有什么属性:

• CPU = 英特尔 i5| Intel i7|AMD FX = 一个 cpu 的可能值列表
• 颜色=白色|黑色| ...=也是一个列表，但每个产品可以有多种颜色
• 鼠标 = 是 | no = bool 标志
• 型号 = ____ = 自由文本属性

然后为这些考虑适当的表格设计。

如果您甚至想要分层属性，例如颜色 = 发光亮橙色 => 亮橙色 => 橙色(因此可以搜索所有橙色产品或仅搜索浅橙色产品，甚至仅搜索发光亮橙色产品)，那么您需要一些结构为此，也是。但是，难道人们不应该也寻找发光的橙色产品，或者任何浅色产品吗？那么这将不再是一个层次结构树。

正如您所见，这一切都可能变得相当复杂。想想你真正需要什么。根据需要使其复杂化，但也仅限于此。