One-to-One and One-to-Many relation between same 2 table

Question

I am designing Db where It has 2 tables

1. Line Item table
2. Price Table

Each line item can be of type Lot or Unit Lot is kind of group of Line item having same price. So there is Many Line items to one price relation and there is Unit Line item where each of will have a different price (so there is one - one to relation)

So now, I have 2 tables with 2 kind of relation ship between them price(unit) 1 to 1 Line Item Price(lot) 1 to many Line Item

We have developed a design like below

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

here type indicate if is Unit or Lot

But it does not fully restrict relationship described above

eg: LineItem Table has 3 rows below

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

and Price table will have below 2 records

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

Imagine changing PriceIdFk on Line Item from 31 to 32, so 2 items belonging to same lot will have different prices which should not happen.

I have tried of creating separate master table as LineItemType(Id,Type) where it will have only 2 records (1,Unit) and (2,Lot) and have this Id referred in PriceTable having one more column LineItemTypeFk, but this does not make much difference as 2 items belonging to same lot can have different price.

I guess I am identifying the entities in wrong way but I could not find any solution where this case fits.

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}

If an item is member of the group, then the group price applies; otherwise the item price applies.

-- 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 ;

Notes

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key

---

Option 2

As per comments below, to keep GRP in 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}

If an item is member of the group G00, then the item price applies, otherwise the group price applies.

-- 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 ;