Supabase Relationships: Foreign Keys and Joins Explained

-- One-to-Many: Users → Posts
-- Each post belongs to one user

create table users (
  id uuid default gen_random_uuid() primary key,
  email text unique not null,
  username text unique not null,
  created_at timestamp with time zone default now()
);

create table posts (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references users(id) on delete cascade not null,
  title text not null,
  content text not null,
  created_at timestamp with time zone default now()
);

-- Index foreign key for faster joins
create index posts_user_id_idx on posts(user_id);

-- One-to-One: Users ↔ Profiles
-- Each user has exactly one profile

create table profiles (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references users(id) on delete cascade unique not null,
  bio text,
  avatar_url text,
  website text
);

-- Many-to-Many: Posts ↔ Tags
-- Posts can have multiple tags, tags can belong to multiple posts

create table tags (
  id uuid default gen_random_uuid() primary key,
  name text unique not null
);

create table post_tags (
  post_id uuid references posts(id) on delete cascade,
  tag_id uuid references tags(id) on delete cascade,
  primary key (post_id, tag_id)
);

create index post_tags_post_id_idx on post_tags(post_id);
create index post_tags_tag_id_idx on post_tags(tag_id);

// Get posts with user information (One-to-Many)
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    users (
      id,
      username,
      email
    )
  `)

// Result:
// [
//   {
//     id: 'post-1',
//     title: 'First Post',
//     content: '...',
//     users: {
//       id: 'user-1',
//       username: 'john_doe',
//       email: '[email protected]'
//     }
//   }
// ]

// Get users with their posts (reverse relationship)
const { data } = await supabase
  .from('users')
  .select(`
    *,
    posts (
      id,
      title,
      created_at
    )
  `)

// Get posts with comments and comment authors
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    comments (
      id,
      content,
      users (
        username
      )
    )
  `)

// Filter on related data
const { data } = await supabase
  .from('posts')
  .select('*, users(*)')
  .eq('users.username', 'john_doe')

// Count related records
const { data } = await supabase
  .from('users')
  .select('*, posts(count)')

// Get posts with their tags
const { data } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    post_tags (
      tags (
        id,
        name
      )
    )
  `)

// Result:
// [
//   {
//     id: 'post-1',
//     title: 'First Post',
//     post_tags: [
//       { tags: { id: 'tag-1', name: 'JavaScript' } },
//       { tags: { id: 'tag-2', name: 'Tutorial' } }
//     ]
//   }
// ]

// Get tags with their posts
const { data } = await supabase
  .from('tags')
  .select(`
    id,
    name,
    post_tags (
      posts (
        id,
        title
      )
    )
  `)

// Add tag to post (insert into junction table)
const { error } = await supabase
  .from('post_tags')
  .insert([{ post_id: postId, tag_id: tagId }])

// Remove tag from post
const { error } = await supabase
  .from('post_tags')
  .delete()
  .eq('post_id', postId)
  .eq('tag_id', tagId)

-- ✅ Good: Descriptive foreign key names
create table posts (
  id uuid primary key,
  author_id uuid references users(id) on delete cascade not null,
  category_id uuid references categories(id) on delete set null
);

-- ✅ Good: Add indexes immediately
create index posts_author_id_idx on posts(author_id);
create index posts_category_id_idx on posts(category_id);

-- ✅ Good: Use appropriate cascade actions
-- CASCADE for dependent data (comments must have a post)
-- SET NULL for optional relationships (post can exist without category)

-- ❌ Bad: Generic column names
create table posts (
  id uuid primary key,
  user_id uuid references users(id),  -- Which user? Author? Editor?
  parent_id uuid references posts(id) -- Unclear relationship
);

-- ❌ Bad: No cascade action specified
create table comments (
  post_id uuid references posts(id)  -- What happens when post deleted?
);