{"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":"reading-an-unfamiliar-schema","title":"Reading a database somebody else built","url":"https://addaly.com/learn/data-and-sql/reading-an-unfamiliar-schema","position":9,"of":66,"minutes":9,"answer":"In a schema you did not build, let the foreign keys draw the map, state each table's grain, and hunt for the status and soft-delete columns that silently filter everything.","keyIdea":"In a schema you did not build, let the foreign keys draw the map, state each table's grain, and hunt for the status and soft-delete columns that silently filter everything."},"body":{"format":"markdown","text":"## The situation\n\nYou are given access to a database with 180 tables, no documentation, and a question to answer by Thursday. The person who designed it left. This is the normal case, not the unlucky one, and there is a routine for it that takes about forty minutes.\n\n## Find out what exists, and how big it is\n\n```sql\n-- Postgres: tables with a live row estimate, largest first\nSELECT relname, n_live_tup\nFROM pg_stat_user_tables\nORDER BY n_live_tup DESC\nLIMIT 40;\n```\n\nSize is the fastest guide to importance. The three biggest tables are almost always the event tables (the things that happen) and the small ones are the entities and the lookup lists. A table with 12 rows is a controlled vocabulary; a table with 400 million is a log.\n\nPortable equivalents: `\\dt` and `\\d tablename` in psql, `.tables` and `.schema` in SQLite, `SHOW TABLES` and `DESCRIBE t` in MySQL, and `information_schema.columns` everywhere.\n\n```sql\nSELECT column_name, data_type, is_nullable, column_default\nFROM information_schema.columns\nWHERE table_name = 'orders'\nORDER BY ordinal_position;\n```\n\n## Let the foreign keys draw the map\n\nIf the designer declared foreign keys, the structure is already written down and you can read it:\n\n```sql\nSELECT\n  tc.table_name      AS child,\n  kcu.column_name    AS child_column,\n  ccu.table_name     AS parent\nFROM information_schema.table_constraints tc\nJOIN information_schema.key_column_usage kcu\n  ON kcu.constraint_name = tc.constraint_name\nJOIN information_schema.constraint_column_usage ccu\n  ON ccu.constraint_name = tc.constraint_name\nWHERE tc.constraint_type = 'FOREIGN KEY'\nORDER BY child;\n```\n\nThat single result is the entity relationship diagram, generated from the truth rather than from a document somebody stopped updating in 2021.\n\nWhen it returns nothing (common in databases built by application frameworks that manage relationships in code) fall back to naming conventions. A column called `customer_id` in `orders` almost certainly points at `customers.id`. Verify rather than assume:\n\n```sql\nSELECT count(*) FROM orders o\nLEFT JOIN customers c ON c.id = o.customer_id\nWHERE c.id IS NULL;      -- how many orders point at a customer that does not exist\n```\n\nA non-zero answer tells you two things at once: the guess is probably right, and the data has orphans nobody is watching.\n\n## Establish the grain of each table you will use\n\nFor every table you plan to query, find the sentence \"one row of this table is one ____\". Check it rather than believing the name:\n\n```sql\nSELECT count(*), count(DISTINCT order_id) FROM order_items;\n```\n\nEqual numbers mean one row per order, and the table is misnamed. Different numbers mean one row per line item, as expected. Two minutes here prevents the row-multiplication bugs that the joins module is entirely about.\n\n## Find the columns that silently filter everything\n\nAlmost every mature database has at least one of these, and missing it is how you produce a confidently wrong number:\n\n- **Soft deletes**: `deleted_at`, `is_deleted`, `archived`. Rows are still there. Every honest query needs `WHERE deleted_at IS NULL`, and nobody will tell you.\n- **Test data**: `is_test`, `environment`, an internal customer whose name is `QA Test`. Payment systems in particular are full of it.\n- **Status columns**: an `orders` table where `status` runs `cart`, `pending`, `paid`, `cancelled`. Counting all rows counts abandoned baskets as orders.\n- **Multi-tenancy**: `tenant_id` or `org_id`. Forgetting it sums every customer of the business together.\n\nFind them by listing the distinct values of every low-cardinality column:\n\n```sql\nSELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;\n```\n\nDo this for each suspicious column. It takes minutes and it is the single highest-yield habit in this lesson.\n\n## Read the application, if you can\n\nColumn meanings often live in code, not in the database. A grep of the application repository for the table name will usually find the model definition, and the comment above it, and the migration that added the mystery column with a message explaining why. When there is a repository you can read, read it: it is documentation that had to be true.\n\n## Ask one person one question\n\nThe last step is social. Find whoever queries this database most and ask a single, specific question: *\"If I want revenue for last month, which table and which filters do you use?\"* Not \"can you explain the schema\", which invites an hour of vagueness. A concrete question gets you their working query, and their working query contains all the filters nobody wrote down.\n\nThen write what you learned into a file in your own repository. Three paragraphs: the grain of each table you touched, the filters that are always required, and the one thing that surprised you. You are going to be the person somebody asks next."},"check":{"question":"You are asked for last quarter's revenue from a database you have never seen. You have found `orders` with `amount` and `placed_at`, and the sum looks plausible. Which single check most changes whether the figure is right?","options":[{"text":"Confirming that `amount` is stored as `numeric` rather than a floating-point type, since binary rounding across a quarter of orders could compound into a visibly wrong total.","correct":false,"tempting":"Worth doing, but it changes the result at the level of cents rather than materially altering the figure."},{"text":"Confirming there is an index on `placed_at` so the query runs in reasonable time.","correct":false,"tempting":"Concerns how long the query takes, not whether the number it produces is right."},{"text":"Checking the collation used to sort the customer names in the output.","correct":false,"tempting":"Affects presentation order only; revenue is unchanged by how the rows are sorted."},{"text":"Listing the distinct values of `status` and any soft-delete or test flags, to see how many rows are abandoned baskets, cancellations or internal tests.","correct":true}]},"prev":{"slug":"case-expressions","title":"CASE, COALESCE and computing a column that does not exist","url":"https://addaly.com/learn/data-and-sql/case-expressions"},"next":{"slug":"the-grain-of-a-result","title":"Grain: the sentence to say before you write the query","url":"https://addaly.com/learn/data-and-sql/the-grain-of-a-result"},"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) 'Reading a database somebody else built', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/reading-an-unfamiliar-schema (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":"Reading a database somebody else built","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":"In a schema you did not build, let the foreign keys draw the map, state each table’s grain, and hunt for the status and soft-delete columns that silently filter everything.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-situation","runs":[{"text":"The situation"}]},{"t":"p","runs":[{"text":"You are given access to a database with 180 tables, no documentation, and a question to answer by Thursday. The person who designed it left. This is the normal case, not the unlucky one, and there is a routine for it that takes about forty minutes."}]},{"t":"h2","id":"find-out-what-exists-and-how-big-it-is","runs":[{"text":"Find out what exists, and how big it is"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- Postgres: tables with a live row estimate, largest first\nSELECT relname, n_live_tup\nFROM pg_stat_user_tables\nORDER BY n_live_tup DESC\nLIMIT 40;"},{"t":"p","runs":[{"text":"Size is the fastest guide to importance. The three biggest tables are almost always the event tables (the things that happen) and the small ones are the entities and the lookup lists. A table with 12 rows is a controlled vocabulary; a table with 400 million is a log."}]},{"t":"p","runs":[{"text":"Portable equivalents: "},{"text":"\\dt","code":true},{"text":" and "},{"text":"\\d tablename","code":true},{"text":" in psql, "},{"text":".tables","code":true},{"text":" and "},{"text":".schema","code":true},{"text":" in SQLite, "},{"text":"SHOW TABLES","code":true},{"text":" and "},{"text":"DESCRIBE t","code":true},{"text":" in MySQL, and "},{"text":"information_schema.columns","code":true},{"text":" everywhere."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT column_name, data_type, is_nullable, column_default\nFROM information_schema.columns\nWHERE table_name = 'orders'\nORDER BY ordinal_position;"},{"t":"h2","id":"let-the-foreign-keys-draw-the-map","runs":[{"text":"Let the foreign keys draw the map"}]},{"t":"p","runs":[{"text":"If the designer declared foreign keys, the structure is already written down and you can read it:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT\n  tc.table_name      AS child,\n  kcu.column_name    AS child_column,\n  ccu.table_name     AS parent\nFROM information_schema.table_constraints tc\nJOIN information_schema.key_column_usage kcu\n  ON kcu.constraint_name = tc.constraint_name\nJOIN information_schema.constraint_column_usage ccu\n  ON ccu.constraint_name = tc.constraint_name\nWHERE tc.constraint_type = 'FOREIGN KEY'\nORDER BY child;"},{"t":"p","runs":[{"text":"That single result is the entity relationship diagram, generated from the truth rather than from a document somebody stopped updating in 2021."}]},{"t":"p","runs":[{"text":"When it returns nothing (common in databases built by application frameworks that manage relationships in code) fall back to naming conventions. A column called "},{"text":"customer_id","code":true},{"text":" in "},{"text":"orders","code":true},{"text":" almost certainly points at "},{"text":"customers.id","code":true},{"text":". Verify rather than assume:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT count(*) FROM orders o\nLEFT JOIN customers c ON c.id = o.customer_id\nWHERE c.id IS NULL;      -- how many orders point at a customer that does not exist"},{"t":"p","runs":[{"text":"A non-zero answer tells you two things at once: the guess is probably right, and the data has orphans nobody is watching."}]},{"t":"h2","id":"establish-the-grain-of-each-table-you-will-use","runs":[{"text":"Establish the grain of each table you will use"}]},{"t":"p","runs":[{"text":"For every table you plan to query, find the sentence “one row of this table is one ____”. Check it rather than believing the name:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT count(*), count(DISTINCT order_id) FROM order_items;"},{"t":"p","runs":[{"text":"Equal numbers mean one row per order, and the table is misnamed. Different numbers mean one row per line item, as expected. Two minutes here prevents the row-multiplication bugs that the joins module is entirely about."}]},{"t":"h2","id":"find-the-columns-that-silently-filter-everything","runs":[{"text":"Find the columns that silently filter everything"}]},{"t":"p","runs":[{"text":"Almost every mature database has at least one of these, and missing it is how you produce a confidently wrong number:"}]},{"t":"ul","items":[[{"text":"Soft deletes","b":true},{"text":": "},{"text":"deleted_at","code":true},{"text":", "},{"text":"is_deleted","code":true},{"text":", "},{"text":"archived","code":true},{"text":". Rows are still there. Every honest query needs "},{"text":"WHERE deleted_at IS NULL","code":true},{"text":", and nobody will tell you."}],[{"text":"Test data","b":true},{"text":": "},{"text":"is_test","code":true},{"text":", "},{"text":"environment","code":true},{"text":", an internal customer whose name is "},{"text":"QA Test","code":true},{"text":". Payment systems in particular are full of it."}],[{"text":"Status columns","b":true},{"text":": an "},{"text":"orders","code":true},{"text":" table where "},{"text":"status","code":true},{"text":" runs "},{"text":"cart","code":true},{"text":", "},{"text":"pending","code":true},{"text":", "},{"text":"paid","code":true},{"text":", "},{"text":"cancelled","code":true},{"text":". Counting all rows counts abandoned baskets as orders."}],[{"text":"Multi-tenancy","b":true},{"text":": "},{"text":"tenant_id","code":true},{"text":" or "},{"text":"org_id","code":true},{"text":". Forgetting it sums every customer of the business together."}]]},{"t":"p","runs":[{"text":"Find them by listing the distinct values of every low-cardinality column:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;"},{"t":"p","runs":[{"text":"Do this for each suspicious column. It takes minutes and it is the single highest-yield habit in this lesson."}]},{"t":"h2","id":"read-the-application-if-you-can","runs":[{"text":"Read the application, if you can"}]},{"t":"p","runs":[{"text":"Column meanings often live in code, not in the database. A grep of the application repository for the table name will usually find the model definition, and the comment above it, and the migration that added the mystery column with a message explaining why. When there is a repository you can read, read it: it is documentation that had to be true."}]},{"t":"h2","id":"ask-one-person-one-question","runs":[{"text":"Ask one person one question"}]},{"t":"p","runs":[{"text":"The last step is social. Find whoever queries this database most and ask a single, specific question: "},{"text":"”If I want revenue for last month, which table and which filters do you use?”","i":true},{"text":" Not “can you explain the schema”, which invites an hour of vagueness. A concrete question gets you their working query, and their working query contains all the filters nobody wrote down."}]},{"t":"p","runs":[{"text":"Then write what you learned into a file in your own repository. Three paragraphs: the grain of each table you touched, the filters that are always required, and the one thing that surprised you. You are going to be the person somebody asks next."}]}],"check":{"question":[{"text":"You are asked for last quarter’s revenue from a database you have never seen. You have found "},{"text":"orders","code":true},{"text":" with "},{"text":"amount","code":true},{"text":" and "},{"text":"placed_at","code":true},{"text":", and the sum looks plausible. Which single check most changes whether the figure is right?"}],"options":[{"text":[{"text":"Confirming that "},{"text":"amount","code":true},{"text":" is stored as "},{"text":"numeric","code":true},{"text":" rather than a floating-point type, since binary rounding across a quarter of orders could compound into a visibly wrong total."}],"correct":false,"tempting":[{"text":"Worth doing, but it changes the result at the level of cents rather than materially altering the figure."}]},{"text":[{"text":"Confirming there is an index on "},{"text":"placed_at","code":true},{"text":" so the query runs in reasonable time."}],"correct":false,"tempting":[{"text":"Concerns how long the query takes, not whether the number it produces is right."}]},{"text":[{"text":"Checking the collation used to sort the customer names in the output."}],"correct":false,"tempting":[{"text":"Affects presentation order only; revenue is unchanged by how the rows are sorted."}]},{"text":[{"text":"Listing the distinct values of "},{"text":"status","code":true},{"text":" and any soft-delete or test flags, to see how many rows are abandoned baskets, cancellations or internal tests."}],"correct":true,"tempting":null}],"order":[1,2,3,0],"why":null},"prev":{"slug":"case-expressions","title":"CASE, COALESCE and computing a column that does not exist","minutes":7},"next":{"slug":"the-grain-of-a-result","title":"Grain: the sentence to say before you write the query","minutes":8}}}