Relationships & Foreign Keys¶

Các loại Relationship¶

Relationship Types¶

┌─────────────────────────────────────────────────────────────┐
│                    RELATIONSHIP TYPES                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  1. ONE-TO-ONE (1:1)                                        │
│     User ←────────→ Profile                                 │
│     Mỗi user có một profile                                 │
│                                                              │
│  2. ONE-TO-MANY (1:N)                                       │
│     Project ←───────→ Tasks                                 │
│     Mỗi project có nhiều tasks                              │
│                                                              │
│  3. MANY-TO-MANY (M:N)                                      │
│     Users ←──┬──→ Projects                                  │
│              │                                              │
│        project_members (junction table)                     │
│                                                              │
└─────────────────────────────────────────────────────────────┘

One-to-One Relationship¶

User ↔ Profile¶

-- auth.users là built-in
-- profiles extend user data

CREATE TABLE public.profiles (
  -- FK is also PK (1:1)
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,

  full_name TEXT,
  avatar_url TEXT,
  bio TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Query với join
SELECT
  u.email,
  p.full_name,
  p.avatar_url
FROM auth.users u
JOIN public.profiles p ON u.id = p.id;

Supabase Client¶

// Fetch user with profile
const { data } = await supabase
  .from('profiles')
  .select(`
    *,
    user:auth.users(email)
  `)
  .eq('id', userId)
  .single();

One-to-Many Relationship¶

Project → Tasks¶

CREATE TABLE public.projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  owner_id UUID REFERENCES auth.users(id) ON DELETE CASCADE
);

CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,

  -- Foreign Key to projects (Many tasks → One project)
  project_id UUID NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for faster joins
CREATE INDEX idx_tasks_project ON tasks(project_id);

Supabase Client - Nested Query¶

// Get project with all tasks
const { data: project } = await supabase
  .from('projects')
  .select(`
    id,
    name,
    tasks (
      id,
      title,
      status,
      created_at
    )
  `)
  .eq('id', projectId)
  .single();

// Result:
// {
//   id: "uuid",
//   name: "Project 1",
//   tasks: [
//     { id: "uuid", title: "Task 1", ... },
//     { id: "uuid", title: "Task 2", ... }
//   ]
// }

Many-to-Many Relationship¶

Users ↔ Projects (với roles)¶

