The Record

4.0 What this chapter gives you#

1. You will be able to look at any table that holds people and say, for every column, whether it belongs to the record’s own identity, to what the record claims about a person, or to what the record remembers about itself.
2. You will be able to explain why a name, a telephone number, an email address and a national identity number are all bad primary keys, and give a different reason for each one.
3. You will be able to calculate, from the size of a population, how many pairs of people in it share a name and a date of birth, and use that number to kill the idea that exact matching is enough.
4. You will be able to design a golden record that can explain itself, naming which source supplied each field and which rule chose it.
5. You will be able to store an attribute with a valid period, so the sentence the database asserts is “she lived there between these two dates” and not “she lives there”.
6. You will be able to add the second clock and answer two different questions with two different queries: what we now believe was true then, and what we believed then was true then.
7. You will be able to say exactly where a soft delete stops being a delete under Article 17 of the General Data Protection Regulation, and name four honest ways to close the gap.
8. You will be able to merge two person records and split them again without breaking a foreign key, because you will have separated the record identifier from the person identifier beforehand.
9. You will be able to read a real schema with real column types and constraints and name the one line that makes it auditable.

An identity system looks from the outside like a security product. It has authentication in it, and cryptography, and a login screen. From the inside it is almost entirely a database of people, and the security sits on top like a lid on a box. If the box is a mess the lid does not help. Much of what goes wrong in identity is not an attack. It is two rows where there should be one, one row where there should be two, or a row saying something that stopped being true four years ago with nothing to record when.

This chapter is about that box. We will build a person record from nothing, asking two questions of everything we add: what exactly is this record asserting, and for how long is the assertion meant to hold. Those two questions generate almost every hard problem in the field.

The claim is deliberately strong: every hard problem in an identity system is a database problem wearing a costume. Take the costume off and you find a key that is not stable, an attribute with no time dimension, a delete that fights a foreign key, a merge with no inverse. “The right to erasure” sounds legal, and it has real legal content, but the part that stops teams is that a system of record is append-only and a person has a right to make some of it go away. That is a database problem, with database answers, some honest and some not.

Three neighbours to mark and leave. The three verbs that operate on a record, working out who somebody claims to be, checking the claim, and deciding what they may do, belong to chapter 3. Human names belong to chapter 5; here a name is an ordinary attribute. Deciding whether two records are the same person is chapter 8, and here we take that decision as made and ask only what the database must do about it.

The plain version#

A box of cards, and one card each#

Picture a small clinic in a town: two doctors, a nurse, one person on the front desk, about four thousand patients. No computers. There is a wooden box on the desk with cards in it, one card per patient, filed alphabetically. The card is the record. When a patient arrives, the desk finds their card, and everything the clinic knows about that person is on it.

The rule the clinic runs on is short. One card for each person, and one person for each card. It sounds like a rule that does not need saying. It is the hardest promise in the building, and by the end of this chapter you will know why nobody has kept it perfectly anywhere.

A sensible card has the patient’s full name, date of birth, address, telephone number, and the date they registered. It also has a number in red ink in the corner, and that number turns out to be the most important thing on it.

Two things that must never be written on the card#

The more interesting question, which almost nobody asks, is what must never go on the card.

The first is anything that would let a stranger pretend to be the patient. Suppose the clinic gives each patient a slip with a secret word on it, to be said on the telephone as proof. If the desk writes that word on the card, everybody who can open the box can be that patient on the telephone. The card is a list of who people are; the secret word is a way of proving you are one of them. Keeping the proof in the same box as the list destroys the point of having a proof. Whatever the clinic uses to check a caller must live elsewhere, in a different drawer, with a different lock.

The second is harder, and it is the one that ruins clinics: the blank space at the bottom marked “notes”. A blank space with no rules fills up. Somebody writes “difficult”. Somebody writes “does not speak much English”. Somebody writes, meaning only to be helpful, “attends the mosque on Fridays, prefers morning appointments”. Together these turn the card into a small dossier holding the patient’s religion, their command of the language and a staff member’s opinion of their character, none of it dated, none of it reviewed, none of it something the patient was told was being kept. The blank space is not a field. It is a hole in the design that the world leaks through.

The number in red ink#

The clinic needs a way to point at a card. The prescription pad, the appointment book, the results from the laboratory in the city and the letter from the hospital all have to say which patient they concern.

They could say the name, except that the clinic has three patients called Sunil Kulkarni and two of them live on the same street.

They could use the telephone number. Watch how that fails, four separate ways, each fatal alone. People change their number, and every piece of paper referring to the old one now refers to nobody. Numbers are reissued, so the one Sunil gave up in March goes to a stranger in July and all that paper now refers to the wrong person with nothing to show it. A husband and wife who share a landline become, as far as the box is concerned, one patient. And a newborn baby and an old woman who never owned a telephone cannot be registered at all, and a rule that cannot register some of your patients is not a rule but a wall.

So the clinic invents a number. The next card gets 4,001, the one after 4,002, and it means nothing at all. Its entire job is to point at one card and no other. Because the clinic made it up, it can promise three things it could never promise about anything a patient brings in from outside: the number will never change, it will never be given to a second person even after the first one dies, and everybody gets one.

A card with no dates is a card that lies#

Anjali Deshpande, born the second of March 1988, registered on the fourteenth of June 2013. Address: Flat 4, Sadashiv Peth, Pune.

Be exact about what that asserts. On its face, “Anjali Deshpande lives at Flat 4, Sadashiv Peth”. But no fact of that shape is true forever. The card is really asserting that she lived there between two dates, and it has left both dates out.

Suppose in 2019 she moves across Pune and the desk rubs out the old address and writes the new one. The clinic has destroyed the only evidence it held about where she lived in 2016, which is exactly what somebody will ask about when a letter sent in 2016 turns out never to have arrived. The fix is not clever. Instead of one address line, the card gets a small table: the address, the date she moved in, the date she moved out.

address                       from         to
Flat 4, Sadashiv Peth, Pune   2013-06-14   2019-01-09
12 Baner Road, Pune           2019-01-09   (blank)

Two details matter more than they look. The move-out date of one row equals the move-in date of the next. That is deliberate: agree once that a row covers its start date and everything up to but not including its end date, and the rows meet edge to edge with no gap and no overlap. And we can now answer a question the old card could not. Where did she live on the fourth of March 2016? Find the row whose window contains that date. That is still the answer in 2040, because nothing was rubbed out.

The second notebook: when we found out#

Anjali moved on the ninth of January 2019. The clinic did not learn until the third of April, when she mentioned it at an appointment. For nearly three months the box said Sadashiv Peth, and the clinic acted on that, posting a letter there on the twentieth of February.

So: on the twentieth of February 2019, where did Anjali live?

There are two correct answers and they differ. She lived at Baner. The clinic believed she lived at Sadashiv Peth. Both are facts, the first about the world and the second about the clinic. If somebody later asks why the letter went to the wrong house, only the second answers, and the little table cannot hold it, because after the third of April the table simply says she has been at Baner since January and always did. The table has quietly rewritten the past so the clinic looks careless when in fact it acted correctly on what it knew.

So the clinic keeps a second notebook: not a table of where people lived, but a diary of when the clinic learned things, from which nothing is ever rubbed out. The entry reads: on the third of April 2019 we were told that from the ninth of January she lives at Baner.

