Designing a relational schema

What a normalised design looks like

Imagine a list of purchases containing the customer’s current name on every row. It is convenient to read: each purchase comes with the name beside it. But if the customer corrects their name, every copy must change. An ordinary correction has become a search for all the places where we repeated the same fact.

A normalised design gives that current name one home, in a customer record. Orders store the customer’s ID. When we need an order with its customer’s name, a query matches the ID to the customer record. We still get the combined answer; we no longer maintain several copies of the name to get it.

Some repetition remains. A customer ID appears on many orders, and that is useful: each occurrence records which customer placed that order. Normalisation does not remove every repeated value. It separates facts with different owners while keeping the references that connect them.

The benefit becomes clearer when we do more than read. We can register a customer before they buy anything. Deleting an order need not delete the only record of that customer. Correcting a name requires changing the customer, without rewriting their purchases. These operations correspond to different facts, and the schema lets us treat them that way.

A poorly arranged table makes those operations interfere. A correction leaves conflicting copies, an insertion needs an invented purchase, or a deletion accidentally removes customer information. These are called update, insertion and deletion anomalies. They are useful symptoms to recognise when reviewing a schema: a simple change should not require an elaborate effort to preserve unrelated information.

To decide where each fact belongs, however, we need more than the advice to avoid duplication. We need to know what the records represent.

Choosing the records

Begin with the things the application must remember and the operations it must support. A shop needs to list products before they sell, register customers, accept orders with several items, and reproduce the amounts agreed for earlier purchases. Those needs suggest customers, products and orders, but we still have to decide what one row of each means.

An order is the purchase as a whole. An order line is one entry within it: a particular product, a quantity, and an agreed unit price. A purchase of two identical mugs can be one line with quantity two. A purchase of a mug and a bowl needs two lines. Both lines belong to the same order.

We’ll use customer C4, Ada, and two products: P7, the Blue mug, and P8, a Bowl. Order O12 contains two mugs at €18 each. A later order, O13, contains one mug at €20 and one bowl at €24. Both orders belong to C4. The shop sells only in euros, and the short IDs are labels for the example rather than a suggested ID-generation scheme.

Each kind of record needs a reliable identity. A product name is a poor choice: two products might share a name, and a rename should not break earlier orders’ references to the product. The assigned ID P7 lets the Blue mug become the Large blue mug while remaining the same product. Ada can also change her name without becoming a different customer.

A candidate key is a column, or combination of columns, that uniquely identifies a row and needs every column in that combination to do so. A table can have more than one. A product might have both an assigned ID and a unique catalogue code. We choose one candidate as the primary key; the other uniqueness rule still matters.

For an order line, we choose (order_id, line_no). Neither column works alone: O13 has several lines, and many orders have a line 1. Together they identify one entry. This is a composite key.

We could choose (order_id, product_id) instead, but that would allow a product only once per order. Our shop permits separate lines for the same product, perhaps at different agreed prices. A key is a statement about what may exist in the application, so that distinction is worth settling before writing SQL.

An assigned ID alone does not settle the rest of the design. We could add a unique row_id to a muddled purchase table and still repeat all the customer details. Every row would have an identity, but correcting a customer would remain difficult. We also need to understand which facts belong to that identity.

Which facts belong together?

Knowing an order ID tells us which customer placed it. We have decided that each order has exactly one customer, so two rows describing O13 cannot legitimately disagree about its customer. Knowing only the customer ID does not identify an order: C4 placed both O12 and O13.

This one-way relationship is a functional dependency. We write order_id → customer_id, meaning that an order ID determines its customer ID. For current customer information, customer_id → name. For catalogue information, product_id → name, current_price.

“Determines” does not mean “never changes.” Ada’s name can change. It means that in any valid state of this database, C4 has one current name. Nor does a dependency follow just because a small sample happens to contain unique names. It comes from the rules of the application: another customer named Ada must still be allowed.

The line’s quantity and agreed price depend on the combination (order_id, line_no). Product P7 alone cannot tell us either value: O12 bought two mugs at €18, while O13 bought one at €20. The order alone cannot tell us either value when it contains several lines.

These dependencies give us a reason to keep some columns together and move others apart. We’ll use them to transform a combined purchase record into tables whose rows each have a clear meaning. The diagrams show selected columns to keep that movement visible; the same reasoning applies to the other fields.

Normalising the schema

Normal forms describe properties of a table and its dependencies. They give us a way to examine a design rather than judge it by how tidy it looks. The first three are a useful starting point for ordinary application schemas. Each builds on the previous one.

