Entities and relationships

What you will be able to do

Decide what the help desk keeps records about and how each record is named. Then decide how many records of one kind belong to a record of another kind. These choices shape every table, rule and query in the rest of the course.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• Primary keys, foreign keys, one-to-many and how to read an entity relationship diagram, from Relational concepts in SQL and Data Preparation for AI.
• The help desk’s tables and how to open psql, from From SQLite to PostgreSQL.
• What the help desk stores and how it uses it, from Access-pattern inventory.

What you need on your computer

• The project ~/projects/ticket-api after Module 1 (the snapshot end-of-m01), with its virtual environment active.
• PostgreSQL running (docker compose up -d db) with the small data loaded. python -m pytest prints 34 passed.

This lesson adds docs/schema.md to the project.

The outputs in this module come from an Apple M4 with 16 GB, on macOS 26.6. The database was PostgreSQL 18.6 with pgvector 0.8.7, in Docker. Your times will differ; the counts will not.

We tested the steps on macOS only. The Windows commands are written out, but not tested.

A library card

Think of a library. It keeps records of three kinds of things: members, books and loans. When you join, you get a card with a number. The number stays the same when you move house or change your name. It is never given to another member, even after you leave.

The library has two members called Emma Petrov. Nobody mixes them up, because the librarian looks at the card number, not the name. A loan slip does not repeat your address either. It has your card number, and your address is in one place: your member record.

Now count the links. One member can have no loans, one loan or twenty. Each loan belongs to exactly one member and one copy of a book. A library that wrote “the book on loan” on the member’s card would be stuck when the member takes a second book.

This lesson has three ideas: kinds of things, a name that never changes, and how many of one kind belong to another.

The problem: a row that cannot hold a list

Look at ticket T-30034, “Parcel arrived damaged”. Open psql in the project folder:

docker compose exec db psql -U tickets -d tickets

Then ask for the ticket’s files:

SELECT ticket_id, file_name, content_type, size_bytes
FROM attachments WHERE ticket_id = 'T-30034';

 ticket_id |  file_name  | content_type | size_bytes
-----------+-------------+--------------+------------
 T-30034   | photo-1.png | image/png    |       3613
 T-30034   | photo-2.png | image/png    |       3767
(2 rows)

The customer sent two photos. Suppose that the old tool had kept the file in a column of the ticket, photo. The first photo fits, and the second one has no place. A second column, photo_2, only moves the problem: then a third photo has no place.

A column holds one value. A table of files, where each row names its ticket, holds any number of files per ticket.

Text description of the diagram

On the left, the ticket row has one photo column, with photo-1.png in it. The second photo, photo-2.png, has no place. A second column would only move the limit to two photos. On the right, the files are rows of their own table, attachments. Each file row holds the ticket ID T-30034, so the ticket can have zero, one, two or more files.

There is a second problem, about names. Ask for a customer by name:

SELECT customer_id, name, email, segment, region, joined_on
FROM customers WHERE name = 'Emma Petrov';

 customer_id |    name     |          email           | segment | region | joined_on
-------------+-------------+--------------------------+---------+--------+------------
 C-0004      | Emma Petrov | emma.petrov@example.com  | home    | west   | 2020-04-05
 C-0012      | Emma Petrov | emma.petrov2@example.com | trade   | west   | 2025-11-22
(2 rows)

Two different customers have the same name, like the two members of the library. If a ticket said “from Emma Petrov”, nobody could tell which customer wrote it.

The idea: entities, identifiers and cardinality

An entity is a kind of thing that the help desk keeps records about: a customer, a ticket, a message. Each entity gets its own table, and one row is one thing. The SQL course called this the grain of a table.

An identifier names one thing. In a table, it is the primary key. A good identifier is stable: it never changes, and it is never given to another thing. The library card number is stable. A name is not unique, and an email address can change.

A relationship says how two entities belong together, for example “a customer writes tickets”. Its cardinality says how many on each side: at least how many (zero or one), and at most how many (one or many). The word comes from mathematics, where the cardinality of a set is the number of its members.

There are three common shapes:

