# Tables, Relationships and Migrations — Supabase

Source: https://www.skillbyai.com/en/supabase/d-tables

> Design the schema in SQL.

## Plain Postgres schema

Tables can be created with the dashboard Table Editor, the SQL Editor or migration files. Use **foreign keys** for relationships: PostgREST reads them to allow nested selects across tables. User-owned rows usually reference `auth.users(id)`, the table Supabase Auth maintains. Prefer keeping schema changes in **migration files** in version control, even if you sketch in the dashboard first, so every environment can be rebuilt the same way.

## Tables, queries and functions

Model data in SQL, query it from the client and push logic into Postgres functions.

![Three ideas: schema and migrations, querying with supabase-js, RPC.](assets/figures/supabase/section-2-map.svg) — Figure 2.1 — Schema, client queries and Postgres functions.

## Projects and tasks

A migration with a foreign key to auth.users.

```sql
create table public.projects (
  id bigint generated always as identity primary key,
  owner_id uuid not null references auth.users (id) on delete cascade,
  name text not null,
  created_at timestamptz not null default now()
);

create table public.tasks (
  id bigint generated always as identity primary key,
  project_id bigint not null references public.projects (id) on delete cascade,
  title text not null,
  done boolean not null default false
);

alter table public.projects enable row level security;
alter table public.tasks enable row level security;
```

## Blueprints, not sketches on a napkin

Clicking in the dashboard is a sketch; a migration file is the blueprint every builder (environment) follows.

**Quiz:** Why define foreign keys between tables in Supabase?

- [ ] They are required for every column
- [x] They enforce integrity and let the API do nested selects
- [ ] They disable RLS automatically
- [ ] They make tables public

*Answer:* They enforce integrity and let the API do nested selects. PostgREST discovers relationships from foreign keys.