Shopify

Production-grade database schema for a Shopify-like e-commerce platform featuring multi-store management, product catalog with variants and images, inventory tracking, order lifecycle management, fulfillment, payment processing, and discount engine.

PostgreSQL15 tablesE-Commerce

e-commercemulti-tenantinventoryfulfillmentpayments

MySQL10 cols

Num

Null

Physical Name

Type

Default

Comment

Unique shop identifier

Owner user account

Shop display name

Primary domain for the shop

Shop contact email

ISO 4217 currency code

Shop timezone string

Shopify plan: basic, shopify, advanced

Shop creation timestamp

Last update timestamp

IXUQCK|💬

MySQL8 cols

Num

Null

Physical Name

Type

Default

Comment

Unique user identifier

User login email

Hashed user password

User first name

User last name

Whether user has admin access

Account creation timestamp

Last profile update timestamp

IXUQCK|💬

MySQL10 cols

Num

Null

Physical Name

Type

Default

Comment

Unique customer identifier

Shop this customer belongs to

Customer email address

Customer first name

Customer last name

Customer phone number

Whether customer opted in to marketing emails

Lifetime spend amount

Customer account creation timestamp

Last update timestamp

IXUQCK|💬

MySQL11 cols

Num

Null

Physical Name

Type

Default

Comment

Unique address identifier

Customer this address belongs to

Recipient first name

Recipient last name

Primary street address

Secondary address line, apartment or suite

City name

State or province name

ISO 3166-1 country code

Postal or zip code

Whether this is the default address

IXUQCK|💬

MySQL11 cols

Num

Null

Physical Name

Type

Default

Comment

Unique product identifier

Shop that owns this product

Product display title

Full product description in HTML

Product vendor or brand name

Product category classification

Status: draft, active, archived

Comma-separated product tags

SEO meta title

Product creation timestamp

Last update timestamp

IXUQCK|💬

MySQL12 cols

Num

Null

Physical Name

Type

Default

Comment

Unique variant identifier

Parent product

Variant title, combination of options

Stock Keeping Unit code

Variant selling price

Original price for sale display

Variant weight in grams

First option value e.g. Red

Second option value e.g. Large

Third option value e.g. Cotton

Variant creation timestamp

Last update timestamp

IXUQCK|💬

MySQL8 cols

Num

Null

Physical Name

Type

Default

Comment

Unique image identifier

Product this image belongs to

Specific variant this image represents

CDN URL of the product image

Alt text for accessibility

Display order position starting from 1

Image upload timestamp

Last update timestamp

IXUQCK|💬

MySQL7 cols

Num

Null

Physical Name

Type

Default

Comment

Unique inventory level record identifier

Product variant being tracked

Warehouse or location identifier

Available inventory quantity

Quantity reserved for pending orders

Last inventory update timestamp

Record creation timestamp

IXUQCK|💬

MySQL16 cols

Num

Null

Physical Name

Type

Default

Comment

Unique order identifier

Shop that received the order

Customer who placed the order

Sequential order number within shop

Order status: pending, processing, completed, cancelled

Payment status: pending, paid, refunded, voided

Fulfillment status: unfulfilled, partial, fulfilled

Order subtotal before tax and shipping

Total tax amount

Total shipping cost

Total discount amount applied

Final order total

Order currency ISO code

Shipping address for this order

Order placement timestamp

Last order update timestamp

IXUQCK|💬

MySQL10 cols

Num

Null

Physical Name

Type

Default

Comment

Unique order item identifier

Parent order

Product variant ordered

Product title at time of purchase

Variant title at time of purchase

SKU at time of purchase

Quantity ordered

Unit price at time of purchase

Line item total price

Record creation timestamp

IXUQCK|💬

MySQL9 cols

Num

Null

Physical Name

Type

Default

Comment

Unique fulfillment identifier

Order being fulfilled

Fulfillment status: pending, open, success, cancelled

Shipping carrier name

Carrier tracking number

Link to carrier tracking page

When order was shipped

Fulfillment creation timestamp

Last update timestamp

IXUQCK|💬

MySQL6 cols

Num

Null

Physical Name

Type

Default

Comment

Unique fulfillment item identifier

Parent fulfillment

Order item being fulfilled

Quantity being fulfilled in this shipment

Record creation timestamp

Last update timestamp

IXUQCK|💬

MySQL10 cols

Num

Null

Physical Name

Type

Default

Comment

Unique discount code identifier

Shop this discount belongs to

Discount code string entered at checkout

Type: percentage, fixed_amount, free_shipping

Discount amount or percentage

Minimum order total required

Maximum total uses allowed, null for unlimited

Number of times code has been used

When discount code becomes active

When discount code expires

IXUQCK|💬

MySQL9 cols

Num

Null

Physical Name

Type

Default

Comment

Unique collection identifier

Shop this collection belongs to

Collection display title

Collection description in HTML

Collection cover image URL

Whether this is a rule-based smart collection

Default product sort order

Collection creation timestamp

Last update timestamp

IXUQCK|💬

MySQL6 cols

Num

Null

Physical Name

Type

Default

Comment

Unique collection-product junction identifier

Collection

Product in collection

Display position within collection

Record creation timestamp

Last update timestamp

IXUQCK|💬

Press enter or space to select a node. You can then use the arrow keys to move the node around. Press delete to remove it and escape to cancel.

Press enter or space to select an edge. You can then press delete to remove it or escape to cancel.

Created with ERD Studio

Tables in this template

shopsShops

10 cols

usersUsers

8 cols

customersCustomers

10 cols

addressesAddresses

11 cols

productsProducts

11 cols

product_variantsProduct Variants

12 cols

product_imagesProduct Images

8 cols

inventory_levelsInventory Levels

7 cols

ordersOrders

16 cols

order_itemsOrder Items

10 cols

fulfillmentsFulfillments

9 cols

fulfillment_itemsFulfillment Items

6 cols

discount_codesDiscount Codes

10 cols

collectionsCollections

9 cols

collection_productsCollection Products

6 cols

Related Templates

View all templates →

E-Commerce Platform

Complete database schema for an online store with products, orders, customers, payments, and inventory management.

PostgreSQL12 tables

e-commerceshoppingpayments+2

E-Commerce

Multi-Vendor Marketplace

Database schema for a multi-vendor marketplace with stores, products, orders, reviews, and seller payouts.

PostgreSQL11 tables

marketplacemulti-vendore-commerce+3

E-Commerce

Subscription Box Service

Database schema for a subscription box service with recurring plans, curated boxes, and shipment tracking.

PostgreSQL10 tables

subscriptionrecurringbox+3

E-Commerce

Digital Product Store

Database schema for a digital product marketplace with creators, licenses, downloads, and customer reviews.

PostgreSQL10 tables

digitaldownloadslicenses+3

E-Commerce