Reading a database somebody else built

The situation

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.

Find out what exists, and how big it is

-- Postgres: tables with a live row estimate, largest first
SELECT relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 40;

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.

Portable equivalents: \dt and \d tablename in psql, .tables and .schema in SQLite, SHOW TABLES and DESCRIBE t in MySQL, and information_schema.columns everywhere.

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Let the foreign keys draw the map

If the designer declared foreign keys, the structure is already written down and you can read it:

SELECT
  tc.table_name      AS child,
  kcu.column_name    AS child_column,
  ccu.table_name     AS parent
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY child;

That single result is the entity relationship diagram, generated from the truth rather than from a document somebody stopped updating in 2021.

When 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:

SELECT count(*) FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;      -- how many orders point at a customer that does not exist

A non-zero answer tells you two things at once: the guess is probably right, and the data has orphans nobody is watching.

Establish the grain of each table you will use

For every table you plan to query, find the sentence “one row of this table is one ____”. Check it rather than believing the name:

SELECT count(*), count(DISTINCT order_id) FROM order_items;

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.

Find the columns that silently filter everything

Almost every mature database has at least one of these, and missing it is how you produce a confidently wrong number:

• 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.
• Test data: is_test, environment, an internal customer whose name is QA Test. Payment systems in particular are full of it.
• Status columns: an orders table where status runs cart, pending, paid, cancelled. Counting all rows counts abandoned baskets as orders.
• Multi-tenancy: tenant_id or org_id. Forgetting it sums every customer of the business together.

Find them by listing the distinct values of every low-cardinality column:

SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;

Do this for each suspicious column. It takes minutes and it is the single highest-yield habit in this lesson.

Read the application, if you can

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.

Ask one person one question

The 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.

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.