这是在这种特定情况下确保数据完整性的好解决方案吗？
我正在开发一个跟踪某些商品价格的应用程序。
每个价格都参考了某个商品、销售该商品的企业以及该商品的销售地点。现在，通常情况下，这会很好：
CREATE TABLE `price` (
`priceId` INT UNSIGNED NOT NULL AUTO_INCREMENT, -- PK
`businessId` INT UNSIGNED NOT NULL,
`itemId` INT UNSIGNED NOT NULL,
`locationId` INT UNSIGNED NOT NULL,
`figure` DECIMAL(19,2) UNSIGNED NOT NULL,
-- ...
)
但我有以下问题：
应用程序逻辑是这样的，即一个地点的一个企业的一件商品可以有多个价格（此时，原因并不重要），并且其中之一价格可以是官方价格 - 一件商品不必有官方价格，但如果有的话，也只能有一个。
问题是；如何对此进行建模以确保数据完整性？
我最初的想法是创建一个附加表：
CREATE TABLE `official_price` (
`priceId` INT UNSIGNED NOT NULL -- PK + FK (references price.priceId),
-- ...
)
该表将保存官方价格的 PriceId:s，并且 PK/UNIQUE 约束将处理“一或无”约束。
这似乎是一个可行的解决方案，但我仍然想知道是否有更好的方法来处理这种情况？
I'm working on an application which tracks prices for certain items.
Each price has a reference to an item, a business that sells that item, and the location the item is being sold at. Now, normally, this would do just fine:
CREATE TABLE `price` (
`priceId` INT UNSIGNED NOT NULL AUTO_INCREMENT, -- PK
`businessId` INT UNSIGNED NOT NULL,
`itemId` INT UNSIGNED NOT NULL,
`locationId` INT UNSIGNED NOT NULL,
`figure` DECIMAL(19,2) UNSIGNED NOT NULL,
-- ...
)
But I have the following problem:
The application logic is such that one item at one business at one location can have multiple prices (at this point it's not really important why), and one of those prices can be an official price - an item doesn't have to have an official price, but if it does, there can be only one.
The question is; how to model this to ensure data integrity?
My initial idea was to create an additional table:
CREATE TABLE `official_price` (
`priceId` INT UNSIGNED NOT NULL -- PK + FK (references price.priceId),
-- ...
)
This table would hold priceId:s for prices that are official, and the PK/UNIQUE constraint would take care of the 'one-or-none' constraint.
This seems like a workable solution, but I'm still wondering if there's a better way to handle this situation?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以使用这个肮脏的黑客：
is_official添加到price表中，因为其中可能存在值 nullpriceId + is_officialcode>1设为is_officialnullYou can use this dirty hack:
is_officialtopricetable, null as a value is possible in itpriceId + is_official1tois_officialnull您可以使
price表仅包含官方价格（数字可能为空），对 (businessId,itemId, < code>locationId），并添加另一个引用priceId的辅助价格表。You could make the
pricetable hold only official prices (with the figure possibly null), put a unique constraint on (businessId,itemId,locationId), and add another table of auxiliary prices referencingpriceId.