People by US state data: sources, fields and accuracy checks

A table of people and states looks like the easiest thing anyone could build. One row per person, one column for the state, done. The difficulty arrives the first time two sources disagree and somebody has to decide what that cell now holds.

This page is about the shape of the file rather than the research behind it. A good shape turns disagreement into something you can store and audit. A bad one makes every correction destroy the evidence for the value it replaced.

What to take away

• A state is not an attribute of a person. It is a relation between a person, a place, a date and a document, so it belongs in its own rows rather than in one column.
• Keep the source string and the normalized value in separate columns. Overwriting the raw text removes the only thing that would let anyone check the normalization later.
• Blank, zero and unknown are three different states of knowledge. A schema that cannot tell them apart will answer questions confidently and wrongly.

Model the relation, not the person

The single column fails on the first person who was born in one place, grew up in a second and spent a career in a third. You get one contested cell and an argument in the comments.

Model it as a separate table, one row per relation:

Column	What it holds
person_id	A stable internal identifier, never a name
relation	born_in, raised_in, resided_in, worked_in, died_in
place_raw	The place exactly as the source wrote it
place_id	The normalized place, as an identifier
jurisdiction	The state or territory as it stood on the relevant date
date_start, date_end	Bounds, each with its own precision flag
source_id	The document or record this came from
confidence	established, probable, unresolved

A person then carries as many rows as the evidence supports, none of them in conflict, and a query can ask a precise question instead of an ambiguous one. The five senses of "from" are what the relation column is for.

Names are the worst join key

Every merge of two people datasets is tempting to run on the name column, and that is where most silent corruption enters.

Names collide. A surname changes on marriage, at a border and in translation, and the same person turns up with different transliterations, different orderings, initials in some sources and full forms in others, honorifics attached, and married and maiden forms in different rows. Matching on a name string joins two different people whenever the string happens to agree, and fails to join one person whenever it does not. Both errors are invisible in the output: you get a table that looks complete.

Mint an internal identifier for every person on first entry, keep it forever, and store any external identifiers you have as their own columns rather than as the key. Where you must match on names, record the match as a scored candidate with its own row and a human decision field, not as a fact.

Precision has to be stored, not implied

A date column typed as a date forces every value into a day. Sources do not supply days. They supply a year, a decade, a season, a regnal or administrative period, a "before" or an "after", and sometimes two incompatible answers.

Store the bound and the precision separately. A year known to the year and a year inferred from an age at a later event are both years, and treating them as the same value is how a range collapses into a false point. The same discipline applies to place: a place known to the municipality and a place known to the state are different precisions of the same column, and the coarser one must not be padded out to look like the finer.

Blank, zero and unknown

Three distinctions decide whether an aggregate means anything:

• Not applicable. The relation cannot exist for this person, for example a state of birth for a birth outside any state.
• Unknown. The relation exists and the value has not been established.
• Zero. A count of something that was measured and came out as none.

Collapse these and a count of people per state becomes a count of people per state among rows where somebody happened to fill the field, which is a different quantity with the same column heading. Any denominator you compute from that column inherits the same defect, which is the mechanism behind most of the misleading counts described on the year-by-year page.

Provenance, versioning and corrections

A correction that overwrites a value destroys the record of what the dataset previously asserted and why. That matters because downstream copies exist, and because the earlier value is often the more interesting evidence.

Append rather than overwrite. Keep the old row, mark it superseded, record the date, the reason and the source that caused the change, and let the current view select the live rows. This costs a small amount of storage and buys you the ability to answer the only question that matters after an error: what did we publish, for how long, and on what basis.

Every row also needs a source_id that resolves to a real record, with a retrieval date. A citation stored as free text degrades into a phrase nobody can follow, which is why archive finding aids such as the guide to census records give each series a stable reference to cite.

Checks worth running

These are cheap, they run on the data you already have, and each one catches a specific failure:

• Referential integrity. Every place_id, source_id and person_id resolves. Orphans are usually the debris of a merge.
• Anachronism. No jurisdiction value that did not exist on the row's date. This catches modern state names applied to earlier territories, a point the state timeline sets out in detail.
• Interval sanity. No date_end before its date_start, no lifespan outside a plausible range, no overlap between two relations that exclude each other.
• Disagreement rate. Where two sources cover the same relation, count how often they differ. A rate of zero usually means the two sources are copies of one another.
• Coverage profile. Fill rate by decade, by relation type and by source. Sharp jumps show you where the archive changed, not where the population did.
• Round number clustering. Values ending in zero, or falling on the first of a month, at rates far above the rest. That is inference recorded as observation.

Bottom line

Most defects in a people-and-places dataset are schema defects, not research defects. One column for a five-way relation, a name used as a key, a date type that forces false precision, and an overwrite that erases history will each produce clean-looking output that no amount of careful sourcing can rescue. Fix the shape first.

Common questions

Should the dataset record a single canonical state per person?

Only as a derived view, never as the stored value, and only with the rule that produced it written down beside it. A canonical pick is an editorial decision about which relation matters most, which is a decision the reader should be able to see and disagree with.

What about living people?

The stricter rule applies inside the schema too. Do not store a living person's birthplace or residence unless they have stated it publicly, and where you do, store the statement, the outlet and the date rather than a bare value. Precise residential information should not be in the file at all. The wider frame for this is on the state pillar.

Can this data support a map?

It can support a map of records. It cannot support a map of people, and the difference between those two things is the subject of notability maps.