Technical Data Model — E-commerce Order System

System / domain: Online store — customer ordering and payment Level: Logical, with physical notes for the chosen database Database / platform: PostgreSQL Author: Platform Data Team Last updated: 5 June 2026

---

1. Overview

This model covers how the store records who buys, what they buy, and how they pay. The central entity is the Order: a customer places many orders over time, each order contains one or more line items pointing at products, and each order is settled by one payment. Everything else hangs off that core relationship.

2. Entities

Entity	Description	Key fields
Customer	A person with an account who can place orders	customer_id (primary key), email (unique), full_name, created_at
Order	A single purchase event placed by a customer	order_id (primary key), customer_id (foreign key), status, placed_at, total_amount
OrderItem	One line on an order: a quantity of a single product	order_item_id (primary key), order_id (foreign key), product_id (foreign key), quantity, unit_price
Product	An item available for sale	product_id (primary key), sku (unique), name, list_price, is_active
Payment	The settlement of one order	payment_id (primary key), order_id (foreign key, unique), amount, method, status, paid_at

3. Relationships

From	To	Type	Notes
Customer	Order	one-to-many	One customer places many orders; every order belongs to exactly one customer
Order	OrderItem	one-to-many	An order contains one or more line items; a line item cannot exist without its order
Product	OrderItem	one-to-many	A product appears on many line items; each line item refers to exactly one product
Order	Payment	one-to-one	Each order is settled by at most one payment; a payment belongs to exactly one order

The many-to-many relationship between Order and Product — an order holds many products and a product appears on many orders — is resolved by the OrderItem junction entity, which also carries its own facts: the quantity bought and the unit price at the time of sale.

4. Keys and constraints

• Primary keys: Each entity is identified by its own id column (customer_id, order_id, order_item_id, product_id, payment_id).
• Foreign keys: Order points at Customer; OrderItem points at both Order and Product; Payment points at Order. If an order is removed, its OrderItems are removed with it (they have no meaning alone); customers and products are never deleted when an order is removed, only unlinked at the order level.
• Unique / required: A customer email must be unique; a product sku must be unique; the order_id on Payment is unique, which is what enforces the one-payment-per-order rule. Quantity must be at least one.
• Allowed values: Order status is one of placed, paid, shipped, or cancelled. Payment status is one of pending, succeeded, or failed.

5. Indexes

• Index on Order.customer_id — speeds up "show me all of this customer's orders", the most common lookup.
• Index on OrderItem.order_id — speeds up loading every line item for an order when rendering an order.
• Index on Product.sku — supports fast lookup by the externally referenced product code.

The unit_price on OrderItem is a deliberate denormalization: it copies the product's price at the moment of purchase rather than reading the live list_price, so a later price change never alters the value of past orders.

6. Open questions and assumptions

• Refunds are not yet modelled; a future Refund entity will likely link to Payment.
• This model assumes a single currency. Multi-currency support would add a currency field to Order and Payment.