Home E-commerceOnline Store

Online Store

Complete e-commerce platform with product variants, cart, orders, reviews, and payment processing

SchemaVerse

E-commercePostgreSQLE-commerceNormalizedREST API

online-store.dbml

14 tables|20 relations

users

idinteger

emailvarchar(255)

*UQ

password_hashvarchar(255)

first_namevarchar(100)

last_namevarchar(100)

phonevarchar(20)

created_attimestamp

updated_attimestamp

addresses

idinteger

user_idinteger

FK*

address_typevarchar(20)

street_addressvarchar(255)

cityvarchar(100)

statevarchar(50)

postal_codevarchar(20)

countryvarchar(2)

is_defaultboolean

created_attimestamp

updated_attimestamp

categories

idinteger

namevarchar(100)

slugvarchar(100)

*UQ

parent_idinteger

descriptiontext

created_attimestamp

updated_attimestamp

products

idinteger

namevarchar(255)

slugvarchar(255)

*UQ

descriptiontext

base_pricedecimal(10,2)

skuvarchar(100)

is_activeboolean

created_attimestamp

updated_attimestamp

product_variants

idinteger

product_idinteger

FK*

skuvarchar(100)

*UQ

namevarchar(100)

pricedecimal(10,2)

stock_quantityinteger

weight_kgdecimal(8,2)

created_attimestamp

updated_attimestamp

product_categories

product_idinteger

category_idinteger

shopping_carts

idinteger

user_idinteger

session_idvarchar(100)

created_attimestamp

updated_attimestamp

cart_items

idinteger

cart_idinteger

FK*

variant_idinteger

FK*

quantityinteger

added_attimestamp

coupons

idinteger

codevarchar(50)

*UQ

discount_typevarchar(20)

discount_valuedecimal(10,2)

min_purchase_amountdecimal(10,2)

max_usesinteger

current_usesinteger

valid_fromtimestamp

valid_untiltimestamp

is_activeboolean

created_attimestamp

shipping_methods

idinteger

namevarchar(100)

descriptiontext

base_costdecimal(10,2)

estimated_days_mininteger

estimated_days_maxinteger

is_activeboolean

created_attimestamp

orders

idinteger

user_idinteger

FK*

order_numbervarchar(50)

*UQ

statusvarchar(20)

subtotaldecimal(10,2)

shipping_costdecimal(10,2)

tax_amountdecimal(10,2)

discount_amountdecimal(10,2)

total_amountdecimal(10,2)

coupon_idinteger

shipping_method_idinteger

shipping_address_idinteger

billing_address_idinteger

payment_methodvarchar(50)

payment_statusvarchar(20)

tracking_numbervarchar(100)

notestext

created_attimestamp

updated_attimestamp

order_items

idinteger

order_idinteger

FK*

variant_idinteger

FK*

quantityinteger

unit_pricedecimal(10,2)

total_pricedecimal(10,2)

created_attimestamp

reviews

idinteger

product_idinteger

FK*

user_idinteger

FK*

order_idinteger

ratinginteger

titlevarchar(200)

commenttext

is_verified_purchaseboolean

created_attimestamp

updated_attimestamp

wishlists

idinteger

user_idinteger

FK*

product_idinteger

FK*

added_attimestamp

React Flow

About this Schema

Overview

A comprehensive e-commerce database schema modeling a modern online store like Shopify or WooCommerce. Supports product catalogs with variants, shopping cart functionality, order processing, customer reviews, and payment integration.

Key Design Decisions

Product Variants: Separate tables for products and variants to handle size/color/material combinations
Cart Persistence: Shopping cart items stored in database for logged-in users
Order Workflow: Status tracking from pending through shipped to delivered
Review System: Customer feedback with star ratings and moderation

Tables

15 tables covering products, inventory, cart, orders, payments, reviews, and customer management. Includes proper foreign key relationships and indexes for common queries like product browsing and order history.

Comments

Loading comments...