How to Design a Database with Tables, Primary Keys and Foreign Keys

by Souy Soeng /October 09, 2026

Diagram of a library database: a loans table in the middle linked by foreign keys to books and members tables

To design a database, you decide which tables you need, give each table a primary key that identifies every row, and link related tables with foreign keys. This guide follows a small library database with books, members and loans. It is the same example built in the video, so you can type along and check that each step works before moving on.

You don't need advanced SQL. If you understand rows and columns and have any SQL tool, you have enough. The examples use SQLite because the whole database lives in a single file, with no server to install. The SQL you write here also carries over to larger systems such as PostgreSQL.

In short

Turn each important noun (books, members, loans) into its own table.
Give every table a primary key, a unique ID that no other row can share.
Store each fact in one place and point to it with an ID instead of copying it.
Link tables with foreign keys, and in SQLite turn the checks on with PRAGMA foreign_keys = ON.
Prove the design works with sample rows, a join and a deliberately broken insert.

Understand why one big spreadsheet breaks down

Picture a library that tracks everything in one large sheet and adds a row every time someone borrows a book. At first this feels quick. Problems appear once the same details are copied again and again. A member's name and email get typed onto every loan she makes, and a book's title gets typed onto every loan of that book.

Two problems follow. The first is that copies drift apart. If a member changes her email, you have to find and fix every row that mentions it. Miss one, and your data now disagrees with itself. The second is that links point nowhere. A plain sheet will accept a loan for a book that was never added, or for a member who has left.

Good database design fixes both problems at once. Each fact is stored in exactly one place, and the database refuses references to rows that don't exist. The rest of this guide shows how to build that structure step by step.

Does the same fact, like an email, appear on many rows?
Does one person appear under several spellings?
Could someone record a loan for a book that isn't there?

List the things you need to store as tables

Start on paper, not in a SQL tool. Describe the library out loud and listen for the nouns: books, members and loans. Each important noun becomes one table. Books are what you lend, members are the people who borrow, and loans record who has which book.

Loans deserve a closer look. A loan isn't a book or a person. It is an event that connects one member to one book. Because that event gets its own table, books and members each stay focused on a single kind of thing, and neither one has to hold details about the other.

Next, name the tables using habits most experienced designers follow. Use short, lowercase names without spaces, such as books, members and loans, so you never need quotes around them in SQL. Keep one kind of thing per table and one item per row. Remember that descriptive details, like a title or an email, are columns, not tables.

Each table holds one kind of thing, never a mix
Each row describes exactly one book, member or loan
Names are lowercase, have no spaces and follow one consistent style
If a column only makes sense for some rows, you may be mixing two things

Choose a primary key for every table

A primary key is a unique ID that names each row. Think of a library card number. Two members might both be called Ana, but they never share a card number. The database enforces two rules on a primary key: every row must have a value, and no two values can repeat. It checks these rules on every insert.

Create the books table with book_id as its primary key and a title column. Running it should produce no errors, just a new empty table. In SQLite, an INTEGER PRIMARY KEY is special. If you leave it out when adding a row, SQLite fills in the next number for you, so you never have to invent IDs by hand.

Avoid using real-world values like titles or emails as keys. Two books can share a title, and people change their emails. A plain number that carries no meaning is the safer choice.

You can watch the rule work. Insert (1, 'Dune') and the row is saved. Then insert (1, 'Emma'), and SQLite rejects it with a UNIQUE constraint error, so the table still holds one row. That error protects you, because a silent duplicate could make two books look like one in every report. To fix it, leave the ID out and SQLite will give Emma the next free number, 2.

Every table has exactly one primary key column
IDs are plain integers, not titles or emails
You let SQLite assign IDs instead of typing them
A second row with an existing ID fails with a UNIQUE error

