Database modeling and ER diagrams

From nouns to entities

Customer, order, and product can be entities. Address may be an attribute or an entity depending on its own identity and behavior.

• Primary key: stable identity.
• Foreign key: explicit relationship.
• Cardinality: one-to-one, one-to-many, or many-to-many.

A many-to-many relationship

An order has many products and a product appears in many orders. The join table also stores quantity and historical price.

CREATE TABLE order_items (
  order_id UUID REFERENCES orders(id),
  product_id UUID REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Normalize with judgment

Normalization reduces contradictions. Denormalization can speed known reads. The decision needs a real case and a consistency strategy.