First normal form: rows for the repeated records

An order form can contain a list of items. If we store that whole list as a nested group inside one purchase row, the individual lines are not ordinary rows that the relational model can identify and connect. We’ll give each line its own row, with a single value of the chosen kind in each field. This is the arrangement described by first normal form.

O13’s mug and bowl become two rows, identified by O13 with line 1 and O13 with line 2. The product, quantity and price stay together on their respective lines. We can add a third line without changing the table definition, and a query can address one line without unpacking a list first.

Fixed columns such as product_1, product_2 and product_3 are another tempting design. Those scalar columns can satisfy first normal form, but they still make poor order lines: a fourth item requires another column, and searching all items means checking every slot. The need for an arbitrary number of similar records is a strong reason to use rows.

This does not mean every value must be broken into its smallest conceivable pieces. A product name contains words, but we do not need a table of those words to store a name. The useful unit follows from what the application needs to identify, relate, validate and query. Nested JSON has legitimate uses too; Tables and JSON examines that choice in more detail.

We now have a flat table of purchase lines. If we copied the whole order form onto each row, it still contains repeated order and customer information. First normal form has made the lines usable as rows; it has not resolved where their other facts belong.

Second normal form: the whole key

In that combined table, the key is (order_id, line_no). The quantity needs both parts. The customer ID does not: all lines in O13 belong to C4 because O13 belongs to C4. The order’s placement time also depends on the order ID alone.

Copying those fields onto every line makes one order fact appear in several places. Correcting which customer owns O13 would require changing all its lines. If one remained C4 while another became C5, which customer would own the order?

We make an orders table keyed by order_id and move the order-level fields there. The lines retain order_id as their reference. Now one order row supplies the customer and placement time for all its lines.

Second normal form requires first normal form and removes dependencies of non-key fields on only part of a candidate key. Here, separating orders from lines removes that partial dependency. The line number still matters for line-specific facts; the order facts no longer pretend to be line-specific.

When a table has only single-column candidate keys, there is no smaller part for a partial dependency to use. That does not make its design finished. Our new orders table can still repeat Ada’s current name on every order.

Third normal form: facts about another record

Suppose the orders table contains order_id, customer_id, and customer_name. The name depends on the order indirectly: the order identifies C4, and C4 identifies Ada. We can write that chain as order_id → customer_id → customer_name.

O12 and O13 therefore repeat the same customer fact, even though each order row has a perfectly good key. We give the current name a home in customers, keyed by customer_id, and leave the customer ID on each order.

The purchase lines have the same problem if they contain the current product name and catalogue price. Those fields depend on product_id. P7’s name belongs in products, where every line referring to P7 can find it. The quantity and agreed price stay on the line because they describe that purchase entry.

These moves remove the indirect, or transitive, dependencies that violate third normal form in our example. A useful practical question is: does this field describe this row’s identity, or some other record that the row refers to? The current customer name describes C4, however many orders C4 places.

We have arrived at four tables: customers, products, orders and order lines. A customer and a product can now exist before either appears in an order. Deleting the last line that mentions P8 no longer removes the bowl from the catalogue. Changing Ada’s current name changes one customer record. The earlier anomalies disappear because we separated the facts that were getting in each other’s way.

Keeping the connections

Splitting tables only helps if we preserve how their records relate. Each order keeps a customer_id; each line keeps an order_id and product_id. A foreign key can require those references to identify existing rows. We’ll implement those rules in the next article.

The connections describe the shape of the application. One customer can have many orders, while each order has one customer: a one-to-many relationship. Orders and products have a many-to-many relationship: an order can contain several products, and a product can appear in several orders. Order lines connect them, while recording the quantity and price of each occurrence.

The split also lets us reconstruct the combined information. Start with line 2 of O13. Its product ID P8 finds exactly one product, the Bowl. Its order ID O13 finds exactly one order, whose customer ID C4 finds exactly one customer, Ada. The line still has its quantity of one and agreed price of €24. None of that information needed to be discarded when we separated the tables.

A decomposition is called lossless when joining the pieces reconstructs the original relation without losing rows or inventing combinations. The retained keys are what make our splits work. If we separated agreed prices from products but kept only order_id, O13’s €20 and €24 would have no reliable connection to its mug and bowl. Joining by the order would produce four matches, including the mug at €24 and the bowl at €20. Keeping the complete line identity preserves that association.

Similarly, the customer side must contain only one row for C4. If it contained two, each matching order could produce two joined rows. The identity rules do more than label the diagram: they are part of why joining the pieces gives the intended answer.

