How to design a database schema
A good schema is mostly a handful of decisions made in the right order. This is the order that works for most applications, with the mistakes that cost the most to fix later.
1. List the things your application remembers
Write down the nouns: customers, orders, products, invoices. Each one that has its own identity and lifetime becomes a table. If two nouns always change together, they may be one table; if one noun has parts that change separately, those parts may be their own.
2. Give every table a primary key
Use a surrogate key (a generated id or UUID) unless a natural key is genuinely stable. Emails, usernames and product codes change more often than people expect; a foreign key pointing at one of them makes every change a migration.
3. Model relationships with foreign keys
- One to many: put the foreign key on the many side (orders.customer_id).
- Many to many: add a join table with two foreign keys, and put anything about the link (role, joined_at) on it.
- One to one: a foreign key that is also unique, usually to split rarely used columns away.
4. Let the database enforce the rules
NOT NULL, UNIQUE, CHECK and foreign keys catch bad data for every program that touches the database, not only yours. A rule kept only in application code is a rule that one script or one bug will eventually bypass.
5. Index for the questions you ask
Index foreign keys and the columns you filter and sort by, and no more: every index slows writes. Check the real queries before adding one.
6. Plan for change
Schemas change. Keep each change as a small migration, test it against a copy of real data, and prefer additive changes (new column, new table) over destructive ones. Seeing the diagram next to the migration makes both easier to review.
Schemas to start from
Questions
Should I use UUIDs or integers for ids?
Integers are smaller and faster to index; UUIDs can be created anywhere without coordination and do not reveal how many rows you have. Either is fine; mixing them without a reason is not.
How many tables is too many?
There is no number. Split a table when a group of columns has its own lifetime or is repeated, and stop when splitting only adds joins.