sql - sql如何建模实体的自定义属性?

  显示原文与译文双语对照的内容
69 0

假设我们有一个应用程序,它应该能够存储各种产品。每个产品至少有一个 ID 和一个 Name,但是所有其他属性都可以由用户自己定义。

  • 他可以创建一个支持 attributes Ipods Ipods和 generation的Ipods Ipods Ipods
  • 他可以用属性大小和颜色创建一个 productgroup TShirts
  • 我们需要存储产品的定义和具体产品本身。
  • 我们希望确保通过产品属性来聚合( GROUP BY ) 是很容易的。比如 为每一代ipod选择容量总和
  • 解决方案必须不需要模式更改( 由于来自 Bill Karwin的输入而增加的需求- 请参见他的回答) !

你将如何根据上述需求建模你的模式?

注: 要求 4.很重要 !

感谢大家贡献和讨论这个方法。我已经经看过过去这个问题的一些解决方案,但是它们中没有一个对我很容易

时间:原作者:0个回答

77 5

分组不会很容易,因为你将在"颜色"上使用什么聚合运算符?请注意,在案例 2中不可能使用你的需求 4.

在任何情况下,聚合仅因数据类型的变化而困难,并且可以以通过更加复杂的方式来解决。

这是典型的EAV模型,它在精心设计的数据库中有一个地方。为了使它的更加复杂,我已经经看到这些值存储在类型安全表中,而不是单独的of列。

替代值:

EntityID int
,AttributeID int
,Value varchar(255)

你有多个表:

EntityID int
,AttributeID int
,ValueMoney money
EntityID int
,AttributeID int
,ValueInt int
etc.

然后为每一代获得iPod容量:

SELECT vG.ValueVarChar AS Generation, SUM(vC.ValueDecimal) AS TotalCapacity
FROM Products AS p
INNER JOIN Attributes AS aG
 ON aG.AttributeName = 'generation'
INNER JOIN ValueVarChar AS vG
 ON vG.EntityID = p.ProductID
 AND vG.AttributeID = aG.AttributeID
INNER JOIN Attributes AS aC
 ON aC.AttributeName = 'capacity'
INNER JOIN ValueDecimal AS vC
 ON vC.EntityID = p.ProductID
 AND vC.AttributeID = aC.AttributeID
GROUP BY vG.ValueVarChar
原作者:
100 4

我建议使用具体表继承或者类表继承插件设计。这两个设计都满足你的全部标准。

在具体表继承中:

  • ipod以 IDNameCapacityGeneration 存储在表 product_ipods 中。
  • 使用 IDNameSizeColor 将Tshirts存储在表 product_tshirts 中。
  • 具体产品类型的定义位于 product_ipodsproduct_tshirts的元数据( 表定义) 中。
  • SELECT SUM(Capacity) FROM product_ipods GROUP BY Generation;

在类表继承中:

通用产品属性存储在表 Products 中,列 IDName

ipod以 product_id ( 。Products.ID的外键) 。CapacityGeneration 存储在表 product_ipods 中。

  • Tshirts存储在表 product_tshirts 中,带有 product_id ( 。Products.ID的外键) 。SizeColor
  • 具体产品类型的定义位于 Productsproduct_ipodsproduct_tshirts的元数据( 表定义) 中。
  • SELECT SUM(Capacity) FROM product_ipods GROUP BY Generation;

请参见我对"产品表,多种产品,每个产品有许多参数。"的回答,在这里我描述了一些解决你描述的问题类型的解决方案。我还详细介绍了为什么是一个坏的设计。

来自 @dcolumbus:的评论

使用 CTI,product_ipods的每一行都是它的自身价格的变化?

如果每种产品都有价格,我希望价格列出现在 Products 表中。对于 CTI,产品类型表通常只包含用于该产品类型的属性列。所有产品类型通用的属性都在父表中获取列。

另外,在存储订单行项目时,是否将product_ipods中的行存储为行项?

在行项目表中,存储产品 In,它应该是 Products 表和 product_ipods 表中的相同值。

来自 @dcolumbus:的评论

这对我来说太多余了。在这种情况下,我看不到子表的点。但是,即使子表有意义,连接的ID 是什么?

子表的要点是存储所有其他产品类型不需要的列。

连接id可以是自动递增号。子类型表不需要自动增加自己的id,因为它可以使用超级表生成的值。

CREATE TABLE products (
 product_id INT AUTO_INCREMENT PRIMARY KEY,
 sku VARCHAR(30) NOT NULL,
 name VARCHAR(100) NOT NULL,
 price NUMERIC(9,2) NOT NULL
);
CREATE TABLE product_ipods (
 product_id INT PRIMARY KEY,
 size TINYINT DEFAULT 16,
 color VARCHAR(10) DEFAULT 'silver',
 FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO products (sku, name, price) VALUES ('IPODS1C1', 'iPod Touch', 229.00);
INSERT INTO product_ipods VALUES (LAST_INSERT_ID(), 16, 'silver');
INSERT INTO products (sku, name, price) VALUES ('IPODS1C2', 'iPod Touch', 229.00);
INSERT INTO product_ipods VALUES (LAST_INSERT_ID(), 16, 'black');
INSERT INTO products (sku, name, price) VALUES ('IPODS1C3', 'iPod Touch', 229.00);
INSERT INTO product_ipods VALUES (LAST_INSERT_ID(), 16, 'red');
INSERT INTO products (sku, name, price) VALUES ('IPODS2C1', 'iPod Touch', 299.00);
INSERT INTO product_ipods VALUES (LAST_INSERT_ID(), 32, 'silver');
INSERT INTO products (sku, name, price) VALUES ('IPODS2C2', 'iPod Touch', 299.00);
INSERT INTO product_ipods VALUES (LAST_INSERT_ID(), 32, 'silver');
INSERT INTO products (sku, name, price) VALUES ('IPODS2C3', 'iPod Touch', 299.00);
INSERT INTO product_ipods VALUES (LAST_INSERT_ID(), 32, 'red');
原作者:
132 1

创建新产品表,并在用户执行操作时添加/删除列来更改表。使用模式了解每个产品具有哪些属性。这满足了你的全部需求。

还需要一个表来存储它的他表名称或者使用可以以针对表查询sysobjects的事件进行前缀:

select [name] from sysobjects where [name] like 'product_%' AND xtype='U'
原作者:
...