ERD

erDiagram
    User ||--o{ Store : owns
    User ||--o{ Order : places
    User ||--o{ Review : writes
    User ||--|| Cart : has
    User ||--|| PaymentAccount : has
    User ||--o{ PaymentTransaction : has
    User }|--o{ Coupon : receives

    Store ||--o{ Product : offers
    Store ||--o{ Order : receives

    Product ||--o{ ProductOption : has
    Product ||--o{ OrderItem : includes
    Product ||--o{ CartItem : contains

    Order ||--o{ OrderItem : contains
    OrderItem ||--o{ OrderItemOption : has

    Cart ||--o{ CartItem : contains
    CartItem ||--o{ CartItemOption : has

    User {
        int id PK
        string name
        string email
        string password
        string phone_number
        enum user_type
        string account_number
    }

    Store {
        int id PK
        int owner_id FK
        string name
        string business_number
        string phone_number
        string location
        string category
        string operating_hours
        decimal minimum_order_amount
    }

    Product {
        int id PK
        int store_id FK
        string name
        string image_url
        string category
        decimal price
        int stock
    }

    ProductOption {
        int id PK
        int product_id FK
        string name
        decimal price
        boolean is_multiple_choice
    }

    Order {
        int id PK
        int user_id FK
        int store_id FK
        enum status
        decimal total_amount
        datetime ordered_at
    }

    OrderItem {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal price
    }

    OrderItemOption {
        int id PK
        int order_item_id FK
        int product_option_id FK
    }

    Review {
        int id PK
        int order_id FK
        int user_id FK
        int store_id FK
        string content
        int rating
        datetime created_at
    }

    Cart {
        int id PK
        int user_id FK
        int store_id FK
    }

    CartItem {
        int id PK
        int cart_id FK
        int product_id FK
        int quantity
    }

    CartItemOption {
        int id PK
        int cart_item_id FK
        int product_option_id FK
    }

    PaymentAccount {
        int id PK
        int user_id FK
        decimal balance
        decimal daily_charge_limit
    }

    PaymentTransaction {
        int id PK
        int user_id FK
        decimal amount
        enum type
        datetime created_at
    }

    Coupon {
        int id PK
        decimal discount_amount
        date expiry_date
    }

    UserCoupon {
        int id PK
        int user_id FK
        int coupon_id FK
        boolean is_used
        datetime received_at
    }

Loading