{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"one-fact-in-one-place","title":"Where each fact should live","url":"https://addaly.com/learn/data-and-sql/one-fact-in-one-place","position":5,"of":66,"minutes":9,"answer":"Store each fact once, where it is true; a value copied onto a row is only a duplicate if it is still supposed to change when the original does.","keyIdea":"Store each fact once, where it is true; a value copied onto a row is only a duplicate if it is still supposed to change when the original does."},"body":{"format":"markdown","text":"## The rule, before the jargon\n\nNormalisation has an intimidating vocabulary and one idea underneath it: **every fact is stored once, in the table about the thing it is a fact about.** Everything else is a consequence.\n\nTake a badly shaped table:\n\n```\norder_id | customer_name | customer_phone | customer_city | product | qty | price\n```\n\nThree of those columns are facts about the customer, not about the order. So they are repeated on every order the customer places. Change the phone number and you must change it in 34 places, and last week's export still holds the old one. This is exactly the tuition-centre spreadsheet from the first lesson, and it is what normalisation removes.\n\n## The three steps, in plain language\n\n**Step one: no repeating groups.** A column named `phone1, phone2, phone3` is a list crammed into a row. So is a `tags` column holding `\"urgent,billing,vip\"`. Both are fine until you have to ask \"how many customers have a billing tag\", at which point you are writing string searches instead of counting rows. The fix is a second table with one row per phone or per tag.\n\n**Step two: no partial dependency.** If your key is `(order_id, product_id)` and a column depends only on `order_id` (the order date, say) it belongs in the orders table, not repeated on every line item. Otherwise two line items can disagree about when their own order happened.\n\n**Step three: no transitive dependency.** A column that depends on another non-key column belongs elsewhere. `customer_city` depends on the customer, and the customer depends on the order. Move it to `customers`.\n\nApplied to the table above:\n\n```sql\ncustomers   (id, full_name, phone, city)\norders      (id, customer_id, placed_at, status)\norder_items (order_id, product_id, qty, unit_price)\nproducts    (id, name, current_price)\n```\n\nNow the phone number exists in one row. The city exists in one row. A product's name exists in one row.\n\n## The exception that is not an exception\n\n`order_items.unit_price` looks like a violation: the price is a fact about the product, surely? It is not. `products.current_price` is what the product costs *now*. `order_items.unit_price` is what this customer *was charged*, which is a fact about that line of that order and must never change again. If you normalise it away and join to `products` for a price, every historical invoice silently rewrites itself the next time somebody runs a sale.\n\nThe same reasoning applies to a delivery address copied onto a shipped order, and a tax rate recorded on an invoice. **Anything that was true at a moment and must stay true is a fact about the event, not about the entity.** Copying it in is correct design, not duplication.\n\nThat distinction (a current value versus a recorded value) is the one people most often get wrong, in both directions.\n\n## When to denormalise on purpose\n\nNormalisation is a default, not a religion. Deliberate denormalisation is a real technique with a real cost:\n\n- A reporting table with one wide row per order, built nightly, because forty analysts joining six tables is slower and more error-prone than one scan.\n- A cached `order_count` on the customer row, because the count is read on every page load and computed rarely.\n\nBoth are fine if two conditions hold: the derived value has exactly one place that writes it, and there is a way to rebuild it from the source of truth. A cached count updated in three different code paths will drift, and once it has drifted nobody can tell which number is real. Write it in one place, and keep a job that recomputes it and complains when it disagreed.\n\nThe rule is: **normalise the system of record, denormalise the copies you make from it.**\n\n## How to tell if a table is wrong\n\nThree questions, faster than any theory:\n\n1. **Can I change one true fact about the world by updating one row?** If a change of city needs an UPDATE with a WHERE clause that touches many rows, the city is in the wrong table.\n2. **Can two rows disagree with each other about the same fact?** If `orders` has both `customer_city` and a `customer_id` pointing to a customer with a different city, your data already contains a contradiction and no query can tell you which side is right.\n3. **Is there a column whose value is a list?** Commas inside a cell mean a missing table.\n\n## The honest cost\n\nNormalised data needs joins, and joins are the part of SQL people find hard, which is most of the next module. Very wide, very denormalised tables are easier to query and are exactly what analytical warehouses build on purpose.\n\nThe trade is: normalised data is harder to read and almost impossible to make inconsistent; denormalised data is easy to read and drifts out of agreement with itself unless something rebuilds it. Systems that take writes should be normalised. Tables built for reading can be as flat as you like, as long as everyone knows they are derived."},"check":{"question":"An `invoices` table stores `tax_rate` on every row, even though each customer's region and each region's current rate already live in `customers` and `tax_rates`. A reviewer says the column duplicates a fact and breaks normalisation. Is the reviewer right?","options":[{"text":"Yes. The rate depends on the region, which depends on the customer, so it is a transitive dependency and belongs only in the tax table, reached by a join.","correct":false,"tempting":"Applies the third-normal-form test to a column that records history rather than a current attribute, which is the standard way this rule gets misused."},{"text":"Yes, but an acceptable one, since joining to the tax table on every invoice read would be too slow.","correct":false,"tempting":"Justifies the column by performance when the real justification is correctness; that framing invites someone to remove it during a later cleanup."},{"text":"No. The rate charged on that invoice was true at that moment and must stay fixed, so it is a fact about the invoice rather than about the region.","correct":true},{"text":"No, because tax rates are set by governments and are therefore reference data, which is always duplicated.","correct":false,"tempting":"Reaches the right verdict by an invented rule; reference data is normally stored once, and this column is an exception for a different reason."}]},"prev":{"slug":"keys-and-identity","title":"Keys: how a database knows one thing from another","url":"https://addaly.com/learn/data-and-sql/keys-and-identity"},"next":{"slug":"select-and-where","title":"SELECT and WHERE: asking for rows","url":"https://addaly.com/learn/data-and-sql/select-and-where"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Where each fact should live', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/one-fact-in-one-place (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Where each fact should live","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":1,"slug":"tables-types-and-the-question","title":"Tables, types and the question you are actually asking"},"answer":null,"keyIdea":"Store each fact once, where it is true; a value copied onto a row is only a duplicate if it is still supposed to change when the original does.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-rule-before-the-jargon","runs":[{"text":"The rule, before the jargon"}]},{"t":"p","runs":[{"text":"Normalisation has an intimidating vocabulary and one idea underneath it: "},{"text":"every fact is stored once, in the table about the thing it is a fact about.","b":true},{"text":" Everything else is a consequence."}]},{"t":"p","runs":[{"text":"Take a badly shaped table:"}]},{"t":"code","lang":"","label":"Text","text":"order_id | customer_name | customer_phone | customer_city | product | qty | price"},{"t":"p","runs":[{"text":"Three of those columns are facts about the customer, not about the order. So they are repeated on every order the customer places. Change the phone number and you must change it in 34 places, and last week’s export still holds the old one. This is exactly the tuition-centre spreadsheet from the first lesson, and it is what normalisation removes."}]},{"t":"h2","id":"the-three-steps-in-plain-language","runs":[{"text":"The three steps, in plain language"}]},{"t":"p","runs":[{"text":"Step one: no repeating groups.","b":true},{"text":" A column named "},{"text":"phone1, phone2, phone3","code":true},{"text":" is a list crammed into a row. So is a "},{"text":"tags","code":true},{"text":" column holding "},{"text":"\"urgent,billing,vip\"","code":true},{"text":". Both are fine until you have to ask “how many customers have a billing tag”, at which point you are writing string searches instead of counting rows. The fix is a second table with one row per phone or per tag."}]},{"t":"p","runs":[{"text":"Step two: no partial dependency.","b":true},{"text":" If your key is "},{"text":"(order_id, product_id)","code":true},{"text":" and a column depends only on "},{"text":"order_id","code":true},{"text":" (the order date, say) it belongs in the orders table, not repeated on every line item. Otherwise two line items can disagree about when their own order happened."}]},{"t":"p","runs":[{"text":"Step three: no transitive dependency.","b":true},{"text":" A column that depends on another non-key column belongs elsewhere. "},{"text":"customer_city","code":true},{"text":" depends on the customer, and the customer depends on the order. Move it to "},{"text":"customers","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"Applied to the table above:"}]},{"t":"code","lang":"sql","label":"SQL","text":"customers   (id, full_name, phone, city)\norders      (id, customer_id, placed_at, status)\norder_items (order_id, product_id, qty, unit_price)\nproducts    (id, name, current_price)"},{"t":"p","runs":[{"text":"Now the phone number exists in one row. The city exists in one row. A product’s name exists in one row."}]},{"t":"h2","id":"the-exception-that-is-not-an-exception","runs":[{"text":"The exception that is not an exception"}]},{"t":"p","runs":[{"text":"order_items.unit_price","code":true},{"text":" looks like a violation: the price is a fact about the product, surely? It is not. "},{"text":"products.current_price","code":true},{"text":" is what the product costs "},{"text":"now","i":true},{"text":". "},{"text":"order_items.unit_price","code":true},{"text":" is what this customer "},{"text":"was charged","i":true},{"text":", which is a fact about that line of that order and must never change again. If you normalise it away and join to "},{"text":"products","code":true},{"text":" for a price, every historical invoice silently rewrites itself the next time somebody runs a sale."}]},{"t":"p","runs":[{"text":"The same reasoning applies to a delivery address copied onto a shipped order, and a tax rate recorded on an invoice. "},{"text":"Anything that was true at a moment and must stay true is a fact about the event, not about the entity.","b":true},{"text":" Copying it in is correct design, not duplication."}]},{"t":"p","runs":[{"text":"That distinction (a current value versus a recorded value) is the one people most often get wrong, in both directions."}]},{"t":"h2","id":"when-to-denormalise-on-purpose","runs":[{"text":"When to denormalise on purpose"}]},{"t":"p","runs":[{"text":"Normalisation is a default, not a religion. Deliberate denormalisation is a real technique with a real cost:"}]},{"t":"ul","items":[[{"text":"A reporting table with one wide row per order, built nightly, because forty analysts joining six tables is slower and more error-prone than one scan."}],[{"text":"A cached "},{"text":"order_count","code":true},{"text":" on the customer row, because the count is read on every page load and computed rarely."}]]},{"t":"p","runs":[{"text":"Both are fine if two conditions hold: the derived value has exactly one place that writes it, and there is a way to rebuild it from the source of truth. A cached count updated in three different code paths will drift, and once it has drifted nobody can tell which number is real. Write it in one place, and keep a job that recomputes it and complains when it disagreed."}]},{"t":"p","runs":[{"text":"The rule is: "},{"text":"normalise the system of record, denormalise the copies you make from it.","b":true}]},{"t":"h2","id":"how-to-tell-if-a-table-is-wrong","runs":[{"text":"How to tell if a table is wrong"}]},{"t":"p","runs":[{"text":"Three questions, faster than any theory:"}]},{"t":"ol","start":1,"items":[[{"text":"Can I change one true fact about the world by updating one row?","b":true},{"text":" If a change of city needs an UPDATE with a WHERE clause that touches many rows, the city is in the wrong table."}],[{"text":"Can two rows disagree with each other about the same fact?","b":true},{"text":" If "},{"text":"orders","code":true},{"text":" has both "},{"text":"customer_city","code":true},{"text":" and a "},{"text":"customer_id","code":true},{"text":" pointing to a customer with a different city, your data already contains a contradiction and no query can tell you which side is right."}],[{"text":"Is there a column whose value is a list?","b":true},{"text":" Commas inside a cell mean a missing table."}]]},{"t":"h2","id":"the-honest-cost","runs":[{"text":"The honest cost"}]},{"t":"p","runs":[{"text":"Normalised data needs joins, and joins are the part of SQL people find hard, which is most of the next module. Very wide, very denormalised tables are easier to query and are exactly what analytical warehouses build on purpose."}]},{"t":"p","runs":[{"text":"The trade is: normalised data is harder to read and almost impossible to make inconsistent; denormalised data is easy to read and drifts out of agreement with itself unless something rebuilds it. Systems that take writes should be normalised. Tables built for reading can be as flat as you like, as long as everyone knows they are derived."}]}],"check":{"question":[{"text":"An "},{"text":"invoices","code":true},{"text":" table stores "},{"text":"tax_rate","code":true},{"text":" on every row, even though each customer’s region and each region’s current rate already live in "},{"text":"customers","code":true},{"text":" and "},{"text":"tax_rates","code":true},{"text":". A reviewer says the column duplicates a fact and breaks normalisation. Is the reviewer right?"}],"options":[{"text":[{"text":"Yes. The rate depends on the region, which depends on the customer, so it is a transitive dependency and belongs only in the tax table, reached by a join."}],"correct":false,"tempting":[{"text":"Applies the third-normal-form test to a column that records history rather than a current attribute, which is the standard way this rule gets misused."}]},{"text":[{"text":"Yes, but an acceptable one, since joining to the tax table on every invoice read would be too slow."}],"correct":false,"tempting":[{"text":"Justifies the column by performance when the real justification is correctness; that framing invites someone to remove it during a later cleanup."}]},{"text":[{"text":"No. The rate charged on that invoice was true at that moment and must stay fixed, so it is a fact about the invoice rather than about the region."}],"correct":true,"tempting":null},{"text":[{"text":"No, because tax rates are set by governments and are therefore reference data, which is always duplicated."}],"correct":false,"tempting":[{"text":"Reaches the right verdict by an invented rule; reference data is normally stored once, and this column is an exception for a different reason."}]}],"order":[1,0,2,3],"why":null},"prev":{"slug":"keys-and-identity","title":"Keys: how a database knows one thing from another","minutes":8},"next":{"slug":"select-and-where","title":"SELECT and WHERE: asking for rows","minutes":7}}}