Normalisation does not choose these business rules for us. We have chosen registered customers and one customer per order. Guest orders, shared ownership, product variants and returns would introduce further decisions. For a new application, the same reasoning begins by establishing what a record means and which dependencies actually hold.

Current facts and historical facts

One pair of similar fields survived all this separation: the product’s current price and the line’s agreed price. It may look as though we forgot to remove a duplicate. But the values only happen to match when a purchase uses the current catalogue price.

O12 bought two mugs at €18 each. When P7’s catalogue price becomes €20, that order must still total €36. If the receipt used today’s catalogue price, a correct join would produce a historically wrong €40 receipt. The database could not recover an agreed price it never recorded.

The two prices answer different questions. The current offer depends on the product; the accepted price depends on the purchase line. Keeping both is compatible with a normalised design. Deciding what a field means comes before deciding whether it duplicates another field.

An address needs the same care. A customer’s saved address can change after delivery. An order that must remember where it was sent needs the address used for that delivery, perhaps as an order snapshot. The customer reference can remain useful for finding orders without making old deliveries follow every address-book edit.

Product names also require a decision. Our example retrieves the current name through the product ID. If a receipt must reproduce the name printed at purchase, the schema needs a purchased-name field or another historical record. An ID preserves a reference; it does not freeze the values behind that reference.

Storing historical facts makes it possible to retain them. It does not make them immutable. Application operations, permissions and any required audit trail must still govern later corrections.

How much separation?

After seeing how useful these splits are, it is easy to keep going. We could put each product’s name in one table, its current price in another, and join them by product ID. But both facts already depend on the same product identity. That split removes none of the dependencies we have been trying to fix.

Without another reason for separating them, creating a product now requires coordinating several rows, and reading its ordinary details requires assembling them again. We have added opportunities for incomplete records and extra work for queries. There can be good reasons for a one-to-one split—different permissions, lifecycles, or a large rarely read value—but the number of tables is not a measure of normalisation.

Too little separation tends to show up as repeated corrections and contradictory answers. Unnecessary separation tends to show up as ordinary operations navigating fragments that always belong together. Both are reasons to revisit what the rows represent. A schema should explain the application more clearly as it develops.

A sound normalised design can still be inconvenient for a particular read workload. A dashboard might repeatedly calculate totals over millions of order lines. Storing a per-day summary can avoid repeating that work. This is denormalisation: deliberately retaining redundant or derived information to serve an access need.

The summary brings a responsibility with it. If a line changes, how does the summary change? One option is to update both in the same transaction. Another is to rebuild or refresh the summary, accepting that it can be behind the source data. The appropriate choice depends on what the reader of that summary is allowed to see.

Keep the authoritative facts clear. In this example, order lines determine the totals; a summary is a result we can reconstruct from them. If both the lines and summary can be edited independently without a reconciliation rule, we have reintroduced the uncertainty normalisation helped remove.

Joins alone are not evidence that denormalisation is needed. Before changing the stored facts, examine the slow query, its indexes, the amount of data it reads and how often the answer is needed. A measured bottleneck gives us something specific to improve and a way to tell whether the additional maintenance is worthwhile.

Choosing data types

So far we have decided which table each piece of information belongs in. We also need to decide how the database should represent it. A data type defines the values a column can hold and the operations available on them. A quantity stored as a number can be added to another quantity. Text holds sequences of characters, called strings. A name stored as text can be searched or combined with another string.

This is more than a label on a field. The characters “10” and the number 10 can look identical on a screen, but comparing text is different from comparing quantities. For example, under PostgreSQL’s simple "C" text ordering, “10” comes before “2”: their first characters decide the order. As numbers, 2 comes before 10. Storing quantities as text means converting them back into numbers whenever we need numeric comparisons or arithmetic, and deciding what happens when a writer has stored “ten” instead.

Some values made entirely of digits are still better understood as text. A postal code identifies an area; it is not an amount to add up. Converting “00123” to an integer loses its leading zeroes. Choose according to what the value means and what we need to do with it, rather than how it happens to look.

Numbers, text and true-or-false values

For our shop, quantity integer stores a whole-number count. Integer types have finite ranges; PostgreSQL’s bigint offers a larger range than integer when we need it. A count of mugs and a length of fabric have different needs, even if both begin with the value 2. The length may need a fractional part.

Exact decimals, such as PostgreSQL’s numeric, let us represent decimal amounts without approximating them in binary. Our euro prices use numeric(12,2): up to twelve digits in total, with two after the decimal point. An input of 18.005 is rounded to 18.01. The type does not preserve the extra fraction, so we must choose the scale to suit the amounts we actually need.

