Relationships

Draft

Relationships 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;

team	project
Core	PgQuest
Core	Website
Data	Warehouse

Sort  (cost=125.11..128.11 rows=1200 width=64)
  Sort Key: teams.name, projects.name
  ->  Hash Join  (cost=38.58..63.74 rows=1200 width=64)
        Hash Cond: (projects.team_id = teams.id)
        ->  Seq Scan on projects  (cost=0.00..22.00 rows=1200 width=36)
        ->  Hash  (cost=22.70..22.70 rows=1270 width=36)
              ->  Seq Scan on teams  (cost=0.00..22.70 rows=1270 width=36)

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;

project	member
PgQuest	Ada
PgQuest	Grace
Warehouse	Grace
Warehouse	Linus

Sort  (cost=245.99..251.64 rows=2260 width=64)
  Sort Key: projects.name, users.name
  ->  Hash Join  (cost=75.58..120.08 rows=2260 width=64)
        Hash Cond: (project_members.user_id = users.id)
        ->  Hash Join  (cost=37.00..75.55 rows=2260 width=36)
              Hash Cond: (project_members.project_id = projects.id)
              ->  Seq Scan on project_members  (cost=0.00..32.60 rows=2260 width=8)
              ->  Hash  (cost=22.00..22.00 rows=1200 width=36)
                    ->  Seq Scan on projects  (cost=0.00..22.00 rows=1200 width=36)
        ->  Hash  (cost=22.70..22.70 rows=1270 width=36)
              ->  Seq Scan on users  (cost=0.00..22.70 rows=1270 width=36)

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;

indexname	tablename
project_members_pkey	project_members
project_members_user_id_idx	project_members
projects_pkey	projects
projects_team_id_idx	projects
teams_pkey	teams
users_pkey	users

Sort  (cost=32.83..32.84 rows=3 width=128)
  Sort Key: i.relname
  ->  Nested Loop  (cost=21.62..32.81 rows=3 width=128)
        ->  Hash Join  (cost=21.35..27.66 rows=7 width=68)
              Hash Cond: (x.indrelid = c.oid)
              ->  Seq Scan on pg_index x  (cost=0.00..5.63 rows=163 width=8)
              ->  Hash  (cost=21.14..21.14 rows=17 width=68)
                    ->  Hash Join  (cost=1.06..21.14 rows=17 width=68)
                          Hash Cond: (c.relnamespace = n.oid)
                          ->  Seq Scan on pg_class c  (cost=0.00..19.71 rows=68 width=72)
                                Filter: (relkind = ANY ('{r,m,p}'::"char"[]))
                          ->  Hash  (cost=1.05..1.05 rows=1 width=4)
                                ->  Seq Scan on pg_namespace n  (cost=0.00..1.05 rows=1 width=4)
                                      Filter: (nspname = 'public'::name)
        ->  Index Scan using pg_class_oid_index on pg_class i  (cost=0.27..0.73 rows=1 width=72)
              Index Cond: (oid = x.indexrelid)
              Filter: (relkind = ANY ('{i,I}'::"char"[]))

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.