• One-to-many: one customer has zero or more tickets, and each ticket has exactly one customer. The foreign key goes on the “many” side: tickets.customer_id.
• One-to-one, usually “one to zero or one”: one ticket has at most one row in another table. That table’s primary key can also be its foreign key.
• Many-to-many: one ticket can cite several policy documents, and one document can be cited by many tickets. Neither side can hold a list, so a third table holds one row per pair. You design one at the end of this lesson.

Here is a tiny worked example, with every value visible. Customer C-0022 is Hugo Garcia, and his ticket T-30002 is the course’s running ticket:

Record	How many	The rows
Hugo Garcia’s tickets	6	T-30002, T-30111, T-30119, T-30130, T-30140, T-30171
Messages of T-30002	1	message 2, from an agent: “Could you send the receipt or a screenshot of the …”
AI runs of T-30002	1	run 3: classify with keywords-1.0, cost 0, status ok
Files of T-30002	0	none
Vectors of T-30002	1	384 numbers

Every child row names its parent: a ticket holds C-0022, and a message holds T-30002. No parent holds a list of its children. That is how a table stores “many”: many rows that point to one row.

The help desk in one picture

An entity relationship diagram (ERD) draws the entities, their keys and their relationships. Here is the help desk’s schema at the end of this module:

The help desk's entities and relationships at the end of Module 2. The marks at each end of a line say how many: a bar is one, a circle is zero, a fork is many.

Text description of the diagram

Seven tables. customers has the primary key customer_id (text, such as C-0001). It also has a name, an email that is unique in any case, a segment and a region. A line from customers to tickets says: each ticket has exactly one customer, and a customer has zero or more tickets. tickets has the primary key ticket_id (text, such as T-30001) and the foreign key customer_id. It also has the subject and body, team, priority 1 to 3, status, times, and message_count, a deliberate copy. Four lines leave tickets. To messages (message_id, a number): each message has exactly one ticket, and a ticket has zero or more messages. To attachments (attachment_id, a UUID; object_key, unique): each file has exactly one ticket, and a ticket has zero or more files. To ai_runs (run_id, a number): a run has zero or one ticket, and a ticket has zero or more runs. To ticket_embeddings (the key is ticket_id): a vector has exactly one ticket, and a ticket has zero or one vector. documents (key: doc_id and version) has no line.

Read each line from both ends, as in the SQL course. Then ask why it has that shape, because each mark is a decision:

• customers → tickets: exactly one customer. Grace’s team must always know who to answer. A ticket without a customer is a mistake, so customer_id is required.
• tickets → messages and attachments: exactly one ticket. A message or a file without its ticket means nothing to anybody.
• tickets → ai_runs: zero or one ticket. Priya compares models by their cost per month. If a customer asks Larkfield to delete their data, the tickets go, but the cost of their AI runs must stay in the totals. So a run can lose its ticket, and its ticket_id may be empty. Recovery and retention uses this rule.
• tickets → ticket_embeddings: zero or one vector. Today the vector table’s key is the ticket ID, so a ticket has at most one vector. In Vector storage, each ticket gets one vector per embedding version, and this end becomes “zero or more”.
• documents: no line. The assistants read the policy documents, but no ticket owns one. A document’s key has two columns, doc_id and version. For example, returns-policy has version 3, valid to 31 October 2026, and version 4, valid from 1 November 2026.

Sam asks: “Why does a ticket not keep a list of its message IDs? It would save a join.” Mei answers with the photo problem. A list in one column has no fixed length, and the database cannot check that each item exists. A foreign key on the “many” side can be checked, and the next lesson makes the database check it.

Choose the identifiers

The project uses three kinds of primary key. Each one is a choice, with a reason and a cost.

Text IDs from the old tool, for tickets and customers (T-30001, C-0001). These IDs are public. They appear in emails to customers, and agents say them on the phone. The customer IDs are also the same as in the SQL course’s export. If the project keeps them, every old email and report still points to the right record. So the database must never invent a different ticket ID, and it must never reuse one.

They cost a little. A text key is compared character by character, which is slower than a comparison of two numbers. Its format can go wrong, for example 30003 without the T-, so the next lesson adds a rule for the format. Here, a stored ticket ID takes 8 bytes, the same as a 64-bit number, so its size is not a problem. A long text key would be: olga.olsen@example.com takes 23 bytes, and every index on such a key gets wider.

