Nothing close enough? Start from a blank erd → Describe it in one paragraph.
How to use an erd template.
- 01Define the User and Product entities
Start with User (user_id, email, name, address) and Product (product_id, name, description, price, stock). These are the two central entities in any ecommerce schema.
- 02Model the Order and OrderItems relationship
Create Order linked to User (one user, many orders), and OrderItems as a junction table between Order and Product. OrderItems holds quantity and unit_price at time of purchase.
- 03Add Cart and CartItems
Model Cart as a temporary order entity linked to User, with CartItems linking Cart and Product. The cart-to-order conversion is a critical workflow to represent.
- 04Include Payment and Shipping
Add Payment linked to Order (payment_method, status, amount, transaction_id) and Shipping (carrier, tracking_number, estimated_delivery, status) to complete the transaction lifecycle.
- 05Extend with Reviews and Discounts
Add a Review entity (user_id FK, product_id FK, rating, text, date) and a Discount or Coupon entity (code, discount_type, amount, expiry) to model a complete commerce platform.
Questions about erd templates
What are the core entities in an ecommerce database?
Core entities: User, Product, Order, OrderItems (junction), Cart, CartItems, Payment, and ShippingAddress. Extended entities add ProductVariant, Review, Category, Discount, and Inventory.
How do I model product variants (size, color) in an ER diagram?
Create a ProductVariant entity linked to Product with attributes for each variant dimension (size, color, SKU, stock_qty, price_modifier). OrderItems and CartItems then reference ProductVariant rather than Product directly.
Should Cart and Order be separate entities?
Yes for most designs. Cart is ephemeral (can be abandoned, modified, expired) while Order is a permanent transaction record. Separating them makes it easier to analyze abandoned cart rates and maintain order history integrity.
How do I handle multiple shipping addresses per user?
Create a separate Address entity linked to User (one user, many addresses). Order then references Address via a shipping_address_id foreign key, preserving the address at time of purchase even if the user updates their profile later.
How do I model discount codes in an ecommerce schema?
Create a Discount entity (code, type: percentage/fixed, value, min_order_amount, expiry, max_uses). Link it to Order via an applied_discount_id FK. This records which discount was applied to each order at the time of checkout.