What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
You can build a small movie-catalog database with PostgreSQL by creating tables for movies, people, and credits, then linking those tables with foreign keys. The SQL below is a starter schema for a local PostgreSQL database; the “20 minutes” is a pacing goal, not a guaranteed setup time. An online, managed database is optional.
How should you store movies and actors in a database?
Store each movie and person once, then represent their connection in a separate credits table. A movie can have many credited people, and a person can work on many movies, so the relationship is many-to-many. Credit-specific details—such as whether someone acted or directed, and an actor’s character name—belong on the credit row.
This is the pattern used in the University of Cambridge’s teaching schema based on IMDb data, which separates movies, people, and credits. It is a useful starting point, not a universal schema standard: keys, constraints, and deletion behavior should reflect your application.
What do you need before creating the database?
- A PostgreSQL server and a way to run SQL, such as
psqlor a database administration interface. - Permission to create a database, or an existing empty database where you can create tables.
- A choice between numeric identity IDs and UUIDs. Numeric identity columns are straightforward for a small local tutorial; PostgreSQL services such as Supabase document both identity columns and UUIDs as common options.
The commands below create a local database using psql. If you already have a database, connect to it and start at the table-creation step. PostgreSQL’s documentation explains the key and constraint behavior used here in its PostgreSQL 18 constraints guide.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →#1 Best Overall
Create and connect to a local database
-
From a shell where PostgreSQL is installed, create the database:
createdb movie_catalog -
Connect to it:
psql movie_catalog -
Run the remaining SQL statements at the
psqlprompt. If you use a graphical SQL editor, select the new database before running them.
Create the movies, people, and credits tables
Run these statements in order. Primary keys identify rows; foreign keys ensure that each credit refers to an existing movie and person. The ON DELETE CASCADE rules mean a credit is removed automatically when its movie or person is deleted.
CREATE TABLE movies (
movie_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
release_year integer,
description text
);
CREATE TABLE people (
person_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE credits (
credit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
movie_id bigint NOT NULL REFERENCES movies(movie_id) ON DELETE CASCADE,
person_id bigint NOT NULL REFERENCES people(person_id) ON DELETE CASCADE,
credit_type text NOT NULL,
character_name text,
CONSTRAINT credits_movie_person_type_unique
UNIQUE (movie_id, person_id, credit_type)
);Why use a separate credits table?
Do not store a comma-separated actor list on each movie. A separate row for each movie/person/credit relationship makes it possible to query, update, and constrain credits without repeating people’s details. The credit_type field can hold values such as actor, director, or producer; character_name is optional because it does not apply to every credit.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWhat the uniqueness rule means
The composite unique constraint prevents the same person from being entered twice for the same movie with the same credit type. It still allows that person to have distinct credit types for that movie. If your catalog must represent multiple separate credits of one type for a person in one movie, remove or change this constraint to fit that rule.
Names are not reliable unique identifiers: different people can share a name. The generated person_id distinguishes records even when their names match.
Rank #3
Should genres have their own tables?
Use one genre text column on movies only if the application intentionally allows one genre per movie and does not need genres managed or filtered independently. If movies can have several genres, create a reusable genre table and a join table instead:
CREATE TABLE genres (
genre_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE
);
CREATE TABLE movie_genres (
movie_id bigint NOT NULL REFERENCES movies(movie_id) ON DELETE CASCADE,
genre_id bigint NOT NULL REFERENCES genres(genre_id) ON DELETE CASCADE,
PRIMARY KEY (movie_id, genre_id)
);The composite primary key prevents the same genre from being linked to the same movie more than once. Supabase’s tables and data guide also demonstrates PostgreSQL tables and join-table relationships using a movie-and-actor example.
Add sample records and query a movie’s cast or crew
Insert a movie, two people, and their credits. The following example uses one cast member and one director:
INSERT INTO movies (title, release_year, description)
VALUES ('Example Film', 2024, 'A sample catalog entry')
RETURNING movie_id;
INSERT INTO people (name)
VALUES ('Alex Rivera'), ('Jordan Lee');
INSERT INTO credits (movie_id, person_id, credit_type, character_name)
VALUES
(1, 1, 'actor', 'Mara'),
(1, 2, 'director', NULL);The sample assumes the newly created movie and people receive IDs 1 and 1–2. If the database already contains records, use the IDs returned by your inserts instead of assuming those values.
Join the three tables to show the people credited on a movie:
SELECT
m.title,
m.release_year,
p.name,
c.credit_type,
c.character_name
FROM movies AS m
JOIN credits AS c ON c.movie_id = m.movie_id
JOIN people AS p ON p.person_id = c.person_id
WHERE m.movie_id = 1
ORDER BY c.credit_type, p.name;Choose deletion rules and indexes deliberately
Deletion behavior
This starter schema uses ON DELETE CASCADE because credits depend on their movie and person: deleting either parent removes the linked credit rows. PostgreSQL also supports RESTRICT, NO ACTION, and SET NULL. Choose a different action if your application must preserve credits, prevent deletion while credits exist, or permit an unassigned relationship. With the current non-null foreign keys, SET NULL would require changing those columns to allow null values.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Indexes for lookup paths
PostgreSQL creates indexes for primary keys and unique constraints, but it does not automatically create an index on the referencing side of a foreign key. Add indexes when they support queries your application actually runs. For example, the credit query above filters by movie_id; the unique constraint already begins with movie_id, so it can support that lookup. To find every movie for a person efficiently, add an index on credits(person_id):
CREATE INDEX credits_person_id_idx ON credits (person_id);What should you add after the first schema?
Keep the initial catalog focused on movies, people, and credits. Ratings, reviews, user accounts, and streaming availability can be added when the application needs them; each introduces its own relationships and rules. For an app that needs remote access, a hosted PostgreSQL service is an optional deployment choice rather than a requirement for learning or creating this schema.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