Two clocks. One is the world’s, recording when something was true. The other is the office’s, recording when we came to believe it. Almost every argument about a record that lasts more than five minutes is an argument between somebody holding one clock and somebody holding two.

Crossing out is not the same as burning#

In 2026 Anjali writes and asks the clinic to destroy everything it holds about her. The desk takes out her card, draws a diagonal line across it, writes “closed” at the top, and puts it back. In the clinic’s language the card is deleted. In no other sense is it. It is still in the box, still readable, still in the photocopy made last summer and left in the storeroom, and still referred to by forty pieces of paper in the filing cabinet that say “patient 4,001”.

Crossing out is useful: it stops the card being used by accident and it keeps the filing cabinet honest, because those forty papers still point at something that exists. What it is not is destruction, and a clinic that tells Anjali her records were destroyed has told her an untruth.

Now try burning it. Forty papers point at a card number with no card. The prescription register does not balance. And if one of those papers records a controlled drug, the clinic has committed a second offence to avoid the first. That tension is real and no arrangement of the box removes it. There are only different ways of being honest about it, and we will name four, with the law each answers to.

Two cards for one person, and one card for two people#

In August 2019 Anjali comes in on a Saturday when the usual person is off. She gives her married name, which is not the name on her card. The locum searches, finds nothing, and helpfully makes a new card, 5,412. The clinic now holds two cards for one woman. Her blood pressure readings go on one and her allergy note on the other, and neither doctor sees both.

The other way round is worse. Two women with the same name and birth date arrive over the years, the desk assumes they are one person, and a single card accumulates two medical histories. Nobody notices, because the card is perfectly consistent. It is simply wrong about whose body it describes.

Joining two cards sounds easy: copy everything onto one and cross out the other. It is easy exactly once, and catastrophic when you later find you were wrong, because you cannot tell which entries came from which original. The join destroyed the information needed to undo it.

The clinic that survives does something odd-looking. It never merges cards. It keeps both exactly as they were and writes a third, small piece of paper: cards 4,001 and 5,412 are the same woman, decided on the fifth of May 2021 by the practice manager, on the marriage certificate she brought in. Everything that reads the box follows that note. If the note is wrong, tear up the note; both cards are untouched and nothing was lost.

The whole worked example in one place#

2013-06-14  card 4001 made. Born 1988-03-02, at
            Sadashiv Peth from 2013-06-14.
2016-11-22  marries; name changes. New name row
            opens, old name row closes, same day.
2019-01-09  she moves to Baner. Clinic unaware.
2019-02-20  clinic posts a letter to Sadashiv Peth.
2019-04-03  clinic learns. Address rows corrected
            back to 2019-01-09. Diary entry written
            today and never altered.
2019-08-17  card 5412 made by mistake. Same woman.
2021-05-05  note written: 4001 and 5412 are one
            person. Neither card touched.
2023-09-30  three entries on 5412 turn out to be a
            different woman. Note narrowed, not torn
            up. Both cards still intact.
2026-02-12  she asks for everything to be destroyed.

Every line of that is a database problem, and the rest of the chapter gives the professional vocabulary for each.

Where the plain version stops being true#

There is not one box#

The clinic has one box on one desk with one person allowed to write in it. Every real identity system has many boxes and they disagree.

A bank holds you in a core banking system, a customer relationship system, a card system, a fraud system, a marketing platform and a document store. A government holds you in a civil register, a tax file, a health record, an electoral roll and a benefits system. None was designed at the same time by the same people, and none was designed to defer to the others. Each has its own row for you, its own key, its own idea of your address, and its own moment at which it last heard from you.

That changes the problem’s shape. In the one-box story, “one card per person” is a rule you enforce at the desk. Across six boxes it is an aspiration that some other process reconciles after the fact, forever, and most of the money organizations spend on identity data goes there.

One card per person was never true#

The plain version presents duplicates as an accident: a locum, a missed search. That is how they feel from inside. It is not why they exist.

Duplicates exist because the desk cannot see the person. It sees only what the person produces, and productions vary: a different spelling, a different name order, a nickname, a new address, a date of birth given as the first of January because that is what the papers say. Deciding that two productions came from one body is a judgement under uncertainty, and such judgements are wrong in both directions. Tighten the rule and you create duplicates; loosen it and you merge strangers. There is no setting where both errors are zero, and the mathematics of that trade is chapter 8.

“One person, one row” is therefore not a promise a well-run register keeps and a bad one breaks. It is a target every register misses. The useful question is by how much, in which direction, and whether you measure at all.

A card is not one thing with one lifetime#

The plain version treats the card as a unit: make it, keep it, cross it out. That is the most misleading thing in the story. A date of birth is fixed for life and comes from a registrar. An address changes every few years and comes from the person. A telephone number changes and is then reassigned by somebody else entirely. A note that somebody prefers morning appointments has no authority behind it at all. Treating those as one object, created and destroyed together, produces a record where you cannot say which parts you trust. A card is a bundle of separate assertions that happen to concern the same person, each with its own start, end and source.

The two clocks are not two clocks#

The world’s clock is not single, because the world does not agree with itself about when a fact started. A marriage has a ceremony date, a registration date and a certificate issue date. A death has a date of death, a date of certification and a date of registration, and the first is sometimes an estimate. Saying “valid from the ninth of January” chooses one of several defensible dates, and the choice must be written down, because another system will have chosen differently and the two will not reconcile.

The office’s clock has its own subtlety. There is the moment the fact arrived, the moment it was keyed in, and the moment it became visible to the systems that act on it. In a batch-fed architecture those can be days apart, and the one that matters for defending a decision is when the deciding system could have seen it.

Crossing out and burning are both wrong#

Marking the card is wrong because in a computer the card is not in one box. It is in the table, the search index, last night’s backup, the read replica, the warehouse, three years of application logs, and a spreadsheet somebody exported in 2022. Marking the row touches none of them.

Destroying the row is wrong because a system of record that can lose rows cannot be audited. If a row can vanish, any inconsistency in the history can be explained away as one of the deleted ones.

The honest version: no operation both removes a person and leaves the history intact, because those are direct contradictions. Good systems decide in advance, in writing, which parts of the history survive an erasure and under which named legal exception, and make the survivors genuinely unable to identify anybody. That is a schema-time decision. No delete statement retrofits it.

The made-up number does not stay in the building#

The meaningless number in red ink is the plain version’s best idea. What the story does not show is what happens once it leaves the desk. The laboratory files results under it, the hospital uses it in referral letters, a supplier’s reminder service stores it, and within two years the clinic’s private label is a shared label it cannot change, retire or reuse without breaking systems it has never heard of. Every property that made the number safe is now a promise owed to strangers.

The tidiest lie in the plain version#

The whole story assumes the clinic knows which body it is dealing with, and that the difficulty is merely clerical.

The honest version: a register never contains people. It contains records of encounters, and a belief that certain encounters involved the same body. That belief is an inference with a strength, a date and an author, and it can be revised. A system that stores people stores a conclusion and throws away the evidence. A system that stores encounters and separately stores the current grouping of them into people can revise the grouping when new evidence arrives, and can say why it grouped them as it did. Everything difficult in the rest of this chapter follows from taking that seriously.

The technical version#

What a person record contains#