Floating-point types, such as double precision, represent a wide range of values using a fixed amount of storage. Some decimal values can only be approximated in this format. That can suit measurements and scientific calculations where the error is understood; it needs care when comparing results for exact equality. For our prices, exact decimals make the intended arithmetic easier to express. They still leave us to choose a rounding policy for calculations such as tax.

text stores character strings, including names and codes. Comparing strings also involves a collation: rules for their ordering, such as how letters with accents are treated. The illustration uses a simple ordering to make the distinction visible; a database can use language-specific rules instead.

A boolean represents a true-or-false value. It could describe whether a product is currently offered for sale. It is less helpful for an order that can be awaiting payment, paid, dispatched or cancelled. Those are several named states, and squeezing them into an is_complete flag would discard information the application needs.

Dates and times

A calendar date and a moment in time answer different questions. A date suits a birthday or a delivery day: there need not be an hour or time zone. To record when a purchase happened, we need an instant that readers in different places can recognise as the same event.

PostgreSQL’s timestamp with time zone, also called timestamptz, serves that purpose. 2026-10-04 09:00+00 and 2026-10-04 11:00+02 describe the same instant. PostgreSQL converts the input to UTC, a shared reference time, and displays it in the time zone configured for the database connection. It does not retain the original zone name or offset. That fits our order’s placed_at.

A timestamp without time zone holds a date and clock time without assigning them a zone. “9 am next Tuesday in Stockholm” needs both the local appointment time and its location’s time-zone rules to determine an instant. For future appointments or recurring schedules, preserving that local meaning may matter. A timestamp chosen simply because its name sounds more complete will not decide this for us.

Units and missing values

The type cannot supply every part of the meaning. A field called weight leaves us guessing whether its numbers are grams or kilograms. weight_g records the unit in the name. Our price columns assume EUR; a design supporting several currencies must record which currency an amount belongs to. Keeping the currency separately does not, on its own, make amounts in different currencies comparable.

SQL NULL marks a missing value. It is different from zero, an empty string or false, each of which is a value we actually know. A missing delivery date might mean “not delivered yet,” “date unknown,” or “delivery does not apply.” If those distinctions affect a report or operation, a nullable date alone is insufficient. A delivery state can express the distinction, with a date required for the states that have one.

A type is the first part of the field’s definition, not its complete rule. An integer can represent minus two, but our purchase quantity must be positive. Text can represent P99 without proving that product P99 exists. Constraints adds those requirements. Together, the type, the unit and the rules tell a writer what the field means and what it may contain.

Trying the design

Before committing to a schema, work through representative records and operations. Our design lets us create an unsold product, correct one customer name, and remove an order without accidentally erasing catalogue information. It also records the facts needed to reproduce the agreed amount of a purchase.

To run the SQL yourself, start the shared practice setup. It explains opening PostgreSQL, loading the files and choosing a fresh database for this article.

You can download the practice schema and records to try the examples in an empty PostgreSQL database. The file contains the four tables and both orders. This query follows O12’s line to its current product name, then calculates the amount from the line’s agreed price:

In the query, AS l gives order_lines the short name l, and AS p names products as p. So l.unit_price means the agreed price on the line, while p.name means the product’s name. Multiplying the line’s quantity by its price gives 2 × €18; AS line_total names that calculated output. ORDER BY l.line_no puts the receipt lines in line-number order. Queries and joins develops this syntax and how to reason about its results.

SELECT l.line_no, p.name, l.quantity,
       l.unit_price, l.quantity * l.unit_price AS line_total
FROM order_lines AS l
JOIN products AS p ON p.product_id = l.product_id
WHERE l.order_id = 'O12'
ORDER BY l.line_no;

The result combines information from separate tables while preserving what belongs to each. It uses today’s product name, the purchased quantity and the agreed price. That is the combination we chose to retain. If we needed the original printed product name as well, the test would expose a missing historical fact.

We calculate the line total because it follows from quantity and unit price in this example. A real invoice may include discounts, taxes and prescribed rounding; those meanings need to be established before deciding which amounts to calculate and which to preserve.

A useful review of another schema follows the same questions. What does each row represent? What identifies it? Which facts depend on that identity, and which describe something it refers to? Can we change one fact without accidentally changing another, and reconnect the records without inventing information? The normal forms help us answer those questions systematically.

The remaining job is to make the database enforce the decisions. Every line should identify a real product, quantities should be positive, and a line number should identify only one line within an order. Constraints turns those intentions into rules every writer must respect.