From Rows to Records: How to Think About Your Data Like an Application

Open your team’s client tracker and look at row 47. It’s Brightwater Dental. Columns A to H describe the practice. Columns I to N describe the main contact. Columns O to AJ describe their three projects, squeezed in side by side as “Project 1 Name”, “Project 1 Status”, “Project 2 Name” and so on. When they signed a fourth project last month, someone added a note in the comments.

That’s not a tracker. That’s three or four different things crammed into one row because the sheet only gives you one shape to work with. Learning to separate them is the single most useful skill you can bring to a tooling project, and it doesn’t require writing any code.

Three words you need

Engineers use a lot of jargon here, but you only need three terms.

An entity is a type of thing you want to keep track of. Clients, contacts, projects and invoices are entities. If you’d naturally talk about “a list of them”, it’s probably an entity.

A field is a piece of information about an entity. A client has a name, an industry and an address. A project has a start date, a budget and a status. In spreadsheet terms, fields are your columns.

A relationship is how two entities connect. A client has many projects. A project has one client. A contact belongs to a client, but might work on several projects.

Every record is one instance of an entity: one specific client, one specific project. In a well-designed tool, each record lives in exactly one place, and other records point to it rather than copying its details.

Spotting the entities hiding in your sheet

The easiest way to find entities in a messy sheet is to look for repetition and numbered columns.

Numbered columns are the clearest giveaway. “Project 1”, “Project 2”, “Project 3” means you have a separate entity, Projects, that’s been flattened into the client row. The same goes for “Contact 1 Email” and “Contact 2 Email”.

Repeated values are the other clue. If the same client name, address and account manager appear on twelve rows because the client has twelve orders, the client details belong in their own entity. The orders are another.

Columns that only apply sometimes hint at entities too. If “Renewal Date” is only filled for clients on a retainer, that might be a property of a contract, not of the client.

Running through Brightwater’s row with these clues gives us four entities:

Drawing the relationships

Now work out how they connect. For each pair, ask: “Can one of these have many of the other?”

• One client can have many contacts. Each contact belongs to one client.
• One client can have many projects. Each project belongs to one client.
• One project can have many invoices. Each invoice belongs to one project.
• One contact can work on many projects, and one project can involve many contacts.

That last one is called a many-to-many relationship, and it’s exactly the kind of thing spreadsheets can’t represent cleanly. Your sheet would either repeat contacts on every project row or cram names into a single comma-separated cell. Most internal tools handle it with a linking field that lets you pick several contacts on a project.

Sketch this as boxes with lines between them on a single page. It doesn’t need to be pretty. That sketch is your data model: the plan for how your information is structured. It will be more useful to whoever builds your tool than any requirements document.

Why this matters in practice

Splitting data into entities isn’t tidiness for its own sake. It fixes real problems.

Updates happen once. When Brightwater moves office, you change the address on one client record. Every project and invoice linked to it shows the new address automatically.

Nothing has a ceiling. Brightwater’s fourth project is just another project record. Nobody needs to add “Project 4” columns or squeeze notes into comments.

Questions get easy. “Show me all overdue invoices for clients managed by Sana” is a simple filter across linked records. In the old sheet, it’s an afternoon with lookup formulas.

Typos stop multiplying. You pick the client from a list rather than typing their name. “Brightwater Dental” and “Brightwater Dentl” can’t both exist.

Choosing fields well

Once you have your entities, think about each field’s type. A field type tells the tool what kind of data is allowed, which is how you stop dates turning up as text.

Common types include text, number, date, a single choice from a fixed list, multiple choices, a yes/no checkbox, and a link to another record. A few rules of thumb:

• Use a fixed list wherever you can. Status, industry and region should be picklists, not free text. This is the cure for colour-coded cells.
• Store one thing per field. “Sana / Marcus” in an account manager field breaks every filter you’ll ever run.
• Don’t store what you can calculate. If a project’s total invoiced amount is the sum of its invoices, let the tool add it up rather than typing it in.
• Give every entity a clear name field. It’s what people will see when they link to a record, so make it unambiguous.

Don’t over-design

It’s tempting to model everything. Resist. Start with the entities your process actually uses this month, not every one you might want someday. A small team can run happily on clients, contacts and projects for a year before invoices need to live in the same tool.

You can add entities later. Untangling an over-built model that nobody understands is much harder.

Where to start this week

1. Pick your messiest sheet and highlight every numbered or repeated column group. Each group is a candidate entity.
2. Write your entity list with five to eight key fields each, using the table above as a template.
3. Sketch the relationships on one page, labelling each line “one” or “many” at each end.
4. Mark every free-text field that should be a picklist, and list its allowed values. You’ll need this when you reach Cleaning Your Spreadsheet Data Before Migration: A Practical Guide.