Relational Database Schema: Definition and Worked Example

A relational database schema is the logical blueprint that defines tables, columns, keys and constraints. The database is the engine that stores and runs it. The instance is the data held inside it at a given moment. This page designs one schema end to end, using StreamFlix, a movie streaming catalogue, from entities through normalization to deployable DDL.

This is the reference page for the model itself. For the working procedure step by step, and the pre-launch review checklist, follow the step-by-step procedure guide.

What a relational schema contains

A relational schema describes five things, and nothing else belongs in it.

• The tables in the database
• The columns inside each table, each with a data type
• The primary keys that uniquely identify rows
• The foreign keys that connect tables
• The constraints that protect integrity, such as NOT NULL, UNIQUE and CHECK

Common relational engines include MySQL, PostgreSQL, Oracle and SQL Server. The engine changes the syntax and the data types. It does not change the modelling questions: what data exists, how does it relate, and what rules should the database enforce.

Three words get used interchangeably and mean different things. Keeping them apart is the difference between a design conversation and a deployment conversation.

A schema can exist before any database does. That is the normal case in design-first work: the model is drawn, reviewed, and only then generated into an engine. For the diagram notation used throughout this page, see the guide to entity relationship diagrams and the deeper treatment in logical database design.

The StreamFlix example

StreamFlix is a movie streaming platform, and it plays the same role here that a sample schema does in a modelling tool: something small enough to hold in your head and real enough to break. It stores users and their profiles, movies and release details, reviews written by users, actors who appear in movies, and genres used to classify titles.

Those five concepts become seven tables. Two of them exist only to resolve many-to-many relationships.

Two rules govern that list. Each entity should represent one business concept. Each attribute should store one clear fact.

When you are unsure whether something belongs in the same table, ask whether it will always change together with the rest of the row. If it will not, it usually belongs somewhere else.

Normalization: 1NF to 3NF

Normalization removes duplication so that one fact is stored in one place. Each form fixes a specific failure, and each one is shown below as a before and after pair on the StreamFlix data.

First Normal Form (1NF): keep values atomic

Each column stores one value, not a comma-separated list.

Not in 1NF

This cannot be queried reliably. Finding every Sci-Fi film means matching inside a string, and adding one actor means rewriting the whole cell.

In 1NF

Movies

MovieGenres

MovieActors

Second Normal Form (2NF): remove partial dependencies

Where a table uses a composite primary key, every non-key column must depend on the whole key, not on part of it.

Not in 2NF

genre_name depends only on genre_id, not on the composite key. Rename a genre and every row that uses it has to change.

In 2NF

MovieGenres

Genres

Third Normal Form (3NF): remove transitive dependencies

Non-key columns must depend directly on the primary key, not on another non-key column.

Not in 3NF

user_name and phone_number describe the user, not the review. One user changing a phone number means updating every review row they ever wrote.

In 3NF

Reviews

Users

Normalization summary

3NF is the right default for a transactional schema. Stop there unless you have measured a reason to go further.

Denormalizing is a later decision made against real query plans, not a starting position. Reporting tables, cached aggregates and read replicas are all normal answers once you can show which query is slow and why. Design clean first, then optimize with evidence.

Keys and relationships

With the tables settled, connect them. Every relationship carries four decisions, and each one changes what the database will allow.

• Identifying or non-identifying: does the child row depend on the parent for its own identity
• Mandatory or optional: may the foreign key be NULL
• Cardinality: one-to-one, one-to-many, or many-to-many resolved through a junction table
• Delete and update actions: CASCADE, RESTRICT, SET NULL or NO ACTION

Relationship decisions for StreamFlix

Note the one deliberate difference. Genres uses RESTRICT where everything else uses CASCADE, because deleting a genre should fail loudly rather than silently strip classifications off films.

Why junction tables matter

MovieGenres and MovieActors exist because a relational schema cannot store a many-to-many relationship directly. It is resolved through a table holding the two keys, with those keys as its composite primary key.

The moment a junction table gains its own business columns, it stops being a pure link. MovieActors carries role_name and billing_order, so it is an entity in its own right: it records a casting, not just a connection. That is a modelling decision worth making on purpose rather than discovering later.

For the line notation and the referential action settings, see the foreign key documentation and the guide to ER diagrams.

The finished StreamFlix model

Seven tables, six foreign keys, two of the tables existing only to resolve many-to-many relationships. This is what the schema looks like once normalization and the key decisions are applied.

The StreamFlix model: Users, Movies, Genres, Actors, Reviews, and the MovieGenres and MovieActors junction tables.

Users, Movies, Genres and Actors point at nothing. They are the independent tables, and they are the ones to create first when the schema is deployed.

From logical model to physical DDL

Everything above is a logical design. It says how the data is organized and stays engine-neutral, and the logical design guide covers that stage on its own. The physical design binds it to one engine.

The choices made at this point are the ones that are expensive to reverse: integer widths, string lengths, whether a timestamp carries a time zone, and which columns get an index.

