Entities From Requirements
A requirement is a source of entities only if it is read for them. The rule is a noun the system must remember; the traps are nouns that are really attributes, attributes that are really entities, and the entity you only find by asking "one or many?".
The situation, the reflex, and why it stalls
Every lesson starts where being stuck starts: someone has a problem, and the first move that comes to mind feels like progress.
Given a list of requirements, how do I decide which nouns deserve to be entities, which are attributes, and which are neither?
I have a page of requirements from the founder and I have circled every noun. There are forty of them. "Discount", "delivery slot", "wishlist", "invoice", "customer segment", "return". I cannot make forty tables and I do not know how to choose.
Pick the nouns that sound like tables — the ones that feel like "things" — and make the rest columns. It is fast, and for the obvious cases (Product is a table, colour is a column) it is even right.
"Sounds like a thing" is a feeling about English, not about the system. Address sounds like a thing and is often a column; "paid" sounds like a state and is often an entity (Payment) with its own time, amount and provider reference.
- "Sounds like a thing" is a feeling about English, not about the system. Address sounds like a thing and is often a column; "paid" sounds like a state and is often an entity (Payment) with its own time, amount and provider reference.
- Nouns that are the same entity under two names become two tables. "Customer" and "user" and "account" get three tables because three people wrote the requirements; a later join across all three is the bug that reveals it.
- Nouns that are two entities under one name become one table. "Order" in "the customer places an order" and "order" in "the warehouse picks the order" share a word and not a lifecycle, and the single table gains twenty nullable columns to serve both.
The move
Precisely enough to apply it to a problem you have never seen — not a slogan.
- Apply one test to each noun: must the system remember something about this, on its own, over time? If yes, it is an entity. If it is only ever a fact about something else — a colour, a street, a status — it is an attribute of the entity it describes. "Sounds like a thing" is replaced by "has its own facts and its own life".
- Test each candidate for identity: can two of them be the same in every attribute and still be different? Two orders with identical items placed a minute apart are different orders — entity. Two identical addresses are the same address for every purpose the store has — attribute, until a requirement says otherwise.
- Ask "one or many?" of every relationship, in both directions. Many-to-many is a missing entity (Data Discovery); many-to-one from something that has its own facts is a real relationship; one-to-one is usually the same entity written twice.
- Merge nouns that share identity and split nouns that share only a word. "Customer/user/account" collapse to whichever the domain language prefers; "order" splits into Order and, when the warehouse requirement arrives, Shipment — a different thing with a different lifecycle that refers to the order (Naming and Domain Language).
The question that sorts a noun
The reflex asks whether a noun sounds like a thing. The ladder sharpens that into a question the requirement can answer, and the best form is the one that tells you where the noun's facts should live.
why The best form makes the answer a fact about the system rather than about English: if the order must show the address as it was, the address is a snapshot on the order; if addresses are managed on their own, they are an entity. The vague form can only be answered by taste.
How mis-sorted nouns fail
Each row is a sorting mistake that compiles, passes its tests and fails on a requirement later. The response column is the fix that was available on paper.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
Payment modelled as paid_at on Order | A retried or refunded payment has nowhere to record itself; provider references live in a notes column | A transition with its own facts was treated as a status | Payment entity: order, amount, provider reference, status, times |
| "User" and "customer" as two tables | Order history joins across both; a customer exists twice with different emails | Synonyms modelled as entities | One entity, one name from the domain language, a glossary for the rest |
| Order carries pick, pack, carrier and tracking columns | An order shipped in two parcels has one tracking number; half the columns are null for unshipped orders | Two lifecycles under one noun | Shipment entity, many-to-one to Order |
| Addresses deduplicated into a shared table | One customer's correction changes another's order | Identity assigned to a value | Address as a snapshot on the order, or an entity owned by one customer |
| Colour, size and country as entity tables | Three foreign keys and three admin screens for one product line | Lookups promoted before they had facts of their own | Columns, or a single lookup table, until a requirement gives them facts |
What the sorted list looks like
The output is text, not a schema: entities with the facts they own, attributes with the entity they belong to, and the relationships with their cardinality. This is the page Database Engineering starts from.
1ENTITIES (own facts, own life)2 Customer email, name one → many Order3 Product name, current price, stock one → many OrderItem4 Order placed at, status, address (snap) one → many OrderItem, one → many Payment, one → many Shipment5 OrderItem quantity, price paid many → one Order, many → one Product6 Payment amount, provider ref, status, at many → one Order7 Shipment carrier, tracking, shipped at many → one Order8 Return reason, refund amount, at many → one OrderItem9 10ATTRIBUTES (facts about something else)11 colour, size → Product12 address, total → Order (total recomputable from items — keep? see what-must-persist)13 discount amount → OrderItem (until coupons have rules of their own)14 15NOT DATA16 the store, the website, the checkout page, "the system"17 18GLOSSARY19 user (customer-facing docs) = Customer ; user (admin docs) = Admin ; shopper = CustomerNotice that the many-to-many between Order and Product never appears — it was resolved into OrderItem before the list was written. And the open question on total is left open on purpose; it belongs to the next lesson but one.
How to do it
Most important first.
- For each circled noun, write the facts the system must remember about it. No facts of its own → attribute. Facts of its own → entity candidate.
- For each candidate, ask whether two identical ones can be different. If not, it is a value, and probably an attribute or a lookup (Value Objects).
- Put each relationship in a sentence with "one" or "many" on both ends. Write the ones that are many-to-many on a separate list; each needs a name.
- Group nouns by what they refer to; where a group has several names, pick one from the domain language and keep the others as aliases in a glossary.
- Where one noun has two lifecycles, split it, and name the relationship between the halves.
Worked on a concrete problem
The move has to produce something. This is what it produced.
- Forty nouns, sorted. Entities: Product, Customer, Order, OrderItem, Payment, Return (its own reason, time, refund), DeliverySlot (its own capacity and date), Invoice (its own number and issue date, even if it copies the order). Attributes: colour (of a product), address (of an order — a snapshot), discount amount (of an order line, until coupons are an entity with their own rules). Neither: "the store", "the website", "the checkout page" — the system, not its data.
- "Customer", "user", "account", "shopper" all appeared. Facts remembered: who placed orders, an email, a name. One entity; the domain language says Customer; the glossary records that "user" in the admin requirements means Admin, which is a second entity, not an alias.
- "Order" split. Requirement A: "customer places an order". Requirement B: "the warehouse packs and ships the order in one or more parcels". B's order has a pick list, a carrier and tracking numbers per parcel; A's has items, a total and a payment. Two entities: Order and Shipment, with Shipment → Order many-to-one, because an order can ship in several parcels — a fact only visible once the noun was split.
How you know it worked
What now exists that did not before, and what question you can now ask.
- Every noun on the page has become an entity, an attribute of a named entity, or been struck through as "not data".
- The glossary has more names than the entity list, because synonyms were merged rather than modelled.
- At least one noun was split, and the relationship between the halves has a direction and a cardinality.
- The one-or-many list has no unanswered entries.
The questions you can now ask
The field this whole domain exists for. After this lesson, these are the questions to put to an unfamiliar problem.
- ?Must the system remember something about this noun on its own, over time — or only as a fact about something else?
- ?Can two of these be identical in every attribute and still be different?
- ?Are these two names the same entity, and is this one name two entities with different lifecycles?
- ?Which status column in this design is a Payment, a Shipment or a Return waiting to be named?
What can go wrong
- Over-promotion: every attribute with a lookup list becomes an entity. Colour, size and country get tables and foreign keys before anyone knows whether the store sells more than one size. Promote when the thing has its own facts, not because it could.
- Under-promotion: a status column that is really an entity. "paid_at" on Order works until a payment is retried, refunded, or split, and then three timestamps and a provider reference all need to live somewhere — Payment was an entity all along.
- Identity by attributes: an address table deduplicated by street and city, so that when one customer corrects their street, another customer's order changes.
- Sorting forty nouns is an afternoon that produces no code; on a domain you know, most of the sorting is instant and the afternoon is wasted.
- Splitting Order into Order and Shipment is more tables and more joins today, against nullable columns and conditionals later. The split is right when the lifecycles differ, and premature when they do not yet.
- A glossary has to be maintained, and a stale glossary is worse than none.
- "Entities are the tables." Entities become tables, or documents, or both; the decision about storage shape is the next domain's (SQL vs NoSQL: Choosing a Data Model).
- "Fewer entities is simpler." Fewer entities than the domain has means wider tables with more nullable columns and more code that checks which kind of row this is. Simpler is matching the domain, not minimising boxes.
- "Status is always an entity in hiding." Often it is a column. It becomes an entity when the transition has its own facts — a time, an actor, a reference — that someone will need to see.
Where this applies
Problem-solving advice is stated as universal far more often than it is. These labels say what each method is specific to — and where CONTESTED appears, the note gives the strongest form of the opposing view.
- GENERALThe "own facts, own life" test and the identity test apply to any domain; the specific nouns that fail or pass them are the domain.
- DOMAIN-SPECIFICIn a store an address is usually a snapshot attribute of an order; in a logistics product an address is an entity with geocoding, validation history and delivery notes. The same word, a different answer, because the system remembers different things about it.
- ILLUSTRATIVEThe forty nouns and the founder's page are invented; the count is for the shape of the argument.
Where the depth lives
This domain asks the question and hands the answer off by name.