Relational Database Schema Design Procedure

Six months after the release, nobody can say why that column is nullable. The diagram survived and the reasoning did not, which is the usual way a schema becomes something people work around instead of with. Designing one is a procedure of seven steps, and each step ends in a decision short enough to write down: the scope, the entities, the keys, how far to normalize, the types, the indexes, and whether the design may ship.

A relational schema is the structural definition of a database: its tables, columns, constraints and the relationships between them. One example runs through all seven steps here, a streaming service whose catalogue, accounts and reviews are the kind of small domain that still manages to raise every question. For the concepts under the procedure, read How to Design a Relational Database Schema.

The seven steps at a glance

Step 1: Write down the questions the database must answer

Start with questions, not tables, because a schema is correct only in relation to what the application has to ask it. Five of them settle the scope: what the application stores and who writes it, which business events have to be recorded and in what order, which questions will be asked frequently enough to shape the structure, which records must be unique and by what rule, and which relationships are mandatory rather than optional.

For the streaming service the first pass is short. Users create accounts, films belong to one or more genres, films have a cast, users write reviews, and users save films to a watchlist.

Decision: lock a written list of user actions and the data each one needs, and mark explicitly what release one will not support. Everything after this step is measured against that list.

Step 2: Turn the nouns into tables and columns

Each business concept becomes a table and each fact about it becomes a column. Junction tables appear here too, the moment a relationship runs both ways.

Do not aim for completeness on the first pass. Aim for the entities without which release one cannot work, and notice that Watchlist already earns a column nobody asked for: the date something was added is the first thing anyone will want to sort by.

Decision: fix the entity list for release one and move the edge cases to a parking list. A table added later is cheap, and a table removed after data lands in it is not.

Step 3: Pick the keys and wire the relationships

Every table needs an identifier that never changes, and every dependency you rely on needs a foreign key behind it.

1. Give each table a stable primary key, and prefer a surrogate key to anything a user can edit.
2. Create a foreign key for every dependency the application assumes. An assumption the database does not enforce will drift.
3. Use a junction table for every many-to-many relationship, and give it its own columns where the relationship carries data, as Watchlist does with added_at.
4. Decide per relationship whether the child may exist without the parent, because that answer becomes the NULL rule on the column.
5. Decide what happens on delete and on update before the first row is inserted.

If foreign keys are new territory, read What Is a Foreign Key? alongside this step.

Decision: for each relationship, write down the cardinality, whether it is optional, and the referential action. Three answers per relationship, recorded before anyone implements it.

Step 4: Normalize until updates stop repeating themselves

Normalization is the test of whether one business change means one row change, which is a question about updates rather than about theory.

That table is the summary. The before and after rows for each form are worked through in the normalization section of How to Design a Relational Database Schema.

Decision: normalize to 3NF by default. Denormalize only against a measured query, never against a hunch, and write down which query paid for it.

Step 5: Choose data types and constraints per engine

A portable design meets one specific engine here. The same logical column becomes a different type on each of them, and the differences change behaviour rather than only storage.

Four of those cells hide behaviour worth knowing before you commit to them. MySQL 8.4 has no separate boolean type, since BOOL and BOOLEAN are synonyms for TINYINT(1), so a CHECK is what keeps the column to 0 and 1. PostgreSQL 18 ignores any time zone indication in a value going into a timestamp without time zone, so a value that arrived with an offset comes back without one; choose TIMESTAMPTZ unless you have a reason not to. SQLite treats a declared type as a recommendation rather than a requirement, and since 3.37.0 a STRICT table is how you get the enforcement other engines give by default. Oracle AI Database 26ai treats a character value of length zero as null, which breaks a NOT NULL assumption carried over from another engine, and the documentation advises against relying on it either way. On every engine, money goes in a fixed-point type and never in a floating-point one.

Set the constraints in the same pass: NOT NULL where a value is mandatory, UNIQUE on natural identifiers such as an email address, CHECK for ranges such as a rating between 1 and 5, and a default only where every row genuinely has one. For the syntax on MySQL, see CREATE TABLE.

Decision: name the exact type for every column on the engine you are deploying to, and choose the smallest type that holds the real range. A type list that still says "string" is not finished work.

Step 6: Decide which indexes earn their cost

An index is a trade: reads get faster and every write gets slower, so each one has to be justified by a query you can name.