Here is the model above as MySQL DDL. Independent tables come first, then the tables that reference them, because a foreign key cannot point at a table that does not yet exist.

CREATE TABLE Users (
  user_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(50)  NOT NULL UNIQUE,
  email      VARCHAR(255) NOT NULL UNIQUE,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE Movies (
  movie_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title            VARCHAR(255)      NOT NULL,
  release_year     SMALLINT UNSIGNED NOT NULL,
  duration_minutes SMALLINT UNSIGNED NOT NULL
) ENGINE=InnoDB;

CREATE TABLE Genres (
  genre_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE Actors (
  actor_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  birth_year SMALLINT UNSIGNED,
  country    VARCHAR(60)
) ENGINE=InnoDB;

CREATE TABLE Reviews (
  review_id  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED     NOT NULL,
  movie_id   INT UNSIGNED     NOT NULL,
  rating     TINYINT UNSIGNED NOT NULL,
  comment    TEXT,
  created_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_reviews_user  FOREIGN KEY (user_id)  REFERENCES Users(user_id)   ON DELETE CASCADE,
  CONSTRAINT fk_reviews_movie FOREIGN KEY (movie_id) REFERENCES Movies(movie_id) ON DELETE CASCADE,
  CONSTRAINT chk_reviews_rating CHECK (rating BETWEEN 1 AND 5),
  CONSTRAINT uq_reviews_user_movie UNIQUE (user_id, movie_id)
) ENGINE=InnoDB;

CREATE TABLE MovieGenres (
  movie_id INT UNSIGNED NOT NULL,
  genre_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (movie_id, genre_id),
  CONSTRAINT fk_mg_movie FOREIGN KEY (movie_id) REFERENCES Movies(movie_id) ON DELETE CASCADE,
  CONSTRAINT fk_mg_genre FOREIGN KEY (genre_id) REFERENCES Genres(genre_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE MovieActors (
  movie_id      INT UNSIGNED NOT NULL,
  actor_id      INT UNSIGNED NOT NULL,
  role_name     VARCHAR(120),
  billing_order TINYINT UNSIGNED,
  PRIMARY KEY (movie_id, actor_id),
  CONSTRAINT fk_ma_movie FOREIGN KEY (movie_id) REFERENCES Movies(movie_id) ON DELETE CASCADE,
  CONSTRAINT fk_ma_actor FOREIGN KEY (actor_id) REFERENCES Actors(actor_id) ON DELETE CASCADE
) ENGINE=InnoDB;

Four decisions in that script are worth reading twice. The CHECK on rating enforces the 1 to 5 range in the database rather than only in the application. The UNIQUE on user_id and movie_id together says one review per user per movie. MovieGenres uses RESTRICT so a genre in use cannot be deleted. Every junction table takes its two foreign keys as its composite primary key, which makes a duplicate pairing impossible.

The same model deploys elsewhere with different types. PostgreSQL uses SERIAL or IDENTITY instead of AUTO_INCREMENT and has no UNSIGNED. SQL Server uses IDENTITY and NVARCHAR. Oracle uses NUMBER and VARCHAR2. The tables, keys and constraints do not change, which is the whole point of keeping the logical model separate.

To generate this script from a diagram instead of typing it, see the diagram documentation. To keep the diagram and the deployed schema in step afterwards, use schema synchronization, which compares the model against the live database and reports the differences per object. Running model validation before you deploy catches missing keys and broken references while they are still cheap to fix.

FAQ

What is the difference between a schema, a database and an instance?

The schema is the blueprint: tables, columns, keys and constraints. The database is the engine that runs it. The instance is the data inside it right now. The schema changes through migrations. The instance changes every second.

How do I know whether I need a separate table?

Create one when a value repeats across many rows, when the data changes independently of the row it sits in, or when a many-to-many relationship appears.

Should every schema be normalized to 3NF?

3NF is a strong default for transactional systems. Analytics and read-heavy workloads sometimes denormalize on purpose, but that is an optimization made against measured queries, not a starting point.

What is the difference between logical and physical database design?

Logical design defines entities, attributes and relationships without naming an engine. Physical design turns them into tables, typed columns, indexes and constraints for one specific database.

Can I design a database schema before the database exists?

Yes, and it is usually the better order. Build the model offline, starting from an empty model or one of the samples in the Welcome Pane, review it with the team, then generate the physical schema. Working offline against a saved model file, rather than against a live connection, is what makes that review possible.

What is the best tool for designing a relational database schema visually?

Any tool that lets you draw the model, generate the DDL and compare the model against a live database will do the job. DbSchema does those three in one workflow. Download it and open this model side by side with the diagram view to follow along.

Final thoughts

A relational schema is worth designing in stages: name the entities, normalize until each fact sits in one place, decide the keys and the referential actions, then bind it to an engine. The DDL at the end of that process is short, and it is correct because the thinking happened before it.

Once the schema is deployed, publishing it as schema documentation is what keeps the rest of the team reading the same model you designed.

Starting instead with CREATE TABLE statements and repairing the model later is the expensive order. Migrations against live data cost more than a diagram does.