Skip to content

Database Schema

Entity Relationship Overview

erDiagram
    profiles ||--o| sellers : "pharmacy_id"
    profiles ||--o{ orders : "user_id"
    profiles ||--o{ addresses : "user_id"
    profiles ||--o{ reviews : "user_id"
    profiles ||--o{ notifications : "user_id"
    profiles ||--o{ chats : "via chat_participants"
    profiles ||--o{ wishlists : "user_id"

    sellers ||--o{ products : "seller_id"
    sellers ||--o{ orders : "via order_items.seller_id"
    sellers ||--o{ featured_product_payments : "pharmacy_id"
    sellers ||--o{ pharmacy_subscriptions : "pharmacy_id"
    sellers ||--o{ analytics : "pharmacy_id"
    sellers ||--o{ offer_products : "pharmacy_id"

    categories ||--o{ categories : "parent_id"
    categories ||--o{ products : "category_id"
    brands ||--o{ products : "brand_id"

    products ||--o{ order_items : "product_id"
    products ||--o{ variant_options : "product_id"
    products ||--o{ variant_combinations : "product_id"
    products ||--o{ reviews : "product_id"
    products ||--o{ wishlist_items : "product_id"
    products ||--o{ offer_products : "product_id"
    products ||--o{ featured_product_payments : "product_id"
    products ||--o{ featured_product_access : "product_id"

    variant_options ||--o{ variant_values : "variant_option_id"
    variant_combinations ||--o{ variant_combination_values : "variant_combination_id"
    variant_values ||--o{ variant_combination_values : "variant_value_id"

    orders ||--o{ order_items : "order_id"
    orders ||--o{ payments : "order_id"
    orders ||--o{ order_status_history : "order_id"

    carts ||--o{ cart_items : "cart_id"
    cart_items ||--o| products : "product_id"

    chats ||--o{ messages : "chat_id"
    chats ||--o{ chat_participants : "chat_id"

    promotions ||--o{ offer_products : "promotion_id"
    banners ||--o| promotions : "promotion_id"

Table Inventory

# Table Domain Key Columns
1 profiles Users id, email, role, fcm_token, security prefs
2 sellers Users id, user_id, store_name, approval_status, bank info
3 categories Catalog id, name, parent_id, sort_order
4 brands Catalog id, name, slug, logo
5 products Catalog id, seller_id, price, discount, prescription, variants
6 variant_options Catalog id, product_id, option_name
7 variant_values Catalog id, variant_option_id, value_name
8 variant_combinations Catalog id, product_id, sku, price, stock
9 variant_combination_values Catalog id, combo_id, value_id
10 carts Commerce id, user_id
11 cart_items Commerce id, cart_id, product_id, quantity
12 addresses Users id, user_id, address_line, city, is_default
13 orders Commerce id, order_number, user_id, total, status
14 order_items Commerce id, order_id, product_id, seller_id, price, status
15 payments Commerce id, order_id, tx_ref, amount, status, chapa_response
16 order_status_history Commerce id, order_id, old_status, new_status, notes
17 reviews Engagement id, product_id, user_id, rating, comment
18 wishlists Engagement id, user_id
19 wishlist_items Engagement id, wishlist_id, product_id
20 chats Engagement id, last_message, unread_count
21 chat_participants Engagement id, chat_id, user_id
22 messages Engagement id, chat_id, sender_id, content, type, attachment
23 notifications Engagement id, user_id, type, title, message, related_id, data
24 banners CMS id, title, image_url, link_url, position, sort_order, promotion_id
25 static_pages CMS id, title, slug, content
26 settings CMS id, key, value, group, type
27 analytics Analytics id, pharmacy_id, order_id, metrics
28 featured_product_payments Monetization id, product_id, pharmacy_id, duration, status, amount
29 pharmacy_subscriptions Monetization id, pharmacy_id, type, status, amount, tx_ref
30 featured_product_access Monetization id, product_id, date, views, clicks, conversions
31 promotions Promotions id, title, status, start_date, end_date, created_by
32 offer_products Promotions id, product_id, promotion_id, pharmacy_id, discount, price