1

For a school Food Delivery application I am developing, I've been told to use the most accurate ER model that captures as many constraints as possible. In particular, I have decided to enforce a given constraint

"Each order’s food items must be from a single restaurant"

using an aggregation from Orders to Restaurant-FoodItem relationship. However, as the application layer requires to get a restaurant_id associated to an Order, what I am currently doing to get restaurant_id is to join all the way from Orders to Restaurants, following the edges on the ER model.

Picture

There was another alternative suggested by my teammate to just put restaurant_id as an attribute in Orders to enforce the constraint as well as allow easier more efficient retrieval of restaurant_id. May I know whether the suggested alternative allows us to enforce the earlier constraint?

1

This would be one way to enforce the constraint.


-- User USR exists.
--
user {USR}
  PK {USR}
-- Restaurant RST exists.
--
restaurant {RST}
        PK {RST}
-- Fare (food, drink) FAR exists.
--
fare {FAR}
  PK {FAR}
-- Restaurant RST serves fare FAR.
--
menu {RST, FAR}
  PK {RST, FAR}

FK1 {RST} REFERENCES restaurant {RST}
FK2 {FAR} REFERENCES fare       {FAR}
-- User USR placed (his/her) order number ORD#,
-- from restaurant RST.
--
order {USR, ORD#, RST}
   PK {USR, ORD#}
   SK {USR, ORD#, RST}

FK1 {USR} REFERENCES user       {USR}
FK2 {RST} REFERENCES restaurant {RST}
-- User USR ordered QTY of fare FAR
-- from restaurant RST in (his/her) order number ORD#,
-- as item number ITM# of that order.
--
order_item {USR, ORD#, RST, ITM#, FAR, QTY}
        PK {USR, ORD#, RST, ITM#}
        AK {USR, ORD#, RST, FAR}

  FK1 {USR, ORD#, RST} REFERENCES
order {USR, ORD#, RST}

 FK2 {RST, FAR} REFERENCES
menu {RST, FAR}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
Using suffix # to save on screen space.
Works for SQL Server and Oracle, for others use _NO.
For example, rename ORD# to ORD_NO.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.