What a Research Database Is For

A research database is not a record of what happened. It is a record of what we have been able to establish about what happened, together with an honest account of how well we have established it. That second half is the difficult part, and it is what this series is about.

Section 1Two databases that look alike

Consider an airline's booking system. It holds passengers, flights, seats, payments. It has a table of people, with names and dates of birth, and foreign keys linking them to reservations. On a diagram it would look a great deal like the Taiwan Politics database, which holds officials, elections, parties, and the relationships between them.

The resemblance is superficial, and mistaking it for a real similarity is the most common way research databases go wrong.

The booking system has a property the research database can never have: it defines its own truth. If the system says seat 14C is assigned to a passenger, then it is. There is no external fact to be wrong about. A passenger's date of birth enters the system because that passenger typed it in, and if it is wrong, the consequence is an administrative problem, not an epistemic one.

A research database is the opposite. Every row is a claim about a world that exists independently and does not care what we recorded. When the Taiwan Politics database says that a particular legislator was born in 1956, that is not a fact the database creates. It is an assertion, made by someone, on the basis of something, at some point in the past, with some degree of confidence.

The consequence that matters most: in an application database, rejecting bad input is correct behaviour. In a research database, rejecting bad input throws away evidence.

A candidate record that arrives with a mangled birth date is not noise. Somebody transcribed it from somewhere, and a specialist with the right language and the right archive can very often recover what it meant. If the loading process rejects that row for failing validation, that recovery becomes impossible, and the lab has silently narrowed its own dataset to the subset that happened to be tidy at intake.

Section 2Four rows, four problems

Here is the Taiwan Politics database's officials table, as it looked the day its first batch of records was loaded. An official, in this database, is a person who has stood for election, led a party branch, or chaired a legislative body. There are 50,902 of them.

Four rows. Four entirely different problems, none of which a database constraint could have caught.

Row 3021 has a birth year written in Republic of China era notation. 民國45年 is the forty-fifth year of the Republic, which is 1956 in the Gregorian calendar. The value is not wrong; it is correct in a notation the database cannot compute with. Rejecting it would discard a perfectly good fact. Silently converting it would discard the information that the source used era years, which matters when a later source disagrees and someone has to work out why.

Row 3104 has nothing. And here is the difficulty: a null tells us nothing about why there is a null. Did nobody look? Did someone look carefully and find that the birth year is genuinely not recorded anywhere? Is it known but withheld? Those are three completely different research situations, and the database as loaded cannot distinguish them. That failure — the inability to distinguish unknown from not yet examined — is one of the most consequential and least discussed problems in research data.

Rows 3417 and 3418 share a name and differ by two years. 黃建宏 is a common enough name that these could easily be two men. They could equally be one man, entered twice from two sources that disagreed. The database cannot tell. Worse, neither can a rule: this needs a person with access to the sources to look and decide.

Note what has just happened. We have four rows, and we have already identified that the system will need: a way to preserve original notation while adding a computable value; a vocabulary for why a value is absent; and a workflow for a human to adjudicate identity. None of these are things you would build for a booking system.

Section 3What “clean” cannot mean

Everyone who works with data asks, eventually, whether the data is clean. It is a natural question and it is malformed.

Try to answer it for row 3021. Is 民國45年 clean?

• If your standard is faithfully preserve what the source said — yes, perfectly.
• If your standard is every official has a machine-comparable birth date — no.
• If your standard is no field contains free text — no.
• If your standard is the record is attributable to a source — we cannot tell from the row alone.

The row has not changed. The answer changes because cleanliness is not a property a row has. It is a relation between a row and a standard. And since a lab's standards evolve — new questions demand new precision, new sources become available, new collaborators need new fields — the same row can move in and out of "clean" without anyone touching it.

So this system never asks whether data is clean. It asks a much narrower question, repeatedly:

Does this specific record satisfy this specific criterion, as that criterion was defined on this specific date?

Everything in the rest of this series exists to make that question precise enough to answer mechanically, and stable enough that the answer still means something in two years. The chapter on rules and bindings takes it apart in detail.

Section 4Two philosophies

There are two coherent ways to run a research database, and the choice between them shapes everything downstream.

Validate then admit