-- Junction table
CREATE TABLE public.project_members (
  -- Composite Primary Key
  project_id UUID REFERENCES public.projects(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Extra data on relationship
  role TEXT DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  joined_at TIMESTAMPTZ DEFAULT NOW(),

  PRIMARY KEY (project_id, user_id)
);

-- Indexes
CREATE INDEX idx_project_members_user ON project_members(user_id);
CREATE INDEX idx_project_members_project ON project_members(project_id);

Query Many-to-Many¶

// Get project with members
const { data: project } = await supabase
  .from('projects')
  .select(`
    id,
    name,
    project_members (
      role,
      joined_at,
      user:profiles (
        id,
        full_name,
        avatar_url
      )
    )
  `)
  .eq('id', projectId)
  .single();

// Get all projects for a user
const { data: userProjects } = await supabase
  .from('project_members')
  .select(`
    role,
    project:projects (
      id,
      name,
      owner_id
    )
  `)
  .eq('user_id', userId);

ON DELETE Behaviors¶

Cascade vs Set Null vs Restrict¶

-- CASCADE: Xóa parent → xóa children
project_id UUID REFERENCES projects(id) ON DELETE CASCADE
-- Xóa project → xóa tất cả tasks

-- SET NULL: Xóa parent → set FK = null
assignee_id UUID REFERENCES users(id) ON DELETE SET NULL
-- Xóa user → task.assignee_id = NULL

-- RESTRICT: Không cho xóa parent nếu có children
category_id UUID REFERENCES categories(id) ON DELETE RESTRICT
-- Không xóa được category nếu còn products

-- NO ACTION (default): Giống RESTRICT

Khi nào dùng gì?¶

Behavior	Use Case
CASCADE	Owned data (project → tasks)
SET NULL	Optional reference (task → assignee)
RESTRICT	Required reference (order → product)

Self-referencing Relationship¶

Tasks với Subtasks¶

CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

  -- Self-reference for subtasks
  parent_id UUID REFERENCES public.tasks(id) ON DELETE CASCADE,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index
CREATE INDEX idx_tasks_parent ON tasks(parent_id);

Query Hierarchical Data¶

// Get task with subtasks
const { data: task } = await supabase
  .from('tasks')
  .select(`
    id,
    title,
    subtasks:tasks!parent_id (
      id,
      title,
      status
    )
  `)
  .eq('id', taskId)
  .single();

// Get all root tasks (no parent)
const { data: rootTasks } = await supabase
  .from('tasks')
  .select(`
    *,
    subtasks:tasks!parent_id (*)
  `)
  .is('parent_id', null)
  .eq('project_id', projectId);

Recursive Query với CTE¶

Lấy toàn bộ tree¶

-- Get all descendants of a task
WITH RECURSIVE task_tree AS (
  -- Base case: root task
  SELECT id, title, parent_id, 0 as depth
  FROM tasks
  WHERE id = 'root-task-id'

  UNION ALL

  -- Recursive case: children
  SELECT t.id, t.title, t.parent_id, tt.depth + 1
  FROM tasks t
  JOIN task_tree tt ON t.parent_id = tt.id
)
SELECT * FROM task_tree;

Supabase RPC Function¶

-- Create function for recursive query
CREATE OR REPLACE FUNCTION get_task_tree(root_id UUID)
RETURNS TABLE (
  id UUID,
  title TEXT,
  parent_id UUID,
  depth INTEGER
) AS $$
  WITH RECURSIVE task_tree AS (
    SELECT t.id, t.title, t.parent_id, 0 as depth
    FROM tasks t
    WHERE t.id = root_id

    UNION ALL

    SELECT t.id, t.title, t.parent_id, tt.depth + 1
    FROM tasks t
    JOIN task_tree tt ON t.parent_id = tt.id
  )
  SELECT * FROM task_tree;
$$ LANGUAGE sql STABLE;

// Call from client
const { data } = await supabase.rpc('get_task_tree', {
  root_id: taskId
});

Relationship Shortcuts¶

// Supabase auto-detects relationships via FK

// Forward relation (child → parent)
const { data } = await supabase
  .from('tasks')
  .select(`
    *,
    project:projects(name),
    assignee:profiles(full_name)
  `);

// Reverse relation (parent → children)
const { data } = await supabase
  .from('projects')
  .select(`
    *,
    tasks(count)
  `);

// Multiple levels deep
const { data } = await supabase
  .from('projects')
  .select(`
    name,
    tasks (
      title,
      comments (
        content,
        author:profiles(full_name)
      )
    )
  `);

Best Practices¶

Relationship Design¶

1. Luôn đặt Foreign Keys
   - Data integrity
   - Auto-join trong Supabase

2. Index tất cả FK columns
   - Faster JOINs
   - Faster ON DELETE CASCADE

3. Chọn ON DELETE behavior phù hợp
   - CASCADE cho owned data
   - SET NULL cho optional references

4. Tránh deep nesting
   - Max 3-4 levels
   - Dùng RPC cho complex queries

5. Junction table cho M:N
   - Thêm metadata (role, joined_at)
   - Composite PK

Common Patterns¶

Pattern	Implementation
User Profile	1:1 with auth.users
Comments	1:N with polymorphic
Tags	M:N with junction
Tree/Hierarchy	Self-reference
Audit Log	1:N với JSONB

Tổng kết¶

Relationship Types¶

Type	FK Location	Example
1:1	Either table	user ↔ profile
1:N	Child table	project → tasks
M:N	Junction table	users ↔ projects
Self	Same table	task → subtasks

Supabase Query Patterns¶

// Forward: child.select('*, parent(*)')
// Reverse: parent.select('*, children(*)')
// M:N: either.select('*, junction(*, other(*))')

Q&A¶

Có relationship phức tạp nào trong dự án?
Đã dùng CTE recursive query chưa?
Vấn đề gì với nested data?

Relationships & Foreign Keys¶

Các loại Relationship¶

Relationship Types¶

One-to-One Relationship¶

User ↔ Profile¶

Supabase Client¶

One-to-Many Relationship¶

Project → Tasks¶

Supabase Client - Nested Query¶

Many-to-Many Relationship¶

Users ↔ Projects (với roles)¶

Query Many-to-Many¶

ON DELETE Behaviors¶

Cascade vs Set Null vs Restrict¶

Khi nào dùng gì?¶

Self-referencing Relationship¶

Tasks với Subtasks¶

Query Hierarchical Data¶

Recursive Query với CTE¶

Lấy toàn bộ tree¶

Supabase RPC Function¶

Relationship Shortcuts¶

Embedding Related Data¶

Best Practices¶

Relationship Design¶

Common Patterns¶

Tổng kết¶

Relationship Types¶

Supabase Query Patterns¶

Q&A¶