Relational Database Schema Design Procedure | DbSchema

Designing a relational schema is a procedure, and each step ends in a decision you can write down. Gather the questions, name the entities, wire the keys, normalize, fix types and constraints per engine, decide the indexes, then prove the result with real queries. This page is the procedure.

It uses one running example, a streaming service called StreamFlix, with users, movies, genres, actors, watchlists and reviews. For the concepts behind each step and a full worked walkthrough, read How to Design a Relational Database Schema.

What a relational schema is

A relational schema is the structural definition of a database: its tables, columns, constraints and the relationships between them. For the full definition and a complete theoretical walkthrough, see our guide on 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. A schema is correct only relative to what the application has to ask it.

• What does the application store, and who writes it?
• Which business events have to be recorded, and in what order?
• Which questions will be asked often enough to shape the structure?
• Which records must be unique, and by what rule?
• Which relationships are mandatory, and which are optional?

For StreamFlix, the first pass is short: users create accounts, movies belong to one or more genres, movies have many actors, users write reviews, users save movies 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

Convert each business concept into a table and each fact about it into a column. Junction tables appear here, as soon as a relationship goes both ways.

Do not aim for completeness on the first pass. Aim for the entities without which the release cannot work.

Decision: fix the entity list for release one and move edge cases to a parking list. A table added later is cheap; 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 real dependency between tables needs a foreign key behind it.

1. Give each table a stable primary key, and prefer a surrogate key over anything a user can edit.
2. Create a foreign key for every dependency you actually rely on. If it is not enforced, it will drift.
3. Use a junction table for every many-to-many relationship, and give it its own attributes when the relationship carries data, as Watchlist does with added_at.
4. Decide per relationship whether the child may exist without the parent, because that becomes a NULL rule.
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 implementation.

Step 4: Normalize until updates stop repeating themselves

Normalization is not an academic exercise. It is the test of whether one business change means one row change.

That table is the summary. For the full walkthrough, with the before and after tables for each form, use 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

This is where a portable design meets one specific engine. The same logical column becomes a different type on MySQL, PostgreSQL, SQL Server, Oracle and SQLite, and the differences change behaviour, not just storage.

The traps worth knowing before you commit:

• MySQL has no native boolean. TINYINT(1) is a convention, not a constraint, so add a CHECK if the value must stay 0 or 1.
• PostgreSQL TIMESTAMP without a time zone silently drops offsets. Choose TIMESTAMPTZ unless you have a reason not to.
• SQLite applies type affinity rather than strict typing, so a constraint is the only thing keeping a column honest.
• Oracle historically treats an empty string as NULL, which breaks NOT NULL assumptions carried over from other engines.
• Never store money in a floating-point type on any engine.

Then set the constraints in the same pass. NOT NULL where a value is mandatory, UNIQUE on natural identifiers such as email, 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 actually deploying to, and choose the smallest type that holds the real range. A type list that says "string" is not finished work.

Step 6: Decide which indexes earn their cost

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

Add an index when:

• The column is a foreign key. Most engines do not index them automatically, and joins and cascading deletes both pay for it.
• The column appears in a WHERE clause that runs often, on a table large enough for a scan to hurt.
• The column drives an ORDER BY or GROUP BY that the query plan is currently sorting by hand.
• A UNIQUE rule has to be enforced, in which case the index is the enforcement.
• A composite index matches a real query's leading columns, 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.
• The table is written far more often than it is read.
• You are guessing. An index added before a measured query is a cost with no matching benefit.
• An existing composite index already covers the column as its leading key.

Indexes and foreign keys interact more than people expect, and the detail is in Indexes and Foreign Keys.

Decision: index every foreign key and every primary key, then add nothing else until a real 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 checks that catch structural mistakes rather than typos:

• Insert a review for a movie that does not exist. The foreign key must reject it.
• Insert the same email twice. The UNIQUE constraint must reject it.
• Delete a movie that has reviews and a watchlist entry. Confirm the referential action is the one you chose in step 3, not the engine's default.
• Run the top three read queries from step 1 and read the plan, not just the result.
• 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 observed rather than assumed.

The pre-launch review checklist

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

Most schemas fail this list on referential actions and on documentation. Both are cheap now and expensive later. The common failure patterns are collected in Database Design Mistakes.

Where a design tool fits

The procedure works on paper. A design tool shortens steps 2 to 5, because the diagram, the constraints and the generated DDL stay in one model instead of three documents.

1. Sketch the entities and relationships visually, before the database exists.
2. Set keys, junction tables and referential actions in the diagram rather than in DDL.
3. Generate the SQL for the target engine, or reverse-engineer what is already deployed.
4. Publish the diagram so step 7's documentation check passes.

DbSchema does this as a database design tool, and so do several alternatives. The value is not the drawing; it is that the decisions from each step end up recorded in one place.

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 diagram is reviewable. Start with SQL when the schema is small and you are the only reader.

When should I add indexes?

Index primary and foreign keys from the start. Add anything else only after a real query plan shows the scan, because every index slows writes.

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

Use a surrogate key when 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.

Can I design a relational schema without a live database?

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

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. Not when the diagram looks tidy.

Conclusion

The procedure is what makes a schema reviewable: questions, entities, keys, normalization, types, indexes, proof. Each step ends in a decision, and a decision someone wrote down is one nobody has to reconstruct six months later.

For the concepts under the procedure, and the full normalization walkthrough, read How to Design a Relational Database Schema.

Ready to run the procedure on a real schema? Download DbSchema and start with the free Community Edition, or compare the editions if you need offline models and schema sync.