Data Discovery
"Customer places an order containing products" is already a data model: Customer, Order, OrderItem, Product. Entities are the nouns, relationships are the verbs, and the hidden entity is the one the sentence needs but does not name.
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.
Before I design a database, how do I find out what data this system is actually about?
I know the store needs a database and I have opened a schema tool. I have a users table and a products table because every tutorial has them, and now I am staring at the space where "orders" should go and I do not know what columns it has or why.
Start from the tables you have seen before — users, products, orders — and fill in columns from memory of other schemas. It produces a diagram quickly, and a diagram with boxes and lines looks like a design.
The schema is a memory of other systems, not a description of this one. The orders table has a total column because other schemas had one, and no order_items table because the tutorial's orders had one product each. The first cart with two products has nowhere to go.
- The schema is a memory of other systems, not a description of this one. The
orderstable has atotalcolumn because other schemas had one, and noorder_itemstable because the tutorial's orders had one product each. The first cart with two products has nowhere to go. - Columns are invented instead of found.
status VARCHARappears with no idea of which statuses exist;pricesits on the product with no thought about whether it changes; the diagram is complete and every box is a guess. - Nothing in the diagram connects to a requirement, so when a requirement arrives — "customers can reorder a past order" — there is no way to tell whether the schema supports it except by trying.
The move
Precisely enough to apply it to a problem you have never seen — not a slogan.
- Take the requirement as sentences in plain language and read them for nouns. Each noun that the system must remember something about is a candidate entity. "Customer places an order containing products" gives Customer, Order, Product — three nouns, three candidates.
- Read the same sentences for verbs and prepositions between nouns. Each is a candidate relationship, and the relationship has a shape: does one customer place many orders? Can one order contain many products, and can one product be in many orders? A many-to-many answer is the signal that a noun is missing.
- Name the missing noun. "An order containing products" is many-to-many, and the thing in between — this product, in this order, at this quantity, at this price — is an entity the sentence never said out loud: OrderItem. Most of the important entities in a system are these unnamed ones, and finding them is the move.
- For each entity, ask what the system must remember about it, as attributes, and which of those are facts about the entity itself versus facts about a relationship. A quantity is not about a product or an order; it is about a product-in-an-order. That question is how attributes find their table without needing to know what a normal form is — Database Engineering will give the rule later (Normalization: 1NF to BCNF).
One sentence, read twice
The reflex reads the sentence for tables. The move reads it once for nouns and once for verbs, and the second reading is where the entity that matters comes from. The table shows what each word became.
| Word | Kind | Becomes | Because |
|---|---|---|---|
| Customer | noun | Entity: Customer | The system remembers who placed which order |
| places | verb | Relationship: Customer → Order (one to many) | A customer places many orders; an order has one customer — until guest checkout |
| an order | noun | Entity: Order | Must survive a restart; has a status, a time, a total |
| containing | verb | Relationship: Order ↔ Product (many to many) | An order holds many products; a product appears in many orders |
| (the join) | hidden noun | Entity: OrderItem | Quantity and price paid are facts about a product-in-an-order, not about either alone |
| products | noun | Entity: Product | Has a name, a current price, stock |
The drawing, before it is a schema
The diagram is the deliverable of data discovery: entities and the shape of what connects them, with nothing about keys, types or indexes. That is not laziness; it is the boundary. Database Engineering takes this drawing and produces a schema, and it will do that better if the drawing is honest about what was found rather than dressed as a design.
What the sentence did not say
A sentence hides entities and it hides questions. The board holds the ones the store sentence left open; each is a fact about the data that would change the drawing, so each has to become a question with an experiment before the drawing becomes a schema.
- ✓Four entities: Customer, Order, OrderItem, Product; Payment as a fifth once "pays for" is read.
- ✓Order → OrderItem is one to many; OrderItem carries quantity and the price paid.
- ~One customer per order. Guest checkout would make Customer optional on Order, or replace it with an email.
? Where does the cart go?
becomes Is a cart a separate entity that becomes an order at checkout, or an order in a "draft" status — and does a cart need to survive a restart at all?
experiment Write both versions on paper and walk "add item, close browser, come back, check out" through each; see which needs a copy step.
? Prices change.
becomes When an admin changes a product's price, what should an order placed yesterday show?
experiment Ask the founder with the concrete case; the answer decides whether OrderItem stores the price (Snapshots vs References).
? Stock.
becomes Is stock a number on Product, or a sum over movements in and out — and does anyone need to know why stock changed?
experiment Ask whether "why is stock wrong?" is a question anyone will need to answer; if yes, movements are the entity and the number is a query.
How to do it
Most important first.
- Write the core workflow as plain sentences with a subject, a verb and an object. "Customer adds a product to a cart. Customer checks out the cart. The store creates an order. Customer pays for the order." (Requirement Discovery).
- Underline the nouns; each is a candidate entity. Discard the ones the system does not need to remember anything about ("the store" here is the system itself).
- For each verb between two nouns, ask "one or many?" in both directions. Every many-to-many is a missing entity; name it.
- For each entity, list what must be remembered about it. If an attribute only makes sense with two entities in view, it belongs to the entity between them.
- Draw the entities and relationships, then hand the drawing to Database Engineering for keys, constraints and normal forms — do not skip to the table definition (From Requirements to Tables).
Worked on a concrete problem
The move has to produce something. This is what it produced.
- "Customer places an order containing products." Nouns: Customer, Order, Product. Verbs: places (Customer → Order, one to many — a customer places many orders, an order has one customer), containing (Order → Product, many to many — an order has many products, a product is in many orders). The many-to-many names OrderItem: order, product, quantity, and the price at the time. Four entities from one sentence, and the important one was invisible.
- The chat app: "Users have conversations in which they send messages; each user has read some of the messages." Nouns: User, Conversation, Message. Users ↔ Conversations is many-to-many, so Participant appears (which user is in which conversation, since when). "Has read" is a relationship between User and Message that carries a fact — when — so it is an entity too: ReadState. Two of the five entities were not in the sentence.
- The URL shortener: "Anyone can shorten a URL and see how many times the short link was visited." Nouns: URL, ShortLink, Visit. The count is not an attribute; "how many times" is a query over Visit rows, and deciding that now is what makes per-day analytics possible later without a migration.
How you know it worked
What now exists that did not before, and what question you can now ask.
- Every entity in the drawing can be pointed to in a requirement sentence, and every relationship to a verb.
- At least one entity in the drawing was not a noun in any sentence — you found the join between two nouns and gave it a name.
- Every attribute has an owner you can justify in one sentence, and quantity is not on Product.
- When a new requirement arrives, you can check it against the drawing by reading it aloud and looking for missing nouns.
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.
- ?What are the nouns in this requirement, and which of them must the system remember something about?
- ?For each verb between two nouns, is it one or many in each direction — and where is the many-to-many hiding an entity?
- ?Which attribute only makes sense with two entities in view, and what is the entity between them?
- ?What has to be true of this data after a restart, and what could be recomputed?
What can go wrong
- Every noun becomes a table. "The customer's address" gives an Address entity before anyone knows whether addresses are shared, edited, or ever looked at again; a column would have done. The test is "must the system remember something about this on its own?"
- The drawing is polished into a schema before the requirements are done. Data discovery is cheap to redo on paper and expensive to redo in migrations; stay on paper until the workflow sentences are stable.
- Relationships are assumed rather than asked. "An order has one customer" is true until guest checkout, and "a product has one price" until the first price change — which is the next lesson but one (Snapshots vs References).
- Reading sentences for nouns is slower than drawing the tables you already know, and on a system you have built before it is redundant.
- Finding the hidden entities early means more tables in V1 than the tutorial had; OrderItem is more code than a
product_idon Order, and it is right. - The drawing is not a schema, and someone will have to turn it into one with keys and constraints — this lesson deliberately stops before that.
- "This is normalisation." It is the step before: finding the entities. Normalisation is the discipline that checks the result, and it lives in Database Engineering.
- "Nouns are tables." Nouns are candidates. The system is not a table, the address may be a column, and the most important tables are the ones that were verbs.
- "Get the model right first and the code follows." The model is discovered from the workflow, and the workflow changes; the model will change too, which is why it stays on paper as long as it can.
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.
- GENERALNouns, verbs and the hidden entity between a many-to-many apply to any system that remembers anything, relational or not; a document store still has to decide whether OrderItem is embedded or referenced.
- STAGE-SPECIFICGreenfield, the sentences are all you have and the drawing is the deliverable. In an existing system, the same reading is done against the existing schema to find where the new requirement's nouns already live — and where the missing entity has been faked with a comma-separated column.
- ILLUSTRATIVEThe store, chat app and shortener sentences are invented to show the shape of the move.
Where the depth lives
This domain asks the question and hands the answer off by name.