Logical DB model representation

logical_db_model.md

erDiagram
    USERS {
        integer id PK
        string first_name
        string last_name  
        string email
        string phone_number
        string phone_number_country_code
        boolean phone_number_verified
        string preferred_locale
        string stripe_customer_id
        boolean freezed
        string freeze_reason
        integer earned_amount
        datetime created_at
        datetime updated_at
        boolean deleted
    }
    
    VISITORS {
        integer id PK
        string x_token_visitor
        string contact_email
        datetime expires_at
        datetime created_at
        datetime updated_at
    }
    
    CITIES {
        integer id PK
        string name
        string country_code
        string currency
        string manager_name
        string manager_email
        string manager_phone_number
        boolean open
        string manager_picture
        string default_background_image
        datetime created_at
        datetime updated_at
    }
    
    HOMIE_SERVICES {
        integer id PK
        string name
        array opening_days
        integer opening_hour
        integer closing_hour
        integer minimum_bookable_hour
        integer city_id FK
        boolean open
        integer position
        string icon_image
        string header_background_image
        string icon_hex_color
        datetime created_at
        datetime updated_at
    }
    
    HOMIES {
        integer id PK
        string first_name
        string last_name
        string phone_number
        string email
        integer city_id FK
        integer homie_service_id FK
        string gender
        string profile_picture
        date birthdate
        boolean driving_licence
        array languages
        array commuting_ways
        boolean is_hidden
        string password
        datetime created_at
        datetime updated_at
    }
    
    ADDRESSES {
        integer id PK
        string name
        string line1
        string line2
        string street_number
        string house_number
        string city
        string zip_code
        string state
        string country
        string country_code
        string additional_information
        integer user_id FK
        integer visitor_id FK
        boolean deleted
        datetime created_at
        datetime updated_at
    }
    
    ORDERS {
        integer id PK
        integer user_id FK
        integer visitor_id FK
        integer city_id FK
        datetime expires_at
        datetime created_at
        datetime updated_at
    }
    
    SERVICES {
        integer id PK
        string type
        string frequency
        string status
        string frequent_mission_day
        integer frequent_mission_time
        datetime mission_date
        string plan
        integer quantity
        integer user_id FK
        integer visitor_id FK
        integer order_id FK
        integer city_id FK
        integer homie_service_id FK
        integer address_id FK
        text user_comment
        datetime created_at
        datetime updated_at
    }
    
    MISSIONS {
        integer id PK
        datetime meeting_date
        integer service_id FK
        integer homie_service_id FK
        integer homie_id FK
        integer original_price
        integer price
        integer user_id FK
        string status
        integer address_id FK
        string plan
        integer quantity
        string payment_method
        string payment_recovery_uid
        integer payment_attempts_count
        datetime last_payment_attempt_at
        text user_comment
        datetime created_at
        datetime updated_at
    }
    
    PAYMENTS {
        integer id PK
        integer user_id FK
        integer mission_id FK
        string stripe_charge_id
        integer amount
        string currency
        string status
        string stripe_receipt_url
        string stripe_card_id
        string invoice
        integer invoice_number
        datetime created_at
        datetime updated_at
    }
    
    CARDS {
        integer id PK
        string last4
        string brand
        integer exp_month
        integer exp_year
        string fingerprint
        string funding
        integer user_id FK
        boolean default
        string stripe_id
        boolean deleted
        datetime created_at
        datetime updated_at
    }
    
    COUPONS {
        integer id PK
        string name
        string type
        datetime starts_at
        datetime expires_at
        integer discount
        integer amount
        integer sponsor_id FK
        boolean code_is_uniq
        datetime created_at
        datetime updated_at
    }
    
    COUPON_ENTRIES {
        integer id PK
        integer coupon_id FK
        integer user_id FK
        string code_name
        boolean consummed
        datetime created_at
        datetime updated_at
    }
    
    COUPON_LABELS {
        integer id PK
        string code
        integer coupon_id FK
        datetime created_at
        datetime updated_at
    }

    %% Primary Relationships
    CITIES ||--o{ HOMIE_SERVICES : contains
    CITIES ||--o{ HOMIES : "located in"
    CITIES ||--o{ ORDERS : "placed in"
    CITIES ||--o{ SERVICES : "available in"
    
    HOMIE_SERVICES ||--o{ HOMIES : "work in"
    HOMIE_SERVICES ||--o{ SERVICES : "type of"
    HOMIE_SERVICES ||--o{ MISSIONS : "service type"
    
    USERS ||--o{ ADDRESSES : owns
    USERS ||--o{ CARDS : has
    USERS ||--o{ ORDERS : places
    USERS ||--o{ SERVICES : creates
    USERS ||--o{ MISSIONS : orders
    USERS ||--o{ PAYMENTS : makes
    USERS ||--o{ COUPON_ENTRIES : uses
    USERS ||--o{ COUPONS : sponsors
    
    VISITORS ||--o{ ADDRESSES : uses
    VISITORS ||--o{ ORDERS : places
    VISITORS ||--o{ SERVICES : creates
    
    ORDERS ||--o{ SERVICES : contains
    
    SERVICES ||--|| MISSIONS : generates
    SERVICES }o--|| ADDRESSES : "delivered to"
    
    MISSIONS ||--o{ PAYMENTS : "paid by"
    MISSIONS }o--|| HOMIES : "assigned to"
    MISSIONS }o--|| ADDRESSES : "performed at"
    
    COUPONS ||--o{ COUPON_ENTRIES : "used as"
    COUPONS ||--o{ COUPON_LABELS : "has codes"

Loading