A row may not enter the canonical tables until it meets the quality bar. Quality lives in the admission gate. The canonical tables are, by construction, trustworthy — anything in them has passed.

This is attractive and it is what most engineering instincts suggest. It has two costs that are easy to underestimate. First, rejected rows have to go somewhere, and that somewhere becomes a second database that nobody maintains — a quarantine that grows, is never processed, and quietly contains a large fraction of the actual evidence. Second, the admission bar has to be defined before you have seen the data, which is exactly when you know least about it.

Admit then audit

A row enters as long as it is structurally representable. Quality lives in a separate, versioned audit layer that detects problems and routes them to people who can resolve them.

The canonical tables are therefore not a promise of quality. They are a complete record of what the lab holds, annotated by a system that knows which parts have been examined and to what standard.

This lab chose admit then audit. That is the right choice for a research setting, for one overriding reason: the alternative silently discards evidence that a specialist could later resolve, and in historical and comparative political research, that specialist very often exists and will arrive two years after the data was loaded.

It also has a risk, stated plainly in the table above and worth taking seriously: a user who finds a value in a canonical table may assume it has been checked. Managing that risk is why the release and codebook discipline in Chapter 9 exists, and why a published dataset must state which rules governed which fields.

Section 5Three consequences

Choosing admit-then-audit forces three design commitments. They look like unrelated technical decisions until you see where they come from.

1. Canonical tables must permit low-quality data

A missing birth year is not a constraint violation. It is a research task. Fields that the audit layer is meant to help complete must be nullable at load time, or the row cannot be admitted, the audit system never sees it, and the whole design collapses back into validate-then-admit by accident.

So when you see that twpolitics.officials.official_birthyear is nullable, and that party_leaders.official_id is nullable, those are not oversights. They are the design working.

2. Constraints enforce representation, never research quality

This is the sharpest and most useful version of the principle, and the codebase states it in a docstring.

The test is simple. Ask of any proposed constraint: could a correct, honest research record fail this? If yes, it belongs in a rule. If no — if failing it means the value is not even representable — it belongs in the schema.

3. There is no “is clean” flag, anywhere

It is tempting to add a boolean to the officials table: birth_verified, say. It would make queries easy.

It would also be wrong within a week. Such a flag goes stale when the source value changes, when a related record changes, when the rule version changes, or when a researcher reopens a decision — and nothing in the database would notice. The system would confidently report verified data that had quietly stopped being verified.

The architecture contract forbids it outright: do not add one persistent per-rule is_violated boolean to domain tables. Current violation state lives in the audit layer, where it can be recomputed, and where the machinery exists to notice when it has gone stale.

Section 6The eight stages

With that settled, here is the shape of the whole system. A row travels through eight stages between a research assistant's spreadsheet and a citable dataset. Each has a different owner and different rules about what may happen to the data.

Two things on that diagram are worth flagging now because they are easy to miss.

Identity resolution is not a stage. It is a band across all of them, because it is the only process in the entire system permitted to change what a primary key refers to. Everything else changes attributes. Merging two officials changes which row a dozen foreign keys point at, and retires an identifier that may already have been published. That needs guarantees nothing else needs, which is why Chapter 8 is devoted to it.

Derivation sits between audit and release. It is easy to imagine going straight from audited canonical rows to a download. But the Taiwan database already has a substantial derived layer — a career-sequence view and a resume-event view that assemble each official's candidacies and leadership positions into an ordered history. Those are computed, not collected, and they follow a different rule: a derived value is never edited, only recomputed after its inputs are corrected.

Section 7Who does what

A system like this only works if the division of labour is clear, because the people involved have very different skills and very different scarcity.

The middle row explains an otherwise odd feature of the design: the enormous care taken over rule definitions, versions, and checksums. A rule is written once and then generates hundreds of research-assistant decisions. If its predicate is subtly wrong, it does not waste an afternoon of engineering — it produces hundreds of careful human judgements answering the wrong question, and those judgements become evidence rows that are expensive to retract.

This gives a working principle that recurs throughout the series:

Be slow about rule semantics and fast about everything else. A wrong implementation is cheap to fix. A wrong specification, once researchers have worked it, is not.

The next chapter takes up the first practical decision: how to design the canonical schema, who owns it afterwards, and what to do when the audit layer inevitably demands columns you did not anticipate.