Identity numbers, for messages and AI runs. Nobody says “message 332” on the phone, so a number from the database is enough. message_id bigint GENERATED BY DEFAULT AS IDENTITY means that PostgreSQL gives out the next number when the program does not send one.

A UUID, for attachments. A UUID is a random 128-bit value, such as fcf3867b-6929-4624-9e25-c526ce2f3e67, and it takes 16 bytes. The program makes it before the file is uploaded, and it becomes part of the file’s address in object storage: tickets/T-30034/fcf3867b-…/photo-1.png. Nobody can guess the address of another customer’s file from it. Object-storage patterns uploads files this way.

Never a name or an email address. Names repeat, as the two Emma Petrovs show. Emails change, and an email is personal data. As a key, it would be copied into every row that points to the customer. Then a deletion of the customer’s data would have to find every copy.

Entity	Key	Example	Why
customers	text from the old tool	C-0022	public, stable, used in old reports
tickets	text from the old tool	T-30002	public: on emails and on the phone
messages, ai_runs	identity number	2, 3	internal; the database gives it out
attachments	UUID	fcf3867b-…	made before the upload; part of the file’s address
documents	doc_id + version	returns-policy, 4	a document has several versions

Guided practice: check the relationships in the data

The ERD makes promises, for example “a ticket has zero or more messages” and “each message has exactly one ticket”. Check them against the data.

1. Count the tickets by their number of messages. Run this query in psql:

   SELECT n AS messages, count(*) AS tickets
   FROM (SELECT t.ticket_id, count(m.message_id) AS n
         FROM tickets t LEFT JOIN messages m ON m.ticket_id = t.ticket_id
         GROUP BY t.ticket_id) per_ticket
   GROUP BY n ORDER BY n;

    messages | tickets
   ----------+---------
           0 |      26
           1 |      71
           2 |      64
           3 |      24
           4 |      15
   (5 rows)

   26 tickets have no message after the first one, and no ticket has more than 4. “Zero or more” is right. The LEFT JOIN keeps the tickets without messages. An inner join would hide the zero row.

2. Now check the other end. Does every message have its ticket?

   SELECT m.message_id, m.ticket_id, m.author, m.body
   FROM messages m LEFT JOIN tickets t ON t.ticket_id = m.ticket_id
   WHERE t.ticket_id IS NULL;

    message_id | ticket_id | author |             body
   ------------+-----------+--------+------------------------------
           332 | T-29994   | agent  | Test message, please ignore.
           333 | T-29997   | agent  | Test message, please ignore.
           334 | T-29999   | agent  | Test message, please ignore.
   (3 rows)

   These are three orphans: messages whose ticket does not exist. They are test messages, and somebody deleted their tickets by hand in the old tool. The diagram says “exactly one ticket”, but the database does not know the diagram yet. The course data has these three rows on purpose, and the next lesson deals with them.

3. Quit psql with \q. Copy the schema document from the snapshot into your project. In ~/projects/ticket-api, run:

   cp ../nextia-learning-labs/C20/snapshots/end-of-m02/docs/schema.md docs/

   On Windows, in PowerShell: Copy-Item ..\nextia-learning-labs\C20\snapshots\end-of-m02\docs\schema.md docs\.

