Designing Database Tables From a Messy Excel Sheet

A spreadsheet is a view, and a database is a model. Someone's sheet has twelve columns that make sense when you read across the row, and an engineer asked to put it into a database often creates a table with the same twelve columns. That reproduces the sheet's problems exactly and adds the inconvenience of a server. The better move is to design the tables from what the sheet describes. This post gives a method for doing that with an example, a checklist of the defects to look for and tests for the result. It is a spoke of when a spreadsheet has become a database.

The example sheet

A workbook tracks equipment loans:

It works for a while. It also has all the usual faults.

Step 1: name the things

Read the columns and ask what real-world things they describe. This sheet has:

• People who borrow: name, email, department, phones.

• Items that are lent: name, type, value.

• Loans, the event linking a person and an item at a time: date out, due, returned.

• Departments and item types, which are lookups.

One row mixes four or five subjects. The same borrower appears on two rows with their details repeated, and Laptop 12 is on two rows with its type and value repeated. Whenever the same facts about a thing appear on more than one row, that thing wants its own table.

Step 2: choose the keys

Every table needs a key, a column or set of columns that identify one row and never change or repeat. The choice matters because everything else refers to it.

• Natural keys come from the real world: an email address, an asset tag, a serial number. They are meaningful and sometimes change. An email changes when someone marries or the company renames its domain.

• Surrogate keys are generated: an integer identity column or a UUID. They have no meaning and never change. Most designs use a surrogate primary key and keep the natural key as a UNIQUE constraint.

Names make poor keys. A. Ng is not unique, is spelled in several ways and changes. For the sheet: borrowers.id as a surrogate, email unique; items.id with asset_tag unique; loans.id as the loan number L1 or a generated one.

Step 3: remove repeated values and groups

Two patterns signal missing tables.

Repeated values. Borrower email and Dept repeat on every loan by the same person. Store them once on the borrower, and reference the borrower from the loan. Same for the item's type and value.

Repeated column groups. Phone 1 and Phone 2 are a repeating group: a person may have any number of phones, and the sheet allows two. A third phone has nowhere to go, and searching for a number means searching two columns. The fix is a child table with one row per phone (turning repeating columns into a proper child table).

CREATE TABLE departments (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);
CREATE TABLE borrowers (
    id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    text NOT NULL,
    email   text NOT NULL UNIQUE,
    dept_id integer NOT NULL REFERENCES departments(id)
);
CREATE TABLE borrower_phones (
    borrower_id integer NOT NULL REFERENCES borrowers(id) ON DELETE CASCADE,
    phone       text NOT NULL,
    PRIMARY KEY (borrower_id, phone)
);
CREATE TABLE item_types (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);
CREATE TABLE items (
    id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    asset_tag  text NOT NULL UNIQUE,
    type_id    integer NOT NULL REFERENCES item_types(id),
    value      numeric(10,2) NOT NULL CHECK (value >= 0)
);
CREATE TABLE loans (
    id          text PRIMARY KEY,
    borrower_id integer NOT NULL REFERENCES borrowers(id),
    item_id     integer NOT NULL REFERENCES items(id),
    date_out    date NOT NULL,
    due         date NOT NULL CHECK (due >= date_out),
    returned    date CHECK (returned >= date_out)
);

The value column sits on the item, not the loan, because it describes the item. If the value at the time of the loan matters for accounting, add a separate column on the loan and call it what it is.

Step 4: drop what is calculated

Days out is returned - date_out. It can be derived at any time and goes stale as soon as the date changes, and a stored copy can disagree with the dates it came from. Do not store it. Compute it in a query or a view:

CREATE VIEW loan_status AS
SELECT l.id, b.name AS borrower, i.asset_tag,
       COALESCE(l.returned, CURRENT_DATE) - l.date_out AS days_out,
       l.returned IS NULL AND l.due < CURRENT_DATE     AS overdue
FROM loans l
JOIN borrowers b ON b.id = l.borrower_id
JOIN items i     ON i.id = l.item_id;

Totals, ages, statuses and running balances are the usual derived columns. Keep the facts and derive the rest. The exception is a figure that must be frozen at a point in time, such as an invoice total, which is itself a fact about that moment.

Step 5: add the rules

A sheet cannot say "a due date is not before the date out". A table can, as the CHECK constraints above do. Go through each column and ask: can it be empty, what values are valid, does it refer to another table, must it be unique. Write the answers as NOT NULL, CHECK, REFERENCES and UNIQUE. Every rule you encode is a mistake that cannot enter the database. Do the same for categorical columns: a lookup table, or an enum-like CHECK (status IN ('open','closed')), in place of free text.

Step 6: test the design

A design that looks good can still fail in use. Two tests find most problems.

Rebuild the sheet. Write a query that joins the tables back into the original shape and compare it with the sheet:

SELECT l.id AS loan, l.date_out, b.name, b.email, d.name AS dept,
       i.asset_tag, t.name AS item_type, i.value, l.due, l.returned
FROM loans l
JOIN borrowers b ON b.id = l.borrower_id
JOIN departments d ON d.id = b.dept_id
JOIN items i ON i.id = l.item_id
JOIN item_types t ON t.id = i.type_id;

If the result reproduces the sheet (minus the derived and repeated columns), nothing was lost. If a column is missing, you dropped information.

Try to break it. Imagine the operations people will do and walk through them. Add a borrower with three phones: works. Change a department name: one row to update, not hundreds. Delete a borrower with loans: the foreign key stops it, or the history is lost, so decide which you want. Add a loan for an item that does not exist: rejected. Two people borrow the same item on the same date: allowed, which may be wrong, so add a rule or an exclusion constraint if an item can be out only once at a time. Each answer is a design decision to confirm with the people who use the data.

Common defects to look for

A cell holding several values deserves attention: it prevents searching, joining and counting. Splitting it is a data-cleaning and a design step (splitting full names and addresses in Excel data discusses the limits).

Do not over-design

Normalization has a point of diminishing returns. A table for every attribute is harder to use than a slightly redundant one. Aim for the point where each fact is stored once and each table is about one thing, and then stop. A reporting table that deliberately duplicates data for speed is fine if it is built from the normalized tables and rebuilt on a schedule. The goal is a design the team understands and can maintain.

When a normalized design is not the goal

The data is analytical and read-only. A wide, denormalized table suits a report or a data warehouse, and the source tables hold the normalized form.

The sheet is a one-off import. If you will load it once, analyze it and discard it, a staging table with the original shape is enough.

The structure is genuinely flexible. Records whose fields vary widely may fit a JSON column or a document store better than dozens of nullable columns.

You do not yet know the questions. Design for what the data is, not for one report. If you do not understand the domain, ask the people who do before drawing tables.

PostgreSQL documents the constraint syntax in its manual on table constraints, and Microsoft's page on database normalization basics explains the rules in plain terms. To convert a small extract to JSON, use the CSV to JSON converter.