A person record is not a flat row of columns. It is five distinct kinds of thing that beginners flatten together and then spend years pulling apart. Naming the five is most of the design.

The record’s own identity is the surrogate primary key and nothing else. Other tables point at it. It belongs to the database, not the person, and it never changes.

External identifiers are the values other systems and authorities use: a national identity number, a payroll number, an email address, a mobile number in E.164 form. Each needs a namespace saying whose numbering scheme it is, an issuer, and a validity period, because identifiers are issued, revoked and reassigned. They are never columns on the person row; they are rows in a child table.

Attributes are what the record asserts: names, dates, addresses, contact points, legal status, entitlements. Each needs a value, a period over which it is asserted to hold, and a source. An attribute with no period and no source is a rumour with a column name.

Provenance and assurance record what evidence supports each assertion, who examined it, when, and to what standard. This answers “how do you know” three years later, and it is cut first under time pressure.

Record metadata is data about the row: when it was created, by which process, its status, its relationship to other records. Mixing it into the attributes is how a system loses the ability to tell a person who died from a record that was withdrawn.

Now the harder half. Four things must never sit in a person record, each for a different reason.

Authenticators come first. Password verifiers, one-time-password seeds, recovery answers, biometric templates and device keys are the means of proving the record belongs to you. Storing them beside the record turns read access to the directory into impersonation access for everybody in it. The credential store belongs in a different table, on a different access path, readable by different people.

Payment secrets are next, and here the rule is absolute. PCI DSS version 4.0.1, published in June 2024 and the only active version since v4.0 was retired on 31 December 2024, states at requirement 3.3.1 that sensitive authentication data “is not stored after authorization, even if encrypted”, and at 3.3.1.3 that the personal identification number and PIN block “are not stored upon completion of the authorization process”. No configuration permits a person record to hold a card verification value; the data is not permitted to exist at rest.

Third is the free-text note. Every identity schema acquires one, called notes or comments or remarks, and within two years it holds health information, ethnicity, immigration status and staff opinions, none of it coded, retained to a policy, or visible to the controls the organization believes it has. The rule is not “be careful what you write there”. It is that a fact worth recording is worth a column with a defined domain and a retention rule, and a fact not worth that is not worth recording.

Fourth is special category data. Article 9(1) of the General Data Protection Regulation, Regulation (EU) 2016/679, covers data revealing racial or ethnic origin, political opinions, religious or philosophical beliefs, or trade union membership, together with genetic data, biometric data processed for the purpose of uniquely identifying a natural person, data concerning health, and data concerning sex life or sexual orientation, and prohibits processing them unless a condition in Article 9(2) applies. Note the wording on biometrics: it is biometric data processed for unique identification that is special category, not every photograph. Much of this is lawful to hold with the right basis; what is never wise is holding it in the general person record, where it inherits that record’s access rules instead of its own.

One more rule is a correctness problem rather than a legal one. Do not store derived values as though observed: a risk score or a match confidence is the output of a rule applied to inputs at a moment, and storing it without the inputs and the rule version manufactures a fact nobody can reproduce.

The primary key problem#

Codd’s 1970 paper, “A Relational Model of Data for Large Shared Data Banks”, in Communications of the ACM volume 13 number 6, June 1970, introduced the term in one sentence: a domain, or combination of domains, whose values uniquely identify each element of a relation is “called a primary key”. Codd’s model assumes such a combination exists. For people it does not.

A natural key is made of real-world data that already identifies the thing. A surrogate key is a value the system invents, carrying no meaning, existing only to be a key. The argument between them is old and in most domains genuinely two-sided. For people it is not.

The first three are familiar. The last three catch experienced teams, and each has a documented case behind it. Reassignment is the quiet one: a mobile number surrendered by one subscriber is issued to another after a waiting period, nothing in the number records the handover, and every row keyed on it silently changes its subject.

Exhaustion sounds theoretical until you look at date-based national numbers. Sweden’s personnummer, introduced in 1947 and generally described as the first such scheme covering a whole resident population, encodes the date of birth in its first six digits, with a three-digit serial and, since the 1967 computerization, a check digit; it went to twelve digits for automated processing in 1997. In February 2007 the Swedish Tax Agency reported that numbers were about to run out for certain dates, mainly the first of some months, because those dates appear disproportionately on birth records from countries where births are not always precisely registered. Denmark hit the same wall from the same cause. Its CPR number is a date of birth plus a four-digit sequence, historically validated by a modulus 11 check across the weights 4, 3, 2, 7, 6, 5, 4, 3, 2 and 1; the sequence for men born on 1 January 1965 ran out, and since October 2007 Danish numbers do not always validate against the check digit at all.

Legal restriction is the case people forget. The Health Insurance Portability and Accountability Act of 1996 required a unique health identifier for individuals in the United States. Since the fiscal year 1999 appropriations cycle, Congress has attached a rider, generally cited as Section 510, barring the Department of Health and Human Services from spending appropriated funds to promulgate or adopt such a standard, renewed every year since. The consequence is structural: American healthcare has no national patient key, so hospitals infer identity from demographics, and an entire patient-matching industry exists because a natural key was legislated out of existence.

Where a natural key does exist it usually carries a check digit, and you should be able to work one by hand. The NHS number in England, Wales and the Isle of Man is ten digits, the tenth a modulus 11 check whose use the NHS Data Model and Dictionary states is mandatory. The format was allocated to newborns from July 1995, introduced generally in 1996 and made mandatory on 1 April 1997, replacing schemes including alphanumeric codes in England and Wales and household-based numbers in Scotland, which could not be validated at all.

Number: 401 023 2137

digit    4  0  1  0  2  3  2  1  3
weight  10  9  8  7  6  5  4  3  2
product 40  0  8  0 12 15  8  3  6

sum          = 92
92 mod 11    = 4
11 - 4       = 7
check digit  = 7  -> matches the tenth digit

If the subtraction gives 11 the check digit is 0. If it gives 10 the number is invalid and never issued, which quietly discards about one in eleven of the possible numbers, and that is part of why Denmark abandoned its scheme.

The rule that falls out of all six failures is short. The primary key of a person record must be assigned by the system, carry no meaning, be immutable for the life of the record, and never be reused. Everything brought in from outside is an external identifier and goes in a child table with a namespace, an issuer and a validity period. Do not compromise even when the external identifier looks perfect, because “looks perfect” is what national identity numbers looked like in Sweden in 2006. One caveat: a sequential key leaks, revealing how many records were created between any two of them and letting anybody holding one enumerate the rest, so if the key will ever appear in a web address use a universally unique identifier. RFC 9562 of May 2024 defines the current versions; version 4 is random and version 7 time-ordered.

One person, one row, and the arithmetic against it#

Take a register of five million people and ask how many pairs of them share both a full name and a date of birth.

Assume birth dates spread over a hundred years, which is 36,525 days. Take a name held by one person in a thousand, so five thousand people carry it. The number of pairs among those five thousand is five thousand times four thousand nine hundred and ninety-nine over two, which is 12,497,500. Divide by 36,525 possible birth dates and you get about 342 pairs sharing that one name and a date of birth. For a single name, in one register.

The count grows with the square of the population, so doubling the register quadruples the collisions. Every figure there is also an underestimate, because birth dates are not uniform: the first of January is over-represented in every register that admits people whose birth was not precisely recorded, which is exactly what forced Sweden and Denmark into their shortages.

