Designing good tables

What is wrong with one big table?

Look at sheet in the Tables view. It is how many clubs start: a spreadsheet with one row per sighting, and every row carries the member's name, email and phone, plus the species' family. It is easy to type into. It goes wrong in three ways.

Updates miss copies. If Ruth changes her phone number, you must change it on every row she ever logged. Miss one and the club has two numbers for her, with no way to tell which is current. Spellings drift: row 7 says 'R. Adeyemi', so a GROUP BY on name now counts her as two people. And facts vanish by accident: delete Owen's only Barn Owl row and the club forgets that Barn Owls belong to the family Tytonidae.

All three come from the same cause. A fact about a member or a species is stored many times, once for every sighting.

SELECT member_email, COUNT(DISTINCT member_name) AS spellings, COUNT(*) AS rows
FROM sheet
GROUP BY member_email;

What does 'one fact in one place' mean in practice?

Each kind of thing gets its own table, and each fact about that thing is stored once, in that table. A member's email is a fact about the member, so it lives in members. A species' family is a fact about the species, so it lives in species. A sighting is an event that involves a member and a species, so sightings stores what is true of that event (the date, the place, how many birds) and points at the member and species by id.

A practical test: for each column, ask 'what is this a fact about?' If the answer is something other than the row's own subject, the column belongs in another table. member_phone on a sighting row fails the test, because a phone number is about a person, not a sighting.

Database people call this normalisation and give its stages numbered names. You don't need the names. The test above catches nearly every real problem.

How do I find the separate things hiding in a table?

Look for groups of columns that repeat together. Name, email and phone always appear as a set, and the same set comes back whenever the same person logs a bird. That set is one member. Species and family move together too. What's left (date, place, count) changes on every row, so it describes the sighting itself.

Also look for repeating groups across columns, such as bird1, bird2, bird3 on one row. They cap how many birds a sighting can have and make 'find every Kingfisher' a query over three columns. Turn them into separate rows in their own table.

Then choose what identifies each thing. Names are poor identifiers because they change and get misspelled. Email is better for members here, and every new table gets an INTEGER PRIMARY KEY id that other tables point at.

How do I move data from the old table into the new ones?

INSERT INTO … SELECT copies the result of a query straight into a table. Fill the parent tables first (members, species), then the child table that points at them, because foreign keys need the parents to exist.

For the parents, pick one row per thing. SELECT DISTINCT species, family works when the copies agree. When they don't, as with Ruth's two spellings, group by the identifier and choose one value per group with MAX or MIN. Then check the result by eye: you may want to fix the chosen spelling by hand.

For the child, join the old rows to the new parents on the identifier and select the new ids. The species table you start with was filled this way, and the query below rebuilds the same list so you can compare.

SELECT DISTINCT species, family
FROM sheet
ORDER BY species;

How can I check nothing was lost in the split?

Count first: the new child table should have as many rows as the old table. If it has more, a join matched twice (two members with the same email, say). If it has fewer, some rows found no partner, often because of a spelling difference or a stray space.

Then join the new tables back together and compare with the original. If every fact comes back, the split is lossless. The Changes view helps too: after the task, members and sightings appear as new tables full of green rows, and sheet is untouched, so you can compare side by side before you drop it.

SELECT COUNT(*) AS sheet_rows
FROM sheet AS s
JOIN species AS sp ON sp.common_name = s.species;

How should I name tables and columns?

Consistent names matter more than any particular style. Pick one set of habits and keep it across every table.

• Tables: plural nouns in lowercase snake_case: members, sightings, species.
• Primary keys: id. Foreign keys: the singular table name plus _id: member_id, species_id. Anyone reading a join can then see what links to what.
• Dates: end in _on for a day (seen_on) or _at for a moment (created_at).
• Units in the name when they aren't obvious: price_pence, length_cm.
• Booleans as yes/no questions: is_active, has_paid.
• No spaces, no capitals, no SQL keywords such as order or group, so nothing ever needs quoting.

When is it fine to store something twice?

Copying data on purpose is called denormalising. Do it when you have a reason, and write the reason down. Three common ones:

• History: an order line should keep the price paid that day, even after the product's price changes. That is a different fact (price at the time of sale), not a copy.
• Speed: a sightings_count stored on members saves counting on every page view. You then have to keep it in step, usually in the same transaction as each insert.
• Reports and exports: a flat table rebuilt each night for a spreadsheet or dashboard is fine, as long as nobody edits it and treats it as the source of truth.

What do AI-generated schemas get wrong?

Asked to 'make a database for a bird club', AI tools often produce one wide table that mirrors the spreadsheet, or the opposite: a dozen tiny tables for things that never change, like a colours table with three rows. Check each table against the fact test, and ask whether each extra table holds something you will actually add, edit or link to.

Also check the links. Generated code sometimes stores the member's name in sightings instead of member_id, which brings back the spelling problem. Or it adds member_id with no REFERENCES, so nothing stops a broken link. A good schema has a foreign key wherever one table points at another.

Field notes

• Repeating the same name and email on every row invites copies that disagree.
• Give each kind of thing its own table, and store each fact about it once.
• Fill the parent tables first, then the child table that points at them with ids.
• Count rows after the split to check nothing was lost or doubled.

Easy to get wrong

DISTINCT treats 'Ruth Adeyemi' and 'R. Adeyemi' as different rows, so one person becomes two members. Group by the identifier instead.

Storing names copies the member and species facts into every sighting again. Store the id and join when you need the name.

Numbered columns cap the list and make searching awkward. One row per bird has no limit and one column to search.

Questions people ask

Should I always split tables this far?

Split when a fact repeats and can change. A small personal table that only you edit, and only for a month, can stay flat. Shared data that lives for years should be split.

Is email a good primary key?

Use it to find duplicates while moving data, then give each member an INTEGER id and point other tables at that. People change their email address. An id never needs to change.

What about lookup tables with a fixed list, like statuses?

A short CHECK (status IN ('open', 'closed')) is often enough. Use a table when the list changes, needs extra columns such as a description, or is shared by several tables.

Do I delete the old table after splitting?

Only after you have checked the counts and joined the new tables back to compare. Keep a backup or rename it to sheet_old for a while. Dropping it is the one step you can't undo.