CREATE TABLE books (
  book_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

Add columns and keep each fact in one place

For each table, ask what you need to know about one of these items. A book needs a title and an author. A member needs a name and an email. Each answer becomes a column, and each column holds one simple fact about the item in that row.

In the members table, member_id is the primary key, the name is NOT NULL, and the email is UNIQUE. Add an author column to books the same way. Every column also gets a type: TEXT for names and titles, and INTEGER for whole numbers like IDs. Constraints sit next to the type. NOT NULL makes a column required, and UNIQUE stops two rows from sharing a value, which suits emails well.

Keep one value per cell. If a member has several phone numbers, don't squeeze them into one column separated by commas. When several values pile into one cell, it usually means that data belongs in its own table.

The most important habit is storing each fact in exactly one place. A messy design repeats the member's name and email on every loan. A clean design stores only the member's ID. When something changes, you update one row instead of many, there are no copies to drift apart, and the loans table stays small. To test any column, ask whether this fact appears anywhere else. If it does, keep the real copy in one table and point to it with an ID.

Every column has a type, such as TEXT or INTEGER
Required columns are marked NOT NULL
Values that must never repeat, like email, are marked UNIQUE
No cell holds a list of values
No fact is stored in two tables

CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

Link tables with foreign keys

A foreign key is a column that holds another table's primary key. It works like a pointer to the exact row it means. The loans table is where this linking happens. Instead of copying a title or a name, each loan stores two numbers: which book was borrowed, and which member borrowed it.

The code starts by switching on foreign key checks with PRAGMA foreign_keys = ON, because SQLite leaves them off by default. Then loans gets its own primary key, plus a book_id column that references books and a member_id column that references members. REFERENCES is a promise. Once checks are on, you can't record a loan for book 99 unless book 99 exists.

This setting catches many beginners. SQLite enforces foreign keys only while the pragma is on, and it applies to one connection at a time. Run it every time you open the database, or put it in your app's startup code. Order also matters: create books and members before loans, since loans depend on them.

Seen from above, the design has two independent tables on the outside and a linking table in the middle. Dune can be borrowed by Ana in March and by Ben in May. That gives two loan rows pointing at one book row, with no copied details. The same pattern appears in many systems. Shops link customers and products through orders, and schools link students and classes through enrollments.

PRAGMA foreign_keys = ON runs on every new connection
Each foreign key column uses REFERENCES to name its parent table
Parent tables are created before the tables that point to them
The loans table stores IDs, never copied names or titles

PRAGMA foreign_keys = ON;
CREATE TABLE loans (
  loan_id INTEGER PRIMARY KEY,
  book_id INTEGER REFERENCES books,
  member_id INTEGER REFERENCES members,
  loan_date TEXT);

Insert sample rows and test the join

Now prove the design works. Insert one book (Dune), one member (Ana) and one loan linking them. Then run a SELECT that starts at loans, joins books on book_id and joins members on member_id. A join follows the foreign keys from each loan back to the real book and member, and shows them side by side.

The expected result is a single row: Ana | Dune. That row is your success signal. If nothing comes back, the IDs don't match. If you see extra rows, a join condition is probably missing or wrong.

Next, test the guardrails. Try inserting a loan for book 99, which doesn't exist. With foreign keys on, SQLite should refuse it with a FOREIGN KEY constraint error. If the insert succeeds, your checks are off. Finally, run SELECT COUNT(*) on each table and compare the numbers with what you inserted.

The join returns exactly one row: Ana | Dune
A loan for book 99 fails with a FOREIGN KEY error
Row counts match what you inserted into each table

INSERT INTO books VALUES (1,'Dune','Herbert');
INSERT INTO members VALUES (1,'Ana','a@x.io');
INSERT INTO loans VALUES (1,1,1,'2026-10-01');
SELECT m.name, b.title FROM loans l
JOIN books b ON b.book_id = l.book_id
JOIN members m ON m.member_id = l.member_id;

Fix common errors and guard every row

When something fails, read the error message first. SQLite's messages are short, but they name the constraint that broke. A UNIQUE error means a duplicate key, so let SQLite assign IDs. A FOREIGN KEY error means a missing reference, so insert the book or member first. If bad loans save silently, checks are off, so run the pragma. Repeated data needs its own table.

Think of constraints as checkpoints that every new row must pass. NOT NULL stops blanks, UNIQUE stops repeats, and foreign keys stop loans that point nowhere. No single rule catches everything, but together they filter out almost every bad row before it is saved.

A few habits keep the design healthy as it grows. Sketch tables as boxes and foreign keys as arrows before writing code, because moving an arrow on paper is far quicker than reshaping a table full of data. Name keys the same way everywhere, using book_id in both books and loans. As a next challenge, add a return_date column to loans, left empty until the book comes back, then query the books currently out.

Read which constraint failed before changing anything
Insert parent rows before the rows that reference them
Use the same key name in every table that uses it
Sketch changes on paper before altering real tables

Key takeaways

List, key, describe, link: then verify with real data.
Each real-world thing gets its own table, and each row is one item.
Primary keys stop duplicates, and foreign keys stop broken links.
Store each fact once and refer to it by ID everywhere else.
In SQLite, foreign keys work only after PRAGMA foreign_keys = ON on each connection.
A join that returns the expected rows is the clearest proof your links work.

Frequently asked questions

What is the difference between a primary key and a foreign key?

A primary key is a unique ID that identifies each row in its own table, such as book_id in books. A foreign key is a column in another table that stores that ID to point at the row, such as book_id in loans.

Why shouldn't I use an email or a title as a primary key?

Titles can repeat, since two books can share a name, and emails can change over time. A plain number with no meaning stays unique and stable, which is exactly what a key needs.

Why does SQLite let me insert a loan for a book that doesn't exist?

SQLite leaves foreign key checks off by default, and the setting applies to one connection at a time. Run PRAGMA foreign_keys = ON each time you open the database, or put it in your app's startup code.

Do I need to type IDs myself when inserting rows?

No. In SQLite, if a column is an INTEGER PRIMARY KEY and you leave it out of an insert, SQLite assigns the next number automatically. This also avoids UNIQUE errors from accidentally reusing an ID.

How do I know if my database design works?

Insert a few sample rows and run a join across the linked tables. If it returns exactly the rows you expect, your links work. Then try a deliberately bad insert, such as a loan for a missing book, and confirm the database rejects it.

Watch the full video on YouTube →

Tag

Ad