7 Basic Relational Database Design

Start with requirements and relationships

A relational design starts with the information a system must remember and the rules that information must follow. For an online shop, the requirements include storing customers, products, orders, and the products and quantities in each order. They may also require preserving the price charged at purchase time.

Identify the important entities, such as customers, products, and orders, and describe how they relate. One customer can place many orders, while each order belongs to one customer. An order can include many products, and a product can appear in many orders. This is a many-to-many relationship, which requires an associative table such as order_items.

Make assumptions explicit before choosing the design. In this example, each product appears at most once in an order, and its quantity is recorded on that order line. If repeated lines for the same product were allowed, each line would need its own identifier.

Choose tables, keys, and constraints

A table represents one kind of entity or association; a row represents one instance, and a column represents an attribute. Keep facts with the entity they describe: for example, store a customer’s email in customers, rather than repeating it on every order.

Every table needs a Primary key that uniquely identifies each row. A Foreign key connects a row to a valid row in another table. When a relationship row is identified by a combination of columns, that combination can be a Composite key.

Use constraints to enforce business rules in the database:

• NOT NULL requires a value.

• UNIQUE prevents duplicate values or combinations.

• CHECK limits values to an allowed range or set.

• PRIMARY KEY requires a unique, non-null row identifier.

• FOREIGN KEY requires a valid reference to another table.

For example, a foreign key can prevent an order line from referring to a nonexistent product, while a check constraint can require a positive quantity. Constraints are especially useful when data can be inserted or updated by different parts of an application.

Normalize to reduce avoidable duplication

Normalization organizes data to limit avoidable duplication and the update problems it can cause. A practical introductory goal is third normal form:

1. First normal form (1NF): Each cell contains one value of the column’s kind. Avoid putting a list of products into one order field or creating repeated columns such as product1, product2, and product3.

2. Second normal form (2NF): The table is in 1NF, and each non-key attribute depends on the whole key. In an order-line table identified by order ID and product ID, the quantity belongs to that combination.

3. Third normal form (3NF): The table is in 2NF, and non-key attributes depend on the key rather than on other non-key attributes. Store a customer’s email with the customer, not as a repeated copy in each order.

Normalization does not eliminate every repeated value. Product IDs repeat across order lines because those lines refer to the same product; the foreign key makes that relationship explicit. The order line’s unit_price is intentionally stored as a historical snapshot, while products.current_price records the current price.

Build the shop schema

The following PostgreSQL schema applies the stated assumptions. GENERATED ALWAYS AS IDENTITY is PostgreSQL-specific; other database systems may use different syntax for generated keys.

The customer, product, and order IDs identify their respective rows. Each order references one existing customer. The order_items table resolves the many-to-many relationship; its composite primary key ensures a product appears at most once in an order.

Deleting an order cascades to its order lines. Deleting a product referenced by an order line is blocked unless the references are dealt with, which helps preserve purchase history. Deletion rules should reflect the application’s retention requirements. NOT NULL matters alongside CHECK: a check requiring a positive quantity does not, by itself, require the quantity to be present.

Calculate, review, and test

The recorded total for each order can be calculated from its lines using quantity and historical unit price:

This design does not store the derived total, avoiding disagreement between a separately stored total and the order lines. If a requirement calls for storing totals for performance or audit reasons, define how those totals will be kept consistent.

Before implementation, check that the design can store every required fact without lists packed into fields or repeated groups of columns. Confirm that every table has a clear primary key, relationships use appropriate foreign keys, and constraints capture required, distinct, and valid values. Also verify that an order retains its original price when the product’s current price changes, and that deletion rules protect information that must remain.

Test typical questions, such as which products are in an order, and boundary cases such as zero quantity, duplicate SKU, an unknown customer, or deletion of a product referenced by an order. Related tables can be combined with joins to answer questions about their connected records.

Takeaway: Translate requirements into entities, attributes, and relationships; identify rows with keys; enforce rules with constraints; reduce unnecessary duplication; and test the design against realistic queries and changes.