Question :
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.
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?
Answer :
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.