ER Diagram

Data Requirements

Entities

1. User - Entity for all users in the system

• user_id – primary key, numeric, required
• first_name – text type, required
• last_name - text type, required
• phone- text type, required
• email- text type, required
• password - text type, required

2. Customer

• (No extra attributes, inherits from User)

3. Delivery Man

• (No extra attributes, inherits from User)

4. Restaurant Owner

• (No extra attributes, inherits from User)

5. Order

• order_id – primary key, numeric, required
• order_date – date, required
• status - text type, required
• comment- text type, required

6. Order Items

• order_id - foreign key, required
• item_id - foreign key, required
• quantity - numeric type, required
• total_price- numeric type, required

7. Item

• item_id - primary key, numeric, required
• name- text type, required
• description - text type, required
• price - numeric type, required
• image_url - text type, optional

8. Menu

• menu_id- primary key, numeric, required
• name- text type, required
• created_at - date, required
• updated_at- date, required

9. Restaurant

• restaurant_id - primary key, numeric, required
• name - text type, required
• email - text type, required
• phone_number - text type, required
• website_url - text type, required
• opening_time - time, required
• closing_time - time, required

10. Restaurant Owners

• restaurantowner_id - foreign key, required
• restaurant_id - foreign key, required

11. Delivery Assignment

• deliveryfirm_id - foreign key, required
• deliveryman_id - foreign key, required
• start_date - date, required
• end_date - date, required

12. Delivery Firm

• firm_id - primary key, numeric, required
• name - text type, required

13. Earnings

• earning_id - primary key, numeric, required
• date - date, required
• source - text type, required
• amount- numeric type, required

14. Costs

• cost_id- primary key, numeric, required
• date - date, required
• type - text type, required
• amount- numeric type, required

15. Item Ingredient

• item_id - foreign key, required
• ingredient_id - foreign key, required
• quantity - numeric type, required

16. Ingredient

• ingredient_id - primary key, numeric, required
• name - text type, required

Relations

1:1

• Restaurant → Menu
  

has – a relation which indicates that a restaurant has one menu and a menu belongs to only one restaurant.

• Address → Restaurant
  

is_located_at – a relation which indicates that a restaurant is located at one location, and a location can have only 1 restaurant.

1:M

• Customer → Order
  

has – a relation which indicates that a user can have multiple addresses, and an address can belong to one user.

M:N

• User ↔ User Adresses ↔ Address
  

Junction: User Adresses solves M:N of User and Address, by storing the composite key (user_id, address_id)

has – relation between User and User Addresses where a user can have multiple addresses.

belongs – relation between Address and User Addresses where an address can belong to multiple users.