Relational Archive System: a flat spreadsheet turned into an archive you can search and count

The problem

The client's entire archive lived in a single flat spreadsheet (33 columns, roughly 2,030 rows), with 5.7GB of image scans held somewhere else entirely and nothing connecting the two.

Most of the trouble came from how the sheet was laid out. It had one row per catalogued item, so every image's details were repeated down a block of around eight rows, and nothing could be counted without double-counting it. Every field was free text, so the same thing existed under several spellings at once. One column alone held nearly 400 raw values that turned out to describe a much smaller set of real things.

The relationships were the whole point of the archive, and none of them were stored. They were implied by rows sitting near each other, so any question spanning two dimensions had to be answered by reading the sheet by eye. And because the images had no link back to the rows describing them, the same scan could exist twice under different filenames and nothing would notice.

There was one big constraint too. The person who'd run this long-term isn't a database expert, so whatever replaced the spreadsheet had to be close to impossible to break.

The approach

I modelled the archive properly and didn't just tidy the sheet, because tidying it wouldn't have fixed how it was built.

The big early decision was to use one table for every catalogued item, with fields that change depending on its category. A separate table per category would have been a closer match to how the client described the material, but every total would then have been a sum across five places, and every new category would have meant a schema change. With one table, a count is one rollup.

The schema itself is code (a script that can be re-run safely, which builds the tables through Airtable's metadata API), so the structure can be rebuilt and reviewed. Otherwise it would've been something assembled by clicking and impossible to reproduce.

I also did the work in order, with the structure first, then data entry, then the dashboards. Building the dashboards before the model was sound would have meant rebuilding them.

What I built

A linked model of eight tables replacing the flat sheet, with the relationships actually stored.

A guarded entry workflow. It's a single guided screen where the parent record carries over automatically, so a child record can't be attached to the wrong parent. Pickers search before they offer to create anything, so people reuse what's already there and don't accidentally create a duplicate. Fields only show up when they apply to the category, so invalid combinations can't be entered at all. It shipped with a written guide, a cheat sheet and an in-app help page, because whoever runs it has to be able to manage without help.

A three-stage import pipeline (fetch, transform, import) that took the legacy spreadsheet into the new model. It's a dry run by default and only writes when you commit, with an audit log and a reconciliation manifest, so if a run would go wrong, you find out before anything changes.

The source had no identifiers, so the obvious key was the image filename, but one recurring filename turned out to cover 19 distinct images. Keying on it would have collapsed them into one record. So the importer fingerprints each attachment by size and dimensions as well as its filename. That splits the real collisions, and still merges the rows that really are several items in one image.

Dashboards and profile pages, with live relationship counts on every record, four profile page types and a single filterable dashboard.

A data-quality system. A scanner runs weekly across three record types and catches near-matches, like typo variants of the same entry and images re-uploaded under a different name. It feeds review pages whose count tiles read zero when the archive is clean, so you can see there's no work to do.

The outcome

1,495 records were loaded across two automated passes with zero manual entry. The archive now holds 1,545 images and 2,275 catalogued items in one linked model, against a source that couldn't be counted at all without double-counting.

The relationships that were the whole value of the collection can now be searched and counted. The live counts on each record are something the spreadsheet could never do, and they're why a question that used to mean reading by eye is now a filter.

Two things are still open. Around 347 items still need a person to set a category by hand, because the source described them with umbrella terms the specific value can't be recovered from, and the importer flags those and doesn't guess. And the weekly scanner works on record data, not file metadata. Catching a duplicate image by what's in it is a separate piece of work that hasn't been built.

I think the entry workflow is the part that'll matter longest, because it's what stops the archive drifting back into the same mess as people add to it by hand.