Measured figures agree. A Bipartisan Policy Center issue brief on patient matching reported that Harris County, Texas held 2,488 real patients named Maria Garcia, 231 of them sharing a date of birth, and that the wider dataset held 69,807 pairs sharing both a name and a birth date. The same brief put published average error rates at about eight per cent, ranging up to twenty, and reported that nineteen per cent of 128 surveyed hospital chief information officers had seen an adverse event in the previous year caused by a patient information mismatch. Sharp HealthCare alone identified over 652,000 false negatives between 2001 and 2012, of which staff had corrected 568,000.

The promise therefore fails in both directions. A duplicate is two or more records for one person: history fragments, and each fragment looks complete. An overlay, or commingled record, is one record holding two people: it looks perfectly consistent and is simply wrong about whose body it describes. Duplicates are more common; overlays are far more dangerous, because there is no internal inconsistency to detect and unwinding one is often impossible.

The design consequence is the one the plain version reached from the other side. Do not model people. Model records, and model the belief that certain records refer to the same person as a separate, revisable, time-stamped assertion. Once “these two rows are one person” is itself a row, with an author, a date and an evidence pointer, duplicates and overlays stop being catastrophes and become corrections.

The golden record and master data management#

The commercial name for reconciling many boxes into one view of a person is master data management. Master data is the shared, slow-moving data about the entities an organization deals with, as opposed to the transactions that reference them, and people are master data almost everywhere.

The standards series is ISO 8000, maintained by ISO technical committee 184, subcommittee 4, Industrial data. The vocabulary is ISO 8000-2, fifth edition of September 2022; as of August 2026 it sits at ISO stage 90.92, marked for revision, with a draft in progress. ISO 8000-110:2021 specifies requirements for exchanging master data as characteristic data, covering syntax, semantic encoding and conformance to a data specification, and says those requirements can be checked by computer. Three further parts define the qualities that make a person record trustworthy rather than merely present: ISO 8000-120:2016 on provenance, ISO 8000-130:2016 on accuracy, ISO 8000-140:2016 on completeness, with ISO 8000-150:2022 on roles. Those three are usually measured in the wrong order: completeness is easy and gets a dashboard, accuracy is hard and gets a sample audit, and provenance, which decides whether you can defend a decision, is often not recorded at all.

Golden record means the single reconciled version of a person assembled from several contributing sources. The DAMA Data Management Body of Knowledge, second edition, 2017, treats it under trusted source and best version of the truth, and then warns in plain terms that the term can be misleading. That warning is the honest core of the subject.

A golden record is not a discovery. It is an output. Somebody wrote survivorship rules and the rules chose which value wins. Common rules are: most recent update wins; most trusted source wins; most complete value wins; longest string wins. That last is used in production more often than anyone admits, and it means a person’s address is whichever version has the most characters. Change the rules and the golden record changes with no change whatsoever in the world.

Three consequences follow. A golden record must be derived, never authored: if a human can edit it directly, the next refresh either destroys the edit or the edit permanently overrides a source, so human corrections belong in a source of their own with the corrector as asserter. It must retain its contributions, so that for every field you can say which record supplied the winning value, which supplied losing values, and which rule version chose; without that, “why does the system think I live in Nagpur” has no answer. And it must be rebuildable from the sources and the rules, or it is not a master data system but a second source with an authoritative name.

Architectures vary in what they promise. A registry holds only keys and cross-references, leaving data in the sources. A consolidation hub copies data in for reporting and never writes back. A coexistence hub publishes the golden record back to the sources, which must agree to accept it. A transaction hub is the master, written to directly. Almost every organization believes it is building the fourth and is operating the second.

Valid time: an attribute is true between two dates#

The vocabulary for time in databases was fixed by Richard Snodgrass and Ilsoo Ahn in “A Taxonomy of Time in Databases”, presented at the ACM SIGMOD International Conference on Management of Data in Austin, Texas in May 1985, pages 236 to 246. Valid time is, in their words, “the time that the stored information models reality”. Transaction time is “the time the information was stored in the database”. User-defined time is any other date the engine does not interpret.

Snodgrass’s Developing Time-Oriented Database Applications in SQL, Morgan Kaufmann, July 1999, 504 pages plus front matter, remains the practical reference; the research language TQuel appeared in ACM Transactions on Database Systems volume 12 number 2 in June 1987, and the TSQL2 design, from a committee Snodgrass chaired, was published by Kluwer in 1995. TSQL2 was never adopted as a standard: the committees rejected its hidden-column approach and came back sixteen years later with a different design.

Snodgrass and Ahn called the transaction-time kind a rollback database and the valid-time kind a historical database, reserving temporal for one supporting both, which they described as making it possible to view tuples valid at some moment as seen at some other moment, capturing retroactive and postactive changes completely. Most production identity systems are snapshot tables with an audit log bolted on, which is a poor approximation of the third row and none at all of the second.

Two implementation decisions cause more argument than they deserve. First, interval convention: use closed-open, start inclusive and end exclusive. Adjacent periods then share a boundary value with no gap and no overlap, and you never write 23:59:59 anywhere. Snodgrass argues for this throughout, SQL:2011 adopted it, and PostgreSQL range types default to it. Second, the open end: NULL is semantically honest and operationally miserable, breaking comparison and uniqueness and forcing a special case into every query, while a sentinel such as 9999-12-31 is a lie you must remember. Where the engine offers it, use a real infinity; PostgreSQL accepts the literal ‘infinity’ for date and timestamp types and its ranges accept an unbounded upper bound.

Now the decision that determines whether a schema is any good: which attributes get a valid time and which do not.

Date of birth is the instructive one. It has no valid time, because it does not become true and later stop being true; it is a fixed property of a life. But it very much has a transaction time, because the register can be wrong about it and can be corrected. That distinction, between an attribute that changes and an attribute we change our mind about, is what makes the second clock necessary.

Transaction time and the bitemporal model#

Put the two clocks on two axes and every operation becomes a shape.

              valid time (when it was true) ->
              Jan          Apr          Jul
             +------------+------------+------------+
  Apr 3   ^  |  Sadashiv  |///// Baner corrected ///|
  (when   |  |    Peth    |//// back to 9 Jan //////|
  we      |  +------------+------------+------------+
  learnt) |  |  Sadashiv Peth, believed throughout  |
  Jan 1   |  +------------+------------+------------+

  Row A: valid [2013-06-14, infinity)
         recorded [2013-06-14, 2019-04-03)  closed
  Row B: valid [2013-06-14, 2019-01-09)
         recorded [2019-04-03, infinity)    current
  Row C: valid [2019-01-09, infinity)
         recorded [2019-04-03, infinity)    current

Anjali moved on 9 January 2019 and the register learned on 3 April. Before 3 April its best belief was Sadashiv Peth, and it acted on that when it posted a letter on 20 February. After 3 April its best belief is Baner since January. Both statements are permanently true, about different clocks, and a bitemporal table holds both without either overwriting the other.

One line makes this work and it is the most important line in the chapter. Valid time may be revised; transaction time is append-only and is never edited. A correction does not update a row. It closes the transaction-time period of the old row, which stays readable forever, and inserts a new row whose transaction-time period starts now. The old row is not wrong. It is what we believed, which is a fact about us, and facts about us do not change retrospectively.

