E-commerce ERD: preserve the transaction, not only the current catalog
An online store is a useful model because it separates several kinds of facts that happen at different times. A customer has a current account profile. A product has a current name and list price. An order records a historical agreement: what was bought, at what price, by whom, and where it was to be delivered.
Start with customers, products, orders, and order_items. One customer can place zero or many orders; each order belongs to one customer. One order has one or more items, and each item refers to one product. Put order_id on order_items, not repeated order columns on every item row.
Keep product_id on an order item, but also snapshot product_name and unit_price. A later price change must not rewrite an old receipt. The duplicated values are intentional because a catalog fact and a transaction fact answer different questions.
Payments and shipments deserve their own tables when they can have an independent lifecycle. A payment may be approved, partly refunded, or retried. A shipment may be created after payment and may cover only some items. A simple first version can use payments.order_id and shipments.order_id; add shipment items when partial fulfillment becomes real.
Normalization: separate facts to prevent anomalies
Normalization is not an instruction to create as many tables as possible. It is a way to prevent one fact from being stored in several places. Repetition causes three familiar failures: an update changes only some copies, a new fact cannot be inserted without an unrelated one, or deleting the last related row accidentally deletes useful information.
First normal form means each value is atomic and repeating groups are rows, not a growing set of columns such as product_1, product_2, and product_3. Second normal form matters with composite keys: attributes must depend on the whole key, not just one part. In an enrollment keyed by student and course, the student name belongs to students and the course instructor belongs to courses.
Third normal form removes dependencies between non-key attributes. If employee determines department code and department code determines department location, location belongs to departments rather than being copied into every employee row. Start from a normalized model; introduce denormalized values only when a measured query need and a clear consistency strategy justify them.
Identifying versus non-identifying relationships
A relationship is identifying when the parent key contributes to the child's primary key. An order line identified by (order_id, line_number) cannot be named without its order, so the order relationship is identifying. A customer id on an order is usually non-identifying: it tells us who placed the order but does not make up the order's identity.
The choice changes constraints as well as notation. An identifying foreign key is normally NOT NULL and belongs to the child primary key. A non-identifying foreign key can still be required; it simply stays outside the primary key. Do not select the type to make the diagram look tidy—ask whether the child can be identified independently of its parent.
Barker and IE notation: use symbols to communicate a rule
Barker notation and IE crow's-foot notation both describe cardinality and optionality. The important question is the same in either system: for one parent row, how many child rows are allowed, and for one child row, is a parent required? A circle means optional, a bar means one, and a crow's foot means many in the usual IE reading.
Notation should expose decisions rather than replace them. If a relationship is optional, explain whether a child can exist before a parent is assigned, or whether the application will create the parent in the same transaction. The database constraint, service validation, and ERD should tell the same story.
Column names: make ordinary queries unsurprising
A consistent convention lowers the cost of reading and joining a schema. Use one case style, such as snake_case, and name primary keys predictably: customer_id, order_id. Foreign keys should reveal both their meaning and target, such as billing_address_id rather than a bare address_id when several addresses are possible.
For booleans, names that read as a question—is_active, has_paid—make conditions clear. For time values, distinguish an event from a period: created_at, paid_at, starts_at, and ends_at. Avoid encoding storage types in names; a type can change while the business meaning should remain stable.
Eight anti-patterns worth reviewing
Look for these warning signs before a model is implemented: comma-separated lists in one column; repeated numbered columns; nullable values used to represent different entity types; a table with no primary key; a foreign key without an index where joins are frequent; money stored as floating point; status values with no documented transitions; and “current” reference data reused to represent past transactions.
None of these is automatically wrong, but each needs an explicit reason. A JSON column can suit genuinely flexible attributes. A nullable relationship can be correct before an assignment happens. Trouble starts when a shortcut silently becomes the only source of a business fact and cannot be queried, constrained, or migrated safely.
Surrogate and natural keys: stable identity versus business meaning
A natural key comes from the domain, such as an ISO country code or an externally assigned invoice number. A surrogate key is created by the system, commonly a numeric id or UUID. Natural keys are convenient when they are genuinely stable and compact. Surrogates are useful when a business value can change, is long, is sensitive, or is not known at creation time.
Often the strongest design uses both: a surrogate primary key for relationships and a UNIQUE constraint on the business identifier. That protects the rule “email is unique” or “external payment reference is unique” without forcing every dependent table to carry that mutable value as its key.
Conceptual, logical, and physical models
A conceptual model uses the business language: customer, order, product, shipment. A logical model adds attributes, identifiers, and relationships without committing to a vendor. A physical model chooses table names, types, indexes, constraints, partitioning, and database-specific behavior.
Do not skip directly from a screen mockup to physical columns. The same word can mean different things at each level. “Customer” may be a person conceptually, an account and profile logically, and several normalized tables physically. Keeping the levels distinct makes requirements changes cheaper because business terms do not become accidentally tied to a particular storage decision.