同一个2表之间的一对一和一对多关系

Question

我正在设计Db，它有两个表

1. 行项目表
2. 价格表

每个行项目可以是Lot或Unit类型Lot是一组具有相同价格的行项目。因此，一个价格关系有许多行项目有一个单位行项目，每个项目都有不同的价格(所以有一个一个关系(

现在，我有两张表，它们之间有两种关系价格(单位(1比1行项目价格(批次(1到多行项目

我们开发了一种类似的设计

LineItemTable(Id,PriceIdfk...)和Price(Id,Type,CostPrice)

这里的类型表明是单位还是批次

但它并没有完全限制上述关系

例如：行项目表在下方有3行

(1, LotA, 31)
(2, LotA, 31)
(3, Unit, 32)

价格表将有低于2个记录

(31,Lot,100$)
(32,Unit, 13$)

想象一下，将行项目的PriceIdFk从31更改为32，这样属于同一批次的两个项目将具有不同的价格，这是不应该发生的。

我尝试创建单独的主表作为LineItemType(Id,Type)其中它将只有两个记录(1,Unit)和(2,Lot)并且在具有一个以上列LineItemTypeFk的PriceTable中引用该Id，但这没有太大区别，因为属于同一批次的两个项目可以具有不同的价格。

我想我以错误的方式识别了实体，但我找不到任何适合这种情况的解决方案。

Answer 1

-- Item ITM is priced at PRI dollars.
--
item {ITM, PRI}
PK {ITM}

-- All items in group GRP
-- are priced at PRG dollars.
--
_group {GRP, PRG}
PK {GRP}

-- Item ITM is member of group GRP.
--
item_group {ITM, GRP}
PK {ITM}
FK1 {ITM} REFERENCES  item  {ITM}
FK2 {GRP} REFERENCES _group {GRP}

如果项目是该组的成员，则适用团体价格；否则项目价格适用。

-- Item ITM is priced at PRICE dollars.
--
CREATE VIEW item_price
AS
SELECT i.ITM
, coalesce (g.PRG, i.PRI) AS PRICE
FROM       item       AS i
LEFT JOIN  item_group AS x ON x.ITM = i.ITM
LEFT JOIN _group      AS g ON g.GRP = x.GRP ;

票据

All attributes (columns) NOT NULL
PK = Primary Key
FK = Foreign Key

---

选项2

根据以下意见，将GRP保留在item中。

-- All items in group GRP
-- are priced at group-price of PRG dollars.
--
_group {GRP, PRG}
PK {GRP}
-- Sample data
--
(GRP, PRG)
----------
(G00, -1)  -- special group, individual item price applies
(G01, 120)
(G02, 150)

-- Item ITM, from group GRP, is priced at 
-- individual item price of PRI dollars.
--
item {ITM, GRP, PRI}
PK {ITM}

如果某个项目是G00组的成员，则应用项目价格，否则应用组价格。

-- Item ITM is priced at PRICE dollars.
--
CREATE VIEW item_price
AS
SELECT i.ITM
, CASE WHEN i.GRP = 'G00' THEN i.PRI
ELSE g.PRG
END  AS PRICE
FROM  item  AS i
JOIN _group AS g ON g.GRP = i.GRP ;