From that come four operations. A current insertion records something now believed true from now. A retroactive change records something now believed true from a past date, so the valid period starts before the transaction period; Anjali’s move is one. A postactive change records something believed true from a future date, such as a scheduled entitlement. A correction records that a previous belief was wrong: it closes the prior row in transaction time and inserts a replacement, and crucially does not claim the world changed. A change and a correction are indistinguishable in a valid-time-only table, which is precisely why such tables cannot be audited.

Two queries separate the clocks, and writing both is the practical test of whether you have understood this.

-- What do we believe today about where she
-- lived on 4 March 2019?
SELECT value
FROM   person_fact
WHERE  record_id  = :rid
AND    attribute  = 'address'
AND    valid_at   @> DATE '2019-03-04'
AND    upper_inf(recorded_at);

-- What did we believe on 4 March 2019 about
-- where she lived on that same day?
SELECT value
FROM   person_fact
WHERE  record_id  = :rid
AND    attribute  = 'address'
AND    valid_at   @> DATE '2019-03-04'
AND    recorded_at @> TIMESTAMPTZ
       '2019-03-04 00:00:00+05:30';

The first returns Baner, the second Sadashiv Peth. A system that can only answer the first cannot defend a past decision, and every regulated identity decision is eventually asked to. Article 5(2) of the General Data Protection Regulation makes the controller responsible for demonstrating compliance, not merely complying, and demonstrating what you did means reconstructing what you knew.

The costs are real. Row counts multiply, every query needs two extra predicates that developers forget, object-relational mappers cannot express bitemporal access, and uniqueness constraints become non-overlap constraints needing different index types. Make bitemporal exactly those attributes on which a decision is made or a right depends, and leave the rest as ordinary columns with an audit trail.

What SQL:2011 standardized and what the engines shipped#

The standard caught up in December 2011 with ISO/IEC 9075-2:2011, Information technology, Database languages, SQL, Part 2: Foundation, since withdrawn in favour of ISO/IEC 9075-2:2023, which carries a technical corrigendum dated 2026. The best short account of what was added is Krishna Kulkarni and Jan-Eike Michels, “Temporal features in SQL:2011”, ACM SIGMOD Record volume 41 number 3, September 2012, pages 34 to 43.

SQL:2011 did not adopt the words valid time and transaction time. It defines a period as a named pair of existing datetime columns, then two kinds of table. An application-time period table carries a user-named period, for example PERIOD FOR valid_at (valid_from, valid_to), maintained by the application; the standard adds PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) so the engine enforces that one identifier never has two overlapping periods, and FOR PORTION OF on UPDATE and DELETE, which splits rows automatically. A system-versioned table carries the reserved period name SYSTEM_TIME over two engine-maintained columns, declared WITH SYSTEM VERSIONING; an UPDATE inserts a copy of the old row with its system period closed, and a DELETE closes the period rather than removing the row. Queries use FOR SYSTEM_TIME AS OF, BETWEEN and FROM ... TO. A table with both is bitemporal, and that is the shape a person record should have.

Db2 10 for z/OS, whose technical overview redbook is dated December 2010, shipped both dimensions ahead of the standard’s publication, using its own vocabulary: business time for valid time, system time for transaction time. MariaDB introduced system-versioned tables in the 10.3 series and application-time periods in 10.4, with WITHOUT OVERLAPS arriving in 10.5.3.

PostgreSQL is the interesting case for anybody building today. Release 18, on 25 September 2025, added temporal PRIMARY KEY and UNIQUE constraints using WITHOUT OVERLAPS and temporal FOREIGN KEY constraints using the PERIOD keyword. There is a real difference from the standard. The standard defines a period over two ordinary datetime columns; PostgreSQL requires a single column of a range or multirange type as the last column of the constraint, and requires the btree_gist extension so scalar and range columns can share one index. Referential actions on temporal foreign keys, meaning ON UPDATE and ON DELETE with CASCADE, SET NULL or SET DEFAULT, are not supported in the initial implementation. That is an implementation detail rather than a property of the idea, and it will change. PostgreSQL still has no built-in system versioning as of August 2026, so transaction time is maintained by the application or by triggers, which at least makes the mechanism visible rather than magical.

Soft delete, the right to erasure, and the conflict#

A soft delete marks a row as gone without removing it: a deleted_at timestamp or a status column, plus a filter in every query. It exists for good reasons: foreign keys still resolve, history still balances, mistakes can be undone. A hard delete removes the row; foreign keys either block it or cascade, and cascading from a person record is close to always wrong, because it silently destroys transactions, consents and audit entries others may have a right to see.

Neither is erasure, and the law is specific.

Article 17 of the General Data Protection Regulation is titled “Right to erasure (‘right to be forgotten’)”. Article 17(1) gives six grounds on which a data subject may obtain erasure without undue delay: the data are no longer necessary for the purposes for which they were collected; consent is withdrawn with no other legal basis; the subject objects under Article 21 with no overriding legitimate grounds; the data were unlawfully processed; erasure is required by a legal obligation; and the data were collected in relation to information society services offered to a child under Article 8(1). Article 17(2) adds that a controller who made the data public must take reasonable steps to inform other controllers that erasure of links and copies has been requested.

Article 17(3) is the half engineers usually have not read, and where most real answers live. Erasure does not apply to the extent processing is necessary for freedom of expression and information; for compliance with a legal obligation or a task in the public interest or official authority; for public health under Article 9; for archiving in the public interest or scientific, historical or statistical purposes under Article 89(1), where erasure would seriously impair those objectives; and for the establishment, exercise or defence of legal claims.

Two other provisions bind the design. Article 5(1)(e), storage limitation, requires personal data to be “kept in a form which permits identification of data subjects for no longer than is necessary for the purposes for which the personal data are processed”. Article 19 requires the controller to communicate any rectification, erasure or restriction to each recipient to whom the data were disclosed, “unless this proves impossible or involves disproportionate effort”. The UK Information Commissioner’s Office states that a controller must respond without undue delay and at the latest within one month.

Retention law pushes back with equal force. Under the UK Money Laundering, Terrorist Financing and Transfer of Funds (Information on the Payer) Regulations 2017, regulation 40 requires records to be kept for five years beginning when the business relationship ends or the transaction completes, with an outer limit of ten years. A bank that erases a customer inside that window breaks one law to comply with another. No schema resolves that; a schema has to represent it, by recording which retention obligation applies to which part of the record and until when.

So the conflict is exactly this. A system of record wants to be append-only, because only an append-only history can be audited. A data subject has a right, in defined circumstances, to make part of it cease to exist. Both cannot be fully satisfied. Here are the four honest trades.

Tombstoning with key retention. Keep the person row and its primary key; delete every attribute, identifier, document and contact point. Foreign keys resolve and counts balance. What survives is that a person existed and had these transactions, and whether that residue is personal data depends on whether it can be attributed to an individual with additional information, which for an unusual transaction pattern it often can.

Pseudonymization in place. Replace identifying values with a token. This is the option most often mistaken for erasure and it is not erasure. Recital 26 is explicit that data which have undergone pseudonymization and could be attributed to a person by the use of additional information should be considered information on an identifiable natural person. It lowers risk; it does not end processing.