Foreign keys are the first candidates, and what the engine does for you differs. PostgreSQL 18 does not create an index on the referencing columns of a foreign key, and its documentation notes that a delete or an update on the parent then scans the child table for matching rows, which is why indexing them is worth it. MySQL 8.4 creates that index automatically if no suitable one exists. After the foreign keys come the columns in a WHERE clause that runs frequently on a table large enough for a scan to hurt, the column behind an ORDER BY or GROUP BY the plan is currently sorting by hand, and any UNIQUE rule, where the index is the enforcement rather than an optimization. A composite index earns its place when it matches the leading columns of a real query, in that query's order.

Leave the index out when the column has few distinct values, such as a status flag on a small table, or when the table is written far more often than it is read, or when an existing composite index already covers the column as its leading key. Leave it out above all when you are guessing: an index added before a measured query is a write cost with no matching benefit. The interaction between the two is worked through in Indexes and Foreign Keys.

Decision: index every primary key and every foreign key, then add nothing else until a query plan asks for it. Record which query each secondary index exists for, so the next person can delete it safely.

Step 7: Prove the design with sample data and real queries

A diagram that looks right is not evidence. Load rows and run the questions from step 1 against them:

INSERT INTO Movies (movie_id, title, release_year, duration_minutes)
VALUES (1, 'Inception', 2010, 148);

INSERT INTO Genres (genre_id, name) VALUES (1, 'Sci-Fi');
INSERT INTO MovieGenres (movie_id, genre_id) VALUES (1, 1);

Then run the five checks that catch a structural mistake rather than a typo.

1. Insert a review for a film that does not exist. The foreign key must reject it.
2. Insert the same email address twice. The UNIQUE constraint must reject it.
3. Delete a film that has reviews and a watchlist entry. Confirm the referential action is the one you chose in step 3, not the engine's default.
4. Run the three most frequent read queries from step 1, and read the plan rather than only the result.
5. Insert a row with every optional column empty. If that row is meaningless, a NOT NULL is missing.

Decision: do not deploy until the critical read and write queries have run against sample data and the delete behaviour has been watched rather than assumed.

The pre-launch review checklist

Run this before the schema reaches an environment that holds real data.

The two rows that fail most often are the referential actions and the documentation, and both are cheap now and expensive later. The failure patterns behind the rest are collected in Database Design Mistakes.

Where DbSchema fits in the procedure

The procedure works on paper, and steps 2 to 5 are the ones that get shorter when the diagram, the constraints and the generated DDL come out of one model instead of three documents. In DbSchema as a database design tool, that model is a .dbs file, and the sequence is this.

1. Sketch the entities and relationships on the diagram before the database exists, dragging from a column to the column it references to create each foreign key.
2. Set the keys, the junction tables and the referential actions in the Foreign Key Editor rather than in DDL, by double-clicking the relationship line.
3. Generate the SQL for the target engine, or connect and let DbSchema reverse-engineer what is already deployed.
4. Export the diagram as HTML5, PDF or Markdown documentation, which is what makes the last row of the checklist pass.

Steps 1 and 2 change the model file only. Connected, DbSchema applies each schema change to the database as you make it; disconnected, the edits stay in the .dbs file until you reconnect, click Refresh Model from Database, and choose per object in the synchronization dialog whether to write it to the database, pull it into the model, or put it in a migration script. Drawing the diagram, connecting and reverse-engineering are in the free Community edition, and saving the model to a file, the documentation export and schema synchronization are in Pro.

Seven steps, seven decisions, and a written answer for each one: that is the difference between a schema somebody can review and a diagram somebody has to reconstruct. Download DbSchema at https://dbschema.com/download.html and start at step 2, drawing the entity list on a diagram with no database attached; the Community edition covers the diagram and the SQL editor, and the Pro edition covers the model file, the documentation export and the synchronization that steps 3 to 7 lean on.

FAQ

Should I start with an ER diagram or with SQL?

Start with the diagram when more than one person has to agree on the structure, because a reviewer can read a diagram and correct it before any DDL exists. Start with SQL when the schema is small and you are its only reader.

How do I choose between a natural key and a surrogate key?

Use a surrogate key whenever the natural candidate can change, and an email address or a phone number always can. Keep the natural key as a UNIQUE constraint, so both properties hold at once: a stable identifier for the foreign keys, and a rule against duplicates for the business.

Can I design a relational schema without a live database?

Steps 1 to 4 need no database at all. Step 5 needs you to have chosen an engine, and step 7 needs somewhere to run the queries, which can be a local instance created for the purpose. In DbSchema the offline model is a .dbs file you can edit with nothing connected, which is in the Pro edition.

How do I know the design is finished?

When every row of the pre-launch checklist passes and each of the seven decisions is written down somewhere the next person will find it. A tidy diagram is not the test.