mysql - 我可以在MySQL中创建带有动态列的表结构吗？

Question

我创建了一个库存控制数据库，其中包含两个表(实际上不止两个表，但这是与我的问题相关的两个表):库存和收据

我希望库存表中的库存与收据表中的库存之间的链接更清晰一点，如果客户每张收据只能订购一件库存，那就没问题了，因为我' d 只需在 Recipts 表中包含一个 StockID 列和一个 Quantity 列，其中 StockID 列作为 Stock 表中 ID 的 FK，但是，客户可以制作包含任意数量库存项目的收据，这将意味着我必须在收据表中包含大量列(即 StockID_1、Quantity_1、StockID_2、Quantity_2 等)

在 MySQL 中是否有解决这个问题的方法(你可以像 MySQL 中动态扩展的列集一样)，除了我目前所做的之外，即拥有一个具有以下结构的 OrderContents 列(其中不是由数据库或其他任何东西强制执行的)StockID1xQuantity、StockID2xQuantity 等等？

我会发布数据库结构的图像，但我还没有足够的声誉。我的讲师提到可以通过将数据库标准化为第四或第五范式来完成？

Answer 1

我建议有 3 个表:

股票 (StockID) + 股票特定字段

收据 (ReceiptID) + 收据特定字段。

StockReceipt(ReceiptID、StockID、数量)(可以有 StockReceiptID，或使用 StockID+ReceiptID 作为主键)

<小时/>

包含价格的解决方案可能如下所示:

股票(股票ID，价格)

价格历史记录(股票ID、价格、日期)或(DateFrom、DateTo)

收据(收据 ID、收据日期)

库存收据(收据 ID、库存 ID、数量)

这样您就可以计算过去任何收据的 TotalStockReceiptPrice 和 TotalReceiptPrice。

<小时/>

我怀疑这可能就是您正在寻找的:

股票(股票ID、股票价格)

收据(收据ID)

库存收据(收据 ID、库存 ID、数量)

SELECT r.ReceiptID, SUM(s.StockPrice * sr.Quantity) AS ReceiptPrice
FROM Receipt r
INNER JOIN StockReceipt sr ON r.ReceiptID = sr.ReceiptID
INNER JOIN Stock s ON sr.StockID = s.StockID
GROUP BY r.ReceiptID

这一切都非常规范化(同样，不知道什么规范形式 - 第三种？)。然而，只有当股票记录中的股票价格永远不会改变时，它才有效。一旦更改，您的 ReceiptPrices 将全部反射(reflect)新价格，而不是客户实际支付的价格。

如果价格可能发生变化，您需要保留价格历史表(ItemID、Price、DateTo、DateFrom)或在 StockReceipt 记录中记录 StockPrice(然后删除 JOIN 添加到上述查询中的 Stock 记录中，并使其使用 sr.StockPrice 而不是 s.StockPrice)

要执行您在下面发布的INSERT，您必须执行以下操作:

INSERT INTO StockReceipts  (ReceiptID, StockID, Quantity, TotalStockPrice) 
SELECT 1, 99, 2, s.StockPrice
FROM Stock s 
WHERE s.StockID = 99

但是，很可能发出此收据(并触发 INSERT)的任何东西都已经知道价格，因此可以插入该值。