Cryptographic erasure, or crypto-shredding. Encrypt each person’s attributes under a key unique to them, hold the keys separately, and on an erasure request destroy the key. NIST Special Publication 800-88, Guidelines for Media Sanitization, treats cryptographic erase as a purge technique; Revision 1 was published on 17 December 2014 and Revision 2 finalized in September 2025. The technique is sound and has three hard preconditions usually not met. Every copy, including backups, replicas, indexes and warehouse extracts, must be ciphertext and not a decrypted derivative. The key must be destroyed everywhere, including in key backups. And the cipher must still be strong when somebody attacks the ciphertext years later, which is a bet rather than a fact.

The clearest statement of both technique and limit comes from the French data protection authority, the CNIL, in “Solutions for a responsible use of the blockchain in the context of personal data”, September 2018. The CNIL states plainly that it is technically impossible to grant a request for erasure when data is registered on a blockchain, then offers approximations: deleting the secret key of a keyed hash function, so that nobody can prove or verify what was hashed; erasing the elements that allow a commitment to be verified; and deleting the keys where the stored form is a ciphertext. It is careful that these render data practically inaccessible rather than erasing it, and the distinction applies just as much to an ordinary database with immutable backups.

Hard delete is sometimes correct, particularly for records held on consent alone with no downstream references. It cannot be the general answer for a system of record.

Two practical points close this out. Nobody can reach into last month’s tape and remove one person. The Information Commissioner’s Office position is that the key issue is to put the backup data beyond use: not used for any other purpose, and left until overwritten on the normal cycle. That holds provided the cycle is short, documented and followed, and provided a restore re-applies outstanding erasures before the data goes live again. That last step is the one teams forget, and it turns a restore into a re-identification event.

Second, the suppression list, which contains a genuine irony. Erase somebody completely and nothing stops the same broken enrolment process re-creating them tomorrow, so systems keep a one-way hash of an identifier, with no attributes, whose only purpose is to recognize a person they have promised to forget. That list is personal data, retained precisely because retaining it gives effect to the erasure.

Referential integrity when the human merges or splits#

Every other table carries a foreign key to the person. On a merge, two person keys become one person, and every one of those foreign keys now points at something that has changed meaning. There are three strategies and only one survives contact with a mistake.

Repoint and delete. Update every referencing row from the losing key to the winning key, then delete the loser. Fast, simple, irreversible. Every external system holding the losing key now dangles, and every historical audit entry now claims to have concerned the winner, which is false. This strategy destroys the evidence needed to undo itself.

Repoint and tombstone. The same repointing, but keep the losing row with a pointer to the winner, so external references resolve by following it. Better, still lossy: the referencing rows were rewritten and no longer record what they were filed against.

Never repoint. Leave every referencing row pointing at the record it was always filed against, and express the merge as a separate, time-stamped, revocable assertion that two record keys denote one person. Reads resolve the cluster at query time. This is the only strategy under which unmerge is possible, because nothing was destroyed.

One idea makes the third practical: separate the record identifier from the person identifier. A record identifier is created once, points at a set of encounters, and is immutable and permanent. A person identifier names a cluster of record identifiers, and cluster membership is itself a temporal fact with a valid time, a transaction time, a decider and an evidence pointer. Merging adds an edge. Splitting closes an edge. Neither touches a row of data.

  merge decided 2021-05-05
  4001 ---------------------> 5412
   |                            |
   |  visits, results, letters  |  visits filed
   |  filed against 4001        |  against 5412
   v                            v
  [ read time: cluster = {4001, 5412} ]

  split decided 2023-09-30: three encounters on
  5412 were a different woman.
  5412 splits: new record 7734 created.
  Edge 4001 -> 5412 stays. No edge to 7734.
  Nothing filed against 4001 or 5412 was rewritten.

Four rules govern the cluster graph. Keep the chain and compress on read: if 4001 merges into 5412 and 5412 later into 6120, do not rewrite the first edge, because that destroys the order in which decisions were made. Never cascade from a person: every foreign key referencing a person record must be RESTRICT or NO ACTION, since CASCADE means one operator’s mistake silently removes years of transactions with no undo. Give every attribute row an evidence pointer, because when a record turns out to be an overlay the only way to divide its contents is to know which enrolment produced each row; without provenance the split is a guess, and a guess about which of two people is diabetic is not an acceptable output. And model a split as a new record rather than an edit: the encounters belonging to a different woman become record 7734, with its own key and history, while the original is not rewritten.

One further rule applies to temporal foreign keys. A referencing row valid over a period must reference a row valid across that whole period, not merely at one instant. An entitlement valid from January to December pointing at a residence status valid only from June is not referentially valid even though both rows exist. That is what the PERIOD keyword on a foreign key expresses in SQL:2011 and in PostgreSQL 18, and it is a check hand-written code almost never performs.

A worked schema#

Here is the design in PostgreSQL 18, with real types and constraints.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TYPE record_status AS ENUM (
  'active', 'merged', 'split_source', 'erased'
);

-- The record spine. One row per record, never
-- per person. Nothing here describes a human.
CREATE TABLE person_record (
  record_id    uuid PRIMARY KEY
               DEFAULT gen_random_uuid(),
  status       record_status NOT NULL
               DEFAULT 'active',
  created_at   timestamptz NOT NULL DEFAULT now(),
  created_by   text NOT NULL,
  created_via  text NOT NULL,
  erased_at    timestamptz,
  CONSTRAINT erased_has_time CHECK (
    (status = 'erased') = (erased_at IS NOT NULL)
  )
);

Note the absences. No name, no date of birth, no status called deceased. Death is a fact about a person and belongs with the other facts; erasure is a fact about a record and belongs here.

-- External identifiers. Namespaced, issued,
-- and valid only between two dates.
CREATE TABLE record_identifier (
  record_id   uuid NOT NULL
              REFERENCES person_record (record_id)
              ON DELETE RESTRICT,
  namespace   text NOT NULL,
  value       text NOT NULL,
  issuer      text NOT NULL,
  valid_at    daterange NOT NULL,
  asserted_by text NOT NULL,
  PRIMARY KEY (record_id, namespace,
               valid_at WITHOUT OVERLAPS),
  CONSTRAINT one_holder_at_a_time
    EXCLUDE USING gist (
      namespace WITH =,
      value     WITH =,
      valid_at  WITH &&
    ),
  CONSTRAINT namespace_is_a_uri CHECK (
    namespace ~ '^[a-z][a-z0-9+.-]*:'
  )
);

The temporal primary key says one record holds at most one value in a given namespace at any instant. The exclusion constraint says the reverse and is more important: one value in a namespace is held by at most one record at any instant. That single constraint makes identifier reassignment expressible rather than catastrophic. When a mobile number is reissued you close the first record’s period and open the second’s, and the database enforces that they do not overlap. Without it, two records hold the same number and every lookup is ambiguous with nothing to signal it. The namespace check forces a URI-shaped namespace: 4010232137 in namespace ‘nhs’ means nothing to a system in another country, whereas ‘urn:nhs-uk:number’ does.

