Define Conditional many-to-many in Postgresql

user_2551660 0
Viewed 97

I have these three tables,

  • menu
  • item
  • category

And the relationship among all these tables are,

  1. A menu can have multiple items and categories. So menu has a one-to-many relationship with item and category.
  2. Item and category have a many-to-many relationship

erd

  1. Furthermore, the constraint I am thinking is that item and category can only be connected if they are both under the same menu.

Currently I am thinking to enforce the first two contraint (one-to-many and many-to-many) in the database and handle the 3rd constraint in application side.

Is there a better way in postgres or any other db to define this type of conditional constraint? If yes, is there a industry term for it?

database-design postgresql
1 Answers

Not sure if this makes sense from the point of running a restaurant, but matches constraints as described in the question.

-- Menu MEN exists.
--
menu {MEN}
  PK {MEN}

-- Item ITM is on menu MEN.
--
item {ITM, MEN}
  PK {ITM}
  SK {ITM, MEN}

-- Category CAT is listed on menu MEN.
--
category {CAT, MEN}
      PK {CAT}
      SK {CAT, MEN}

-- Item ITM, from menu MEN, is in category CAT
-- from the same menu.
--
item_category {ITM, CAT, MEN}
           PK {ITM, CAT}

FK1 {ITM, MEN} REFERENCES item {ITM, MEN}
FK2 {CAT, MEN} REFERENCES category {CAT, MEN}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
user_1967130
Related