ER Diagram

Entities

• Entity: User
  • Description: Contains main information about all users in the system.
  • Primary Key: id.
  • Attributes:
    • email: User's email address.
    • street: Street address of the user.
    • city: City of residence.
    • phone_number: Contact phone number.
    • password: Encrypted user password.

• Entity: Employee
  • Description: Represents employees of the system.
  • Primary Key: user_id.
  • Attributes:
    • user_id: Foreign key referencing User.
    • net_salary: The net salary of the employee.
    • gross_salary: The gross salary of the employee.

• Entity: Customer
  • Description: Represents customers in the system.
  • Primary Key: user_id.
  • Attributes:
    • user_id: Foreign key referencing User.

• Entity: Manager
  • Description: A disjoint specialization of the Employee entity for managers.
  • Primary Key: employee_id.

• Entity: FrontStaff
  • Description: A disjoint specialization of the Employee entity for front-facing staff members.
  • Primary Key: employee_id.
  • Attributes:
    • tip_percent: Percentage of tips allocated to the staff.
    • staff_role_id: Foreign key referencing StaffRole.

• Entity: BackStaff
  • Description: A disjoint specialization of the Employee entity for back-office staff members.
  • Primary Key: employee_id.
  • Attributes:
    • staff_role_id: Foreign key referencing StaffRole.

• Entity: StaffRole
  • Description: Defines roles for staff members.
  • Primary Key: id.
  • Attributes:
    • name: Name of the staff role.

• Entity: Shift
  • Description: Represents a work shift in the system.
  • Primary Key: id.
  • Attributes:
    • date: Date of the shift.
    • start: Start time of the shift.
    • end: End time of the shift.
    • employee_id: Foreign key referencing Manager.

• Entity: Assignment
  • Description: Represents shift assignment for each employee.
  • Primary Key: id.
  • Attributes:
    • clock_in_time: Clock in time of the employee.
    • clock_out_time: Clock out time of the employee.
    • manager_id: Foreign key referencing Manager.
    • employee_id: Foreign key referencing Employee.
    • shift_id: Foreign key referencing Shift.

• Entity: Reservation
  • Description: Represents reservations made by users.
  • Primary Key: id.
  • Attributes:
    • stay_length: Duration of the stay.
    • datetime: Date and time of the reservation.
    • creation_timestamp: Timestamp when the reservation was created.
    • number_of_people: Number of people in the reservation.
    • customer_id: Foreign key referencing Customer.

• Entity: Category
  • Description: Represents product categories.
  • Primary Key: id.
  • Attributes:
    • name: Name of the category.
    • is_available: Availability status of the category.

• Entity: Product
  • Description: Represents items that can be purchased in the system.
  • Primary Key: id.
  • Attributes:
    • name: Name of the product.
    • price: Price of the product.
    • tax_class: Tax classification for the product.
    • description: Description of the product.
    • category_id: Foreign key referencing Category.
    • manage_inventory: Boolean indicating if inventory is managed.

• Entity: Inventory
  • Description: Tracks product quantities and restocking levels.
  • Primary Key: product_id.
  • Attributes:
    • quantity: Current stock quantity.
    • restock_level: Minimum stock level before restocking is required.

• Entity: OrderItem
  • Description: Represents items in a customer's order.
  • Primary Key: id.
  • Attributes:
    • quantity: Quantity of the product ordered.
    • price: Price of the product in the order.
    • is_processed: Processing status of the item.
    • timestamp: Time when the item was added.
    • product_id: Foreign key referencing Product.
    • order_id: Foreign key referencing Order.

• Entity: Order
  • Description: Represents a customer's order in the system.
  • Primary Key: id.
  • Attributes:
    • timestamp: Time when the order was placed.
    • status: Status of the order.
    • employee_id: Foreign key referencing Employee.

• Entity: TabOrder
  • Description: Represents orders made at a specific table.
  • Primary Key: order_id.
  • Attributes:
    • table_number: Foreign key referencing Table.

• Entity: OnlineOrder
  • Description: Represents orders made online by customers.
  • Primary Key: order_id.
  • Attributes:
    • customer_id: Foreign key referencing Customer.

• Entity: Table
  • Description: Represents dining tables in the system.
  • Primary Key: table_number.
  • Attributes:
    • seat_capacity: Seating capacity of the table.

• Entity: Payment
  • Description: Represents payments for orders.
  • Primary Key: id.
  • Attributes:
    • tip_amount: Amount of tip included.
    • timestamp: Time of payment.
    • payment_type: Type of payment (e.g., cash, card).
    • amount: Total payment amount.
    • order_id: Foreign key referencing Order.

Relationships

• belongs_to 1:N Relation between Category and Product.
  • Description: Indicates which category a product belongs to.

• is_in: 1:1 Relation between Product and Inventory.
  • Description: Indicates the product's record in inventory.

• is: 1:N Relation between Product and OrderItem.
  • Description: Indicates which products are order items.

• has: 1:M Relation between Order and OrderItem.
  • Description: Indicates which order items belong to a given order.

• places_order: 1:P Relation between Customer and OnlineOrder.
  • Description: Indicates which online order a customer placed.

• is_placed_on: 1:M Relation between Table and TabOrder.
  • Description: Indicates which table a tab order is placed on.

• has_payment: 1:1 Relation between Order and Payment.
  • Description: Indicates the payment for the given order.

• creates: 1:M Relation between Manager and Shift.
  • Description: Indicates the manager's created shifts.

• assigns: 1:N Relation between Manager, and Assignment.
  • Description: Indicates a manager making an assignment.

• assigned_to: 1:N Relation between Employee, and Assignment.
  • Description: Indicates which employee has the assignment.

• is_assigned: 1:N Relation between Shift, and Assignment.
  • Description: Indicates which shift is assigned.

• makes: 1:P Relation between Customer and Reservation.
  • Description: Indicates which customer makes the reservation.

• has_role: 1:N Relation between StaffRole and BackStaff/FrontStaff.
  • Description: Indicates the role of back-staff/front-staff employees.