-- Facts about the person. Bitemporal: valid_at
-- is the world's clock, recorded_at is ours.
CREATE TABLE person_fact (
  fact_id     bigint GENERATED ALWAYS AS IDENTITY
              PRIMARY KEY,
  record_id   uuid NOT NULL
              REFERENCES person_record (record_id)
              ON DELETE RESTRICT,
  attribute   text NOT NULL,
  value       jsonb NOT NULL,
  valid_at    daterange NOT NULL,
  recorded_at tstzrange NOT NULL
              DEFAULT tstzrange(now(), NULL, '[)'),
  evidence_id uuid,
  asserted_by text NOT NULL,
  supersedes  bigint
              REFERENCES person_fact (fact_id),
  CONSTRAINT one_belief_per_period
    EXCLUDE USING gist (
      record_id WITH =,
      attribute WITH =,
      valid_at  WITH &&
    ) WHERE (upper_inf(recorded_at))
);

That partial exclusion constraint is the line that makes the design auditable, and it repays reading slowly. Among the rows we currently believe, identified by an open-ended recorded_at, no two may make statements about the same attribute of the same record over overlapping valid periods. Superseded rows are exempt, because their recorded_at has been closed. So the table can hold three past beliefs about Anjali’s March 2019 address and still guarantee that today there is exactly one. The supersedes column names the row a correction replaces, which forces the writing code to say whether it is correcting a belief or recording a change in the world.

-- Cluster membership: the claim that two
-- records denote one person. Revocable.
CREATE TABLE record_link (
  from_record uuid NOT NULL
              REFERENCES person_record (record_id)
              ON DELETE RESTRICT,
  to_record   uuid NOT NULL
              REFERENCES person_record (record_id)
              ON DELETE RESTRICT,
  basis       text NOT NULL,
  score       numeric(4,3),
  decided_by  text NOT NULL,
  evidence_id uuid,
  recorded_at tstzrange NOT NULL
              DEFAULT tstzrange(now(), NULL, '[)'),
  PRIMARY KEY (from_record, to_record,
               recorded_at WITHOUT OVERLAPS),
  CONSTRAINT no_self_link CHECK (
    from_record IS DISTINCT FROM to_record
  )
);

-- Erasure, recorded as a decision with a ground
-- and a named exception for anything retained.
CREATE TABLE erasure_action (
  record_id      uuid PRIMARY KEY
                 REFERENCES person_record (record_id)
                 ON DELETE RESTRICT,
  requested_at   timestamptz NOT NULL,
  completed_at   timestamptz,
  ground         text NOT NULL,
  retained_under text,
  retained_what  text,
  CONSTRAINT ground_is_art17_1 CHECK (
    ground IN ('17.1.a','17.1.b','17.1.c',
               '17.1.d','17.1.e','17.1.f')
  ),
  CONSTRAINT kept_under_art17_3 CHECK (
    retained_under IS NULL OR
    retained_under IN ('17.3.a','17.3.b','17.3.c',
                       '17.3.d','17.3.e')
  )
);

That last table turns a compliance promise into a checkable fact. An erasure is not a delete statement; it is a decision, taken on a named ground from Article 17(1), which either removed everything or retained something under a named exception in Article 17(3). Writing the article references into a CHECK constraint means the database refuses an erasure nobody can justify, and an auditor can count them. The score column carries the match score that justified a merge, because a threshold change six months later is otherwise impossible to reason about.

Now Anjali, through the schema.

-- 2013-06-14, registration.
INSERT INTO person_record (record_id, created_by,
                           created_via)
VALUES ('b7c2...4001', 'desk:sunita', 'in-person');

INSERT INTO person_fact
  (record_id, attribute, value, valid_at,
   asserted_by)
VALUES
 ('b7c2...4001', 'date_of_birth', '"1988-03-02"',
  daterange('1988-03-02', NULL),
  'evidence:birth-cert'),
 ('b7c2...4001', 'address',
  '"Flat 4, Sadashiv Peth, Pune 411030"',
  daterange('2013-06-14', NULL), 'self-declared');

-- 2019-04-03. She moved on 9 January. Close the
-- old belief; do not overwrite it.
UPDATE person_fact
SET    recorded_at = tstzrange(
         lower(recorded_at), now(), '[)')
WHERE  record_id = 'b7c2...4001'
AND    attribute = 'address'
AND    upper_inf(recorded_at);

INSERT INTO person_fact
  (record_id, attribute, value, valid_at,
   asserted_by, supersedes)
VALUES
 ('b7c2...4001', 'address',
  '"Flat 4, Sadashiv Peth, Pune 411030"',
  daterange('2013-06-14', '2019-01-09'),
  'self-declared', 41),
 ('b7c2...4001', 'address',
  '"12 Baner Road, Pune 411045"',
  daterange('2019-01-09', NULL),
  'self-declared', NULL);

Three rows now exist for one attribute. One is closed in transaction time and says she lived at Sadashiv Peth indefinitely, which is what the register believed until 3 April 2019. Two are open and say Sadashiv Peth until 9 January 2019 and Baner since. The letter posted on 20 February 2019 is permanently explainable without anybody having to remember.

The merge on 5 May 2021 is one row in record_link and touches nothing else. The split on 30 September 2023 creates record 7734, moves nothing, and closes no edge that was not wrong. The erasure request of 12 February 2026 becomes one row in erasure_action with a ground of ‘17.1.a’, a set of deletes against person_fact and record_identifier, and a retained_under of ‘17.3.e’ covering two facts held for a live legal claim.

Reading the schema back#

A design is worth what it can answer. Any person schema that cannot answer all six of these is incomplete.

1. Where did this person live on a past date, as we understand it today? Filter person_fact on valid_at containing the date, recorded_at open.
2. Where did we believe they lived on that date, on that date? Add recorded_at containing the timestamp. This defends a past decision.
3. Who told us, and on what evidence? Join through evidence_id and read asserted_by. If either is null, the fact is a rumour the schema should have refused.
4. Which records are this person, now and before the split? Walk record_link to its transitive closure, once over rows with recorded_at open and once as at the earlier timestamp.
5. What did we erase, on what ground, and what survived under which exception? One row of erasure_action.
6. If the survivorship rules changed today, what would the golden view become? Recompute from person_fact. The golden view is a view; if it is a table in your system, it is a source pretending to be a conclusion.

A checklist to run against an existing person schema, which usually fails three of these on the first pass.

4.98 Common wrong ideas#

Wrong: A national identity number is the natural primary key for a person. Right: It fails at least four of the six properties a key needs. Not everyone has one, people acquire and lose them, schemes exhaust their values as Sweden reported in February 2007 and Denmark showed by abandoning its modulus 11 check in October 2007, and in the United States Congress has barred funding for a national health identifier since the fiscal 1999 rider known as Section 510.

Wrong: One person, one row is achievable if the matching is good enough. Right: It is achievable at no matching quality, because deciding that two records describe one body is an inference under uncertainty with two error directions that trade against each other. Harris County, Texas alone held 2,488 patients named Maria Garcia, 231 of them sharing a date of birth. Design for a revisable grouping of records, not a table of people.

Wrong: A change and a correction are the same operation, since both end with a new value. Right: A change says the world moved; a correction says we were wrong. A valid-time-only table cannot tell them apart, which is exactly why it cannot defend a decision. Only a bitemporal table separates “she moved in January” from “we recorded the wrong street in 2013”.

Wrong: A soft delete satisfies the right to erasure because the data is no longer used. Right: Article 17 concerns data ceasing to exist in identifiable form, not a flag in an application. The row is still in the table, the backup, the replica, the search index and the warehouse, and it is still being processed. Erasure means choosing tombstoning, cryptographic erasure or hard deletion, and naming which Article 17(3) exception covers whatever survives.

