Data Model

Data Model

22 domain tables and 27 foreign keys, grouped into catalogue, orders and payments, customers and staff, personalisation, security and inbox. The diagram is generated from the live MySQL schema.

Relationships

erDiagram
    DEPARTMENTS ||--o{ CATEGORIES : groups
    DEPARTMENTS ||--o{ PRODUCTS : contains
    CATEGORIES ||--o{ PRODUCTS : classifies
    BRANDS ||--o{ PRODUCTS : makes
    USERS ||--o{ ORDERS : places
    USERS ||--o{ PRESCRIPTIONS : uploads
    USERS ||--o{ WISHLIST_ITEMS : saves
    USERS ||--o{ USER_ACTIVITIES : "is logged in"
    USERS ||--o{ BANS : receives
    USERS ||--o{ PRODUCT_INTERACTIONS : "builds profile"
    USERS ||--o{ OFFERS : earns
    PRESCRIPTIONS ||--o{ ORDERS : authorises
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "sold as"
    PRODUCTS ||--o{ WISHLIST_ITEMS : "saved as"
    PRODUCTS ||--o{ PRODUCT_INTERACTIONS : "viewed in"
    PRODUCTS ||--o{ OFFERS : "discounted by"
    ORDERS ||--o{ OFFERS : redeems
    BANS ||--|{ BAN_IDENTIFIERS : blocks
    ORDERS ||--o{ PAYMENTS : "paid by"
    PAYMENTS ||--o{ REFUNDS : "refunded by"
    USERS ||--o{ REFILL_REMINDERS : "reminded about"
    PRODUCTS ||--o{ REFILL_REMINDERS : "refill of"

Loading

Key columns

Table	Notable columns
users	username (random, admin-editable), role (owner / pharmacist / support), two_factor_secret (encrypted), refill_reminders, lat/lng, banned_until
products	slug, price, sale_price, stock (row-locked at checkout), max_per_order, requires_prescription, generics
orders	number (ZV-yymmdd-XXXXX), checkout_token (idempotency), status, offer_discount, payment_status, interaction_warnings (JSON)
payments / refunds	reference (gateway session), amount, refunded_amount, reason
prescriptions	reference, status, auto_approved (24 h rule), file_path (private disk)
offers	visitor (u:id or g:uuid), kind, percent (max 15), expires_at, redeemed_at
product_interactions	visitor, views, dwell_seconds, cart_adds, purchases
refill_reminders	last_purchased_at (unique per cycle), due_at, interval_days, sent_at
bans / ban_identifiers	severity, expires_at, lifted_at; identifier type (email / phone / device / fingerprint / ip / network) and hashed or canonical value

Also: experiment_events and experiment_assignments (A/B), webhook_events, contact_messages, newsletter_subscribers, plus Laravel's sessions, cache and jobs.