一份订单中多种不同产品的表格设计
如果我有一个销售苹果和显示器的在线购物网站，并且它们存储在不同的表中，因为苹果的区别属性是颜色，而显示器的区别属性是分辨率，我如何将它们添加到发票表中，同时仍然保留引用完整性并且不合并这些表？
Invoices(InvoiceId)
|
InvoiceItems(ItemId, ProductId)
|
Products(ProductId)
| |
Apples(AppleId, ProductId, Colour) Monitors(MonitorId, ProductId, Resolution)
If I were to have an online shopping website that sold apples and monitors and these were stored in different tables because the distinguishing property of apples is colour and that of monitors is resolution how would I add these both to an invoice table whilst still retaining referential integrity and not unioning these tables?
Invoices(InvoiceId)
|
InvoiceItems(ItemId, ProductId)
|
Products(ProductId)
| |
Apples(AppleId, ProductId, Colour) Monitors(MonitorId, ProductId, Resolution)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
首先，我会将它们存储在一个产品表中，而不是两个不同的表中。
其次，（除非每张发票仅针对一种产品）我不会将它们添加到单个发票表中 - 相反，我会设置一个 Invoice_Products 表，以在表之间进行链接。
我建议您研究数据库规范化。
In the first place, I would store them in a single Products table, not in two different tables.
In the second place, (unless each invoice was for only one product) I would not add them to a single invoice table - instead, I would set up an Invoice_Products table, to link between the tables.
I suggest you look into Database Normalisation.
您的数据模型的一个问题是您需要一个参考方案来识别产品？也许是SKU？
然后通过分配 SKU 将每个苹果识别为产品。对于显示器也是如此。然后使用发票项目中的 SKU。像这样的东西：
产品{sku}
密钥 {sku}；
发票项 {发票 ID, sku}
密钥 {invoice_id, sku} ;
苹果 {颜色、sku}
键{颜色}
密钥 {sku}；
监控{尺寸、sku}
键{尺寸}
密钥 {sku}；
具有适当的约束...特别是 apple {sku} 和监视器 {sku} == 产品 {sku} 的并集。
A question for your data model is You need a reference scheme will you use to identify products? Maybe SKU ?
Then identify each apple as a product by assigning an SKU. Likewise for monitors. Then use the SKU in the invoice item. Something like this:
product {sku}
key {sku};
invoice_item {invoice_id, sku}
key {invoice_id, sku} ;
apple {color, sku}
key {color}
key {sku};
monitor {size, sku}
key {size}
key {sku};
with appropriate constrains... in particular, the union of apple {sku} and monitor {sku} == product {sku}.
那么 Invoice 表有一个 ProductID FK，并且 ProductID 可以是 AppleID（PK 颜色）或 MonitorID（PK 分辨率）？
如果是这样，您可以引入具有 0=apple、1=monitor 等值的 ProductTypeID，或者如果只有 2 种产品类型，则可以引入 isProductTypeApple 布尔值，并将其包含在 ProductID 表 PK 中。
您还需要在 Apple 表和 Monitor 表 PK 中包含 ProductTypeID 字段。
So Invoice table has a ProductID FK, and a ProductID can be either an AppleID (PK color) or MonitorID (PK resolution)?
If so, you can introduce a ProductTypeID with values like 0=apple, 1=monitor, or a isProductTypeApple boolean if there's only ever going to be 2 product types, and include that in the ProductID table PK.
You also need to include the ProductTypeID field in the Apple table and Monitor table PK.
我喜欢这些名称-值表...重新设计可能更容易，因此它先是“产品”，然后是“产品详细信息”...产品详细信息包含产品 ID、详细信息类型，然后是值。这将允许您将苹果和显示器放在同一个表中，无论标识属性如何（并将其保持打开状态以供稍后添加其他产品）。
可以在发票表中采取类似的方法...有一个“product_type”列，告诉您要查看哪个表（apple 或显示器），然后是一个“product_id”，它引用 apple/monitor 表中的任何 ID 列。查询这样的设置有点困难，可能会迫使您使用动态 sql...如果您无法控制上面的重新设计（以及此处发布的其他答案请参考），我只会采用此路线
第一个解决方案是我认为优先...将此数据库的设计更改为与产品的名称值对，这样您就可以省去稍后编写查询的麻烦。
I like name-value tables for these...It might be easier to redesign so it goes 'Product' and then 'product details'...product details holds the product id, the detail type and then the value. This would allow you to hold apples and monitors in the same table regardless of identifying attribute (and leave it open for other product to be added later on).
Similiar approach can be taken in the invoice table...have a 'product_type' column that tells you which table to look into (apple or monitor) and then a 'product_id' that references whatever ID column is in the apple/monitor table. Querying on a setup like this is a bit difficult and may force you to use dynamic sql...I'd only take this route if you have no control over doing the redesign above (and other answers posted here refer to)
First solution is preferential I would think...change the design on this db to the name value pair with the products and you'll save headaches writing your queries later.