Relationships
DraftRelationships are how table design expresses real structure: ownership, membership, references, and many-to-many connections.
One-to-many
One team can own many projects through projects.team_id.
Loaded Database:relationships.database-init
SELECT teams.name AS team, projects.name AS project
FROM teams
JOIN projects ON projects.team_id = teams.id
ORDER BY team, project;Loading plan...Many-to-many
A join table connects projects and users when both sides can have many matches.
Loaded Database:relationships.database-init
SELECT projects.name AS project, users.name AS member
FROM project_members
JOIN projects ON projects.id = project_members.project_id
JOIN users ON users.id = project_members.user_id
ORDER BY project, member;Loading plan...Indexes on relationships
Foreign-key columns are often indexed because joins and lookups use them.
Loaded Database:relationships.database-init
SELECT indexname, tablename
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY indexname;Loading plan...What We Learned
- Foreign keyrequires a value to reference an existing row.
- One-to-manystores the parent id on the many side.
- Many-to-manyuses a join table between both sides.
- Relationship indexeshelp joins and foreign-key lookups stay fast.