A booking tool's account row carries one identity-provider column. What does replacing it with a separate identity table buy, and what does it cost?
answer
- one human, several ways in
- identity rows, not a provider column
- constrain issuer and subject together
- a lookup on every federated sign-in
- the last route cannot be removed
basics
~20 sA child identity table of (issuer, subject) rows, uniquely constrained on both columns together, lets one account be reached by several sign-in routes. It costs a lookup on every federated sign-in and a last-route rule the schema cannot enforce.
solid answer
~50 sA `provider` column on the account row encodes the assumption that a person has exactly one way in, and that assumption dies the moment a second identity provider is connected to a tool people already have passwords for. The shape that survives is a child table: one row per identity, carrying `user_id`, the `issuer` that asserted it, the `subject` that issuer minted, and when and how the row was linked — with `unique (issuer, subject)` as the key. Both columns together, because a subject is only unique inside the issuer that minted it. What it buys is that a second provider, a re-keyed connection, an unlink and a merge all become row operations instead of schema changes. What it costs is an indexed lookup on every federated sign-in, a second query wherever the UI shows how someone signs in, and one invariant — *this account must keep at least one usable way in* — that no constraint can express for you.
code
pseudocode · 14 lines# sign-in: the only automatic join is an exact identity-row match
on federated_sign_in(iss, sub):
identity = identities.find(issuer = iss, subject = sub) # unique (issuer, subject)
if identity is not null:
identity.last_seen_at = now
return identity.user_id
return NO_ACCOUNT_YET # never fall through to an address match
# unlink: the rule no constraint can express for you
on unlink(user_id, identity_id):
routes_left = identities.count(user_id) - 1
if routes_left == 0 and not has_usable_password(user_id):
reject "this is the account's last way in"
identities.delete(identity_id)go deeper
Recall the shape: an account is who the person is to the product, and a separate table lists the ways that person can sign in. One person, several routes, one account.
Be able to explain the key. The pair of issuer and subject is unique together because a subject only means anything inside the issuer that minted it, and an asserted address is an attribute rather than a key.
Show the operational consequences: the extra probe on the sign-in path, the screens that silently go stale when they keep reading a column, and the invariant that must live in code because no constraint can express 'keep at least one way in'.
Argue the cost of getting it wrong late. Retrofitting an identity table after two providers are live means a data migration over live sign-ins, and the only cheap moment to choose this shape is before the first federated connection exists.
## Where the trouble starts A laboratory-booking tool that has only ever had local passwords keeps its authentication data on the account row: an address, a password hash, and — when the research institute's identity provider is first connected — a `provider` column and a subject column bolted on beside them. That shape works exactly once. It encodes the assumption that a person has one way in, and the entire point of federated sign-in arriving at a tool with two years of existing users is that the same person now has two: the password they have used since the tool was bought, and the identity the institute's provider will assert from next Monday. ## One account, many identities The shape that survives is a child table. The account row keeps who the person is to your product — display name, bookings, permissions. A separate identity table keeps every route by which that person can arrive: - `user_id` — the account this identity resolves to - `issuer` — the party that asserted it, recorded exactly as that party names itself - `subject` — the identifier that party minted for the person, opaque to you - `linked_at`, `linked_by`, `link_method` — when the row was written, by whom, and whether the link was interactive or automatic - `last_seen_at` — the most recent sign-in that used this route The constraint that matters is `unique (issuer, subject)` — both columns, together. A subject is only unique inside the issuer that minted it. In OpenID Connect, `sub` is specified as unique per issuer, not globally; a SAML 2.0 `NameID` is scoped by the asserting party in the same way. Constrain the subject alone and the first value collision between two connected providers silently joins two strangers into one account. What does **not** belong in that key is the address. An address is an attribute of a person that the asserting party may change — a name change, a department move, a domain migration all rewrite it — and it is asserted by a party whose verification policy is its own. It is a useful hint for showing a human which identity is which. It is not a key. ## What the two shapes actually differ on | Situation | `provider` column on the account | separate identity table | |---|---|---| | A second provider is connected | schema change, or one identity overwrites the other | insert a row | | The same person keeps their password and gains a federated route | not representable | two rows, one account | | The customer re-keys a connection and subjects change | in-place update with no history | new rows, old rows retained and auditable | | Someone asks which routes their account has | one value, no history | a query that answers honestly | | Two accounts turn out to be one person | column overwrite, losing a route | re-point identity rows onto the survivor | | Unlinking one route | null out a column | delete one row, subject to a rule | ## What it costs The costs are real and worth stating plainly rather than pretending the child table is free: 1. **A lookup on every federated sign-in.** The path is now: read `iss` and `sub` from what arrived, probe the unique index, get a `user_id`, load the account. With the index in place it is one probe, so the cost is bounded — but it is a second round trip on the hottest path you have. 2. **The account row no longer tells you how someone signs in.** Every screen, export and support tool that used to read one column now needs a join or a second query, and the ones that are not updated quietly start lying. 3. **Link and unlink become code paths.** They were column updates; they are now row operations with rules attached, and rules need tests. 4. **An invariant the schema cannot hold.** `unique (issuer, subject)` stops two accounts claiming one identity. Nothing stops an account ending up with zero identities and no usable password — an account with data, permissions and history and no way in at all. ## The invariants worth writing down - **(issuer, subject) is unique across the whole table.** One asserted identity resolves to at most one account, always. - **An account must retain at least one usable authentication route.** Enforce it in the sign-in and unlink code, on every path that removes a route: unlink, merge, and the moment local passwords are switched off. - **Every identity row records how it was linked.** When a link is later disputed, or an incident asks which accounts were joined automatically, that column is the whole investigation. - **Identity rows are never edited in place to change the subject.** A changed subject is a different identity: insert, and retire the old row with its history intact. The pay-off is that the awkward operations of federated identity — a second provider, a merge, an unlink, an enforcement flip — all reduce to inserting, moving or deleting rows in one narrow table whose rules you can state in four lines.
- Should the table also be unique on (user_id, issuer)?That is a policy choice, not a correctness one. Adding it means an account holds at most one identity per provider, which is usually what you want and makes a re-key obvious: the insert fails instead of quietly leaving two live rows for one person at one issuer. Omit it only if you genuinely support one person holding several distinct identities at the same provider, and can explain to support which one signed in.
- The institute re-keys its connection and every subject changes. What does the identity table let you do that a column would not?Insert the new identity rows alongside the old ones and let people land on their existing accounts as they sign in, then retire the unmatched old rows once the tail has drained. With a single column you would have to overwrite every value in one shot, with no way to run the two routes side by side and no record of what the old subject was when someone disputes where their bookings went.
- What does last_seen_at on an identity row earn its keep for?It answers the two questions support actually asks: which route this person really uses, and whether a route is dead. A row untouched since the enforcement flip is either a person who never moved over or an identity the provider stopped asserting, and both are worth finding before someone reports being locked out.
A shared laboratory's key register. The register lists people, and separately lists keys, each stamped with which locksmith cut it — because two locksmiths both number their keys from one, and key 45 from one of them opens nothing of the other's. A person may hold several keys; the register's one rule that no lock can enforce is that you do not take back someone's last key while their samples are still in the freezer.
saying these in an interview costs you the question
- Puts a provider name and subject in columns on the account row.
- Makes the asserted address the unique key across providers.
- Constrains the subject alone, so two issuers can collide.
- Assumes a subject identifier is globally unique without its issuer.
- Believes a database constraint can enforce keeping one sign-in route.
- Edits an identity row's subject in place when a connection is re-keyed.