Wrong: Pseudonymizing the record is as good as deleting it. Right: Recital 26 states that data which have undergone pseudonymization and could be attributed to a person using additional information remain information on an identifiable natural person. Pseudonymization lowers risk and is worth doing; it does not end processing and it does not answer an erasure request.

Wrong: When two records turn out to be one person, copy everything onto the survivor and delete the loser. Right: That destroys the only information that could undo the merge. Keep both records untouched and express the merge as a separate, time-stamped, revocable assertion that the two keys denote one person. Unmerge then costs one row; after a destructive merge it is usually impossible.

Wrong: The golden record is the true version of the person. Right: It is the output of survivorship rules applied to sources at a moment; change the rules and it changes with no change in the world. The DAMA Data Management Body of Knowledge second edition of 2017 warns that the term can mislead. A golden record must be derived rather than authored, must retain which source supplied each winning field, and must be rebuildable.

Wrong: SQL:2011 gave every database temporal tables, so this is solved. Right: The standard defined application-time period tables, system-versioned tables, FOR PORTION OF and WITHOUT OVERLAPS in December 2011, and engines implemented different subsets over the following fourteen years. SQL Server 2016 gives system versioning only. PostgreSQL added temporal keys and PERIOD foreign keys only in version 18 of 25 September 2025 and as of August 2026 still has no built-in system versioning.

4.99 Chapter summary in 20 lines#

1. An identity system is a database of people, and its hard problems are database problems in a different vocabulary.
2. A person record is five separate things: its own key, external identifiers, attributes, provenance, and metadata about the row.
3. It must never hold authenticator secrets, or read access to the directory becomes impersonation access to everyone in it.
4. It must never hold card sensitive authentication data, which PCI DSS version 4.0.1 requirement 3.3.1 forbids storing after authorization.
5. It must never hold a free-text notes column, which within two years becomes an uncontrolled store of health, ethnicity and staff opinion.
6. It must not hold Article 9(1) special category data beside ordinary attributes, where it inherits the wrong access rules.
7. Codd named the primary key in Communications of the ACM in June 1970, and his assumption that one exists in the data fails for people.
8. Natural keys on people fail six ways: not unique, not stable, not universal, reassigned, exhausted, and sometimes forbidden by law.
9. A person key must be system-assigned, meaningless, immutable and never reused, with external numbers in a namespaced child table.
10. One person, one row cannot be kept: five million people yield hundreds of pairs sharing a common name and a birth date.
11. A duplicate splits one person across records; an overlay puts two people in one record and is worse, because it looks consistent.
12. A golden record is a policy output: it must be derived, must retain its contributions, and must be rebuildable from sources and rules.
13. ISO 8000 covers this, with part 110 on exchanging master data and parts 120, 130 and 140 on provenance, accuracy and completeness.
14. Snodgrass and Ahn named valid time and transaction time at SIGMOD in Austin in May 1985, and the names have held for forty years.
15. Every attribute that can change needs a closed-open valid period, and a date of birth needs no valid period but does need a transaction one.
16. Valid time may be revised; transaction time is append-only and never edited, and that rule is what makes a record auditable.
17. Only the second temporal query, asking what we believed then about then, can defend a decision taken in the past.
18. SQL:2011 standardized periods, WITHOUT OVERLAPS and FOR PORTION OF in December 2011, and engines have shipped different subsets since.
19. Soft delete is not erasure under Article 17; the honest options are tombstoning, cryptographic erasure, and hard deletion with its audit cost.
20. Merges and splits must be revocable assertions about record keys rather than edits to rows, or unmerge becomes impossible.

Chapter sources: E. F. Codd, “A Relational Model of Data for Large Shared Data Banks”, Communications of the ACM volume 13 number 6, June 1970, for the definition of a primary key. Richard T. Snodgrass and Ilsoo Ahn, “A Taxonomy of Time in Databases”, Proceedings of the ACM-SIGMOD International Conference on Management of Data, Austin, Texas, May 1985, pages 236 to 246, for transaction time, valid time and user-defined time and for the rollback, historical and temporal database distinction; Snodgrass, “The Temporal Query Language TQuel”, ACM Transactions on Database Systems volume 12 number 2, June 1987, pages 247 to 298; The TSQL2 Temporal Query Language, committee chaired by Snodgrass, Kluwer, 1995; and Snodgrass, Developing Time-Oriented Database Applications in SQL, Morgan Kaufmann, July 1999, 504 pages plus front matter, ISBN 1-55860-436-7, all checked against the author’s own publication list in August 2026. ISO/IEC 9075-2:2011, SQL Part 2: Foundation, of December 2011, withdrawn in favour of ISO/IEC 9075-2:2023 with Corrigendum 1 of 2026; and Krishna Kulkarni and Jan-Eike Michels, “Temporal features in SQL:2011”, ACM SIGMOD Record volume 41 number 3, September 2012, pages 34 to 43. IBM Redbook SG24-7892-00, DB2 10 for z/OS Technical Overview, December 2010, for business time and system time; MariaDB documentation on system-versioned tables and application-time periods in the 10.3 and 10.4 series and WITHOUT OVERLAPS in 10.5.3; PostgreSQL documentation and versioning policy for release 18 of 25 September 2025, temporal keys, PERIOD foreign keys, range types, exclusion constraints and btree_gist, checked in August 2026. Regulation (EU) 2016/679, Articles 5(1)(d), 5(1)(e), 5(2), 9(1), 9(2), 16, 17(1), 17(2), 17(3), 19 and 89(1), and Recital 26; UK Information Commissioner’s Office guidance on the right to erasure, for the one-month response period and backups put beyond use. Commission Nationale de l’Informatique et des Libertes, “Solutions for a responsible use of the blockchain in the context of personal data”, September 2018. NIST Special Publication 800-88, Guidelines for Media Sanitization, Revision 1 of 17 December 2014 and Revision 2 finalized September 2025, for cryptographic erase. ISO 8000-2:2022, fifth edition of September 2022, ISO/TC 184/SC 4, at stage 90.92 with a draft revision in progress as of August 2026; ISO 8000-110:2021, ISO 8000-120:2016, ISO 8000-130:2016, ISO 8000-140:2016 and ISO 8000-150:2022. DAMA International, Data Management Body of Knowledge, second edition, 2017, section 1.3.3.2. PCI DSS version 4.0.1 of June 2024, requirements 3.3, 3.3.1 and 3.3.1.3, with v4.0 retired on 31 December 2024. NHS Data Model and Dictionary attribute page for NHS NUMBER, and published histories of the format issued to newborns from July 1995 and made mandatory on 1 April 1997. The Money Laundering, Terrorist Financing and Transfer of Funds (Information on the Payer) Regulations 2017, regulation 40. Bipartisan Policy Center issue brief on matching patients, for the Harris County figures, the eight to twenty per cent error range, the survey of 128 chief information officers, the nineteen per cent adverse event figure and the Sharp HealthCare figures for 2001 to 2012. Skatteverket material and published descriptions of the Swedish personnummer and the Danish CPR number. Contemporary reporting and AHIMA material on Section 510 of the appropriations rider in force since fiscal year 1999. RFC 9562, Universally Unique IDentifiers, May 2024.