Build the library

This guide builds the example library from scratch in simple mode. By the end you will have a complete little database — authors, books, members, and loans — wired together with both kinds of relationship, and you will have used the whole create → add columns → relate → insert → query loop.

It picks up where Your first project leaves off. The only new idea is relationships, which we introduce as we go.

1. Create the authors table

We give each table a named primary key (like author_id) so that relationships read clearly later. with pk author_id(serial) makes the key a serial — an auto-incrementing number the database fills in for you:

create table authors with pk author_id(serial)
add column to authors: name (text)
add column to authors: birth_year (int)

2. Create the books table and relate it to authors

Build books the same way. The author_id column will hold which author wrote each book:

create table books with pk book_id(serial)
add column to books: title (text)
add column to books: author_id (int)
add column to books: published (int)
add column to books: isbn (text)

No two books should share an ISBN, so mark that column unique — a constraint the database will enforce on every insert:

add constraint unique to books.isbn

Now the new idea. Every book is written by one author, but an author can write many books — a one-to-many relationship. We declare it so the database keeps the link honest (you can never point a book at an author who does not exist):

add 1:n relationship as books_author from authors.author_id to books.author_id on delete cascade

Read it parent-to-child: from the authors side (the “one”) to the books side (the “many”). on delete cascade says that if an author is ever deleted, their books go too — see Relationships for the other options.

3. Create the members table

Members are the people who borrow books. This table stands alone for now:

create table members with pk member_id(serial)
add column to members: name (text)
add column to members: joined (date)

4. Create the loans table — the many-to-many bridge

A book can be borrowed by many members over time, and a member can borrow many books. That is a many-to-many relationship, and you do not model it with a single link — you use a third table in the middle. Each row in loans represents one borrowing event: one book, one member, and when it happened.

create table loans with pk loan_id(serial)
add column to loans: book_id (int)
add column to loans: member_id (int)
add column to loans: loaned_on (date)
add column to loans: returned_on (date)

loans is a bridge table (also called a junction table). It carries two one-to-many relationships — one to books, one to members — and together they express the many-to-many link:

add 1:n relationship as loans_book from books.book_id to loans.book_id on delete cascade
add 1:n relationship as loans_member from members.member_id to loans.member_id on delete cascade

You can confirm all three relationships at once:

show relationships

Relationships (3):
  books_author: authors.author_id → books.author_id on delete cascade
  loans_book: books.book_id → loans.book_id on delete cascade
  loans_member: members.member_id → loans.member_id on delete cascade

5. Add some rows

The serial keys (author_id, book_id, …) fill themselves in, so you leave them out of each insert. Start with the authors:

insert into authors (name, birth_year) values ('Ursula K. Le Guin', 1929)
insert into authors (name, birth_year) values ('Italo Calvino', 1923)
insert into authors (name, birth_year) values ('Octavia E. Butler', 1947)

Then their books. The author_id values (1, 2, 3) are the keys the database just assigned above — Le Guin is 1, Calvino is 2, Butler is 3:

insert into books (title, author_id, published, isbn) values ('A Wizard of Earthsea', 1, 1968, '978-0553383041')
insert into books (title, author_id, published, isbn) values ('The Left Hand of Darkness', 1, 1969, '978-0441478125')
insert into books (title, author_id, published, isbn) values ('Invisible Cities', 2, 1972, '978-0156453806')
insert into books (title, author_id, published, isbn) values ('Kindred', 3, 1979, '978-0807083697')

A couple of members:

insert into members (name, joined) values ('Grace Hopper', '2023-01-15')
insert into members (name, joined) values ('Alan Turing', '2023-03-02')

And finally two loans, linking a book to a member. Grace Hopper has borrowed book 1 and not yet returned it; Alan Turing borrowed book 3 and returned it. Leave returned_on out when the book is still on loan:

insert into loans (book_id, member_id, loaned_on) values (1, 1, '2024-05-01')
insert into loans (book_id, member_id, loaned_on, returned_on) values (3, 2, '2024-05-03', '2024-05-20')

6. Look at your data

Every table now has rows. show data prints them:

show data authors

┌───────────┬───────────────────┬────────────┐
│ author_id │ name              │ birth_year │
├───────────┼───────────────────┼────────────┤
│         1 │ Ursula K. Le Guin │       1929 │
│         2 │ Italo Calvino     │       1923 │
│         3 │ Octavia E. Butler │       1947 │
└───────────┴───────────────────┴────────────┘

show data loans

┌─────────┬─────────┬───────────┬────────────┬─────────────┐
│ loan_id │ book_id │ member_id │ loaned_on  │ returned_on │
├─────────┼─────────┼───────────┼────────────┼─────────────┤
│       1 │       1 │         1 │ 2024-05-01 │ (null)      │
│       2 │       3 │         2 │ 2024-05-03 │ 2024-05-20  │
└─────────┴─────────┴───────────┴────────────┴─────────────┘

The empty returned_on shows as (null) — the loan that is still out.

Where to go next

You now have the complete library. From here:

Ask questions across tables — the loans bridge only really pays off when you query through it. See Querying with joins.
See the relationships drawn out — show table books and show relationship books_author render the links as diagrams (Relationships).
Try the same build in advanced mode to see the SQL form of each command (Querying & inspecting, Tables).