4. Open docs/schema.md in VS Code, and open its preview (Ctrl+Shift+V, or Cmd+Shift+V on macOS). The diagram is written in Mermaid, a text format for diagrams, and GitHub draws it too. If the preview shows only text, read the text: each line such as tickets ||--o{ messages : "has" is one relationship.

docs/schema.md draws “zero or more” vectors per ticket, because it already shows where the course goes. The file names the later migrations that change the schema.

Independent variation: tickets that cite documents

The assistants of RAG: Building AI That Uses Your Data answer with the policy documents that they used. Grace wants to keep these citations: which document versions each ticket’s reply cited.

Draw the relationship between tickets and documents on paper, and answer three questions:

1. What is the cardinality on each side?
2. Where do the keys go? Remember that a document’s key has two columns.
3. What is the primary key of the new table, and what does it stop?

Show an example answer

It is many-to-many. A ticket can cite zero or more document versions, and zero or more tickets can cite a document version. Neither side can hold a list, so a new table holds one row per citation:

CREATE TABLE ticket_citations (
    ticket_id   text    NOT NULL REFERENCES tickets (ticket_id) ON DELETE CASCADE,
    doc_id      text    NOT NULL,
    doc_version integer NOT NULL,
    PRIMARY KEY (ticket_id, doc_id, doc_version),
    FOREIGN KEY (doc_id, doc_version) REFERENCES documents (doc_id, version)
);

It has two foreign keys: one to the ticket, and one with two columns to the document version. The primary key is the three columns together, so a ticket cannot cite the same version twice. The ERD gets two one-to-many lines: from tickets to ticket_citations, and from documents to ticket_citations. You build and test this table in the module practice at the end of Transactions. It does not become part of the project.

Common mistake

'Find the customer by their name'

Symptom: a query or a program looks up a customer by name and gets two rows. C-0004 and C-0012 are both called Emma Petrov. A program that takes the first row attaches the ticket to the wrong customer, and nothing reports an error.

Cause: a name is not an identifier. It is not unique, and it can change. An email address is unique here, but it can change too, and it is personal data.

Diagnose: for a column that you use to find “the one” row, look for repeated values: SELECT name, count(*) FROM customers GROUP BY name HAVING count(*) > 1; returns Emma Petrov | 2.

Fix: point to rows by their stable primary key (C-0004). Use the name only to show to people. If people search by name, show every match with its ID, and let them choose.

When to model this way, and what it costs

Use one table per entity, with the foreign key on the “many” side, for data that the application owns and changes: tickets, messages, runs. Then each fact lives in one place, and the database can check every link.

It costs something:

• Joins. To show a ticket with its customer’s name and its messages, a query joins three tables. With the right indexes, that is fast. Read a query plan measures it.
• Decisions up front. Each mark on a line is a business decision, such as whether a run keeps its cost without its ticket. Ask the people who own the data. For the help desk, Grace and Priya answered.
• Some data does not need its own table. A list that the program only stores and shows can stay in one column, for example the raw output of a model. The test is simple. If you must find rows by an item, count the items or check that each item exists, give the items their own table.

Knowledge check

Check your understanding

The help desk adds a new table, refunds: one row per refund that an agent makes for a ticket. A ticket can have no refund, one refund or several. Draw the line between tickets and refunds. Which table gets the foreign key, and is it required?

Show the answer

It is one-to-many: each refund has exactly one ticket, and a ticket has zero or more refunds. The foreign key goes on the “many” side, refunds.ticket_id. It is required, because nobody can explain or check a refund without its ticket. The ticket gets no refund column: a second refund would have no place, as photo-2.png had no place in a photo column.

Check your understanding

Sam proposes the customer's email address as the primary key of customers: 'It is unique anyway, and we would not need C-0001.' Give two reasons against it, with an example from the help desk.

Show the answer

First, an email can change. A customer with a new address would need a new key, and every ticket that points to the old one would have to change too. A good identifier is stable. Second, an email is personal data. As a key, it is copied into every ticket of the customer, so a deletion of a customer’s data must find every copy. Also, a key of 23 bytes or more makes every index on it wider than the 8 bytes of C-0001.

Check your understanding

Why can an AI run have zero or one ticket, when a message must have exactly one?

Show the answer

Because the help desk needs the run after its ticket is gone. Priya’s monthly cost totals include every run. If a customer’s data is deleted, the tickets and their messages go. The runs stay with an empty ticket ID, so the totals stay right. A message without its ticket has no use, so it must have exactly one.

Recap

• An entity is a kind of thing that the help desk keeps records about: one table each, one row per thing.
• An identifier must be stable. Tickets and customers keep the public IDs of the old tool, messages and runs get a number from the database, and attachments get a UUID. Names and emails are never keys: remember the two Emma Petrovs.
• Cardinality says how many on each side. A row cannot hold a list (the second photo of T-30034), so the foreign key goes on the “many” side.
• The help desk’s ERD shows a decision at each end of each line. For example, an AI run keeps its cost without its ticket. docs/schema.md holds it in the project.
• The data breaks one promise already: three messages point to tickets that do not exist. Next, the database learns the rules and refuses such rows.

Next: Integrity constraints