Tables and Relationships

How to structure your data with tables that connect and protect data integrity

Last updated: 8/15/2025

Tables are like Excel sheets with superpowers - they enforce structure, connect to other tables and guarantee data quality.

Tables: Sheets with Structure

Unlike spreadsheets where you can put anything anywhere, database tables have rules:

Columns define data types (text, numbers, dates, booleans)
Rows represent individual records (one customer, one order, one product)
Constraints protect data quality (required fields, unique values, valid formats)

Basic Table Structure

customers table
┌─────────────┬─────────────────┬─────────────────┬─────────────────┐
│ id (UUID)   │ email (text)    │ name (text)     │ created_at      │
├─────────────┼─────────────────┼─────────────────┼─────────────────┤
│ 550e8400... │ alice@email.com │ Alice Smith     │ 2025-01-15...   │
│ 6ba7b810... │ bob@email.com   │ Bob Johnson     │ 2025-01-14...   │
└─────────────┴─────────────────┴─────────────────┴─────────────────┘

Key Concepts

Primary Keys

Every table needs a unique identifier for each row.

id uuid primary key default gen_random_uuid()

Why this matters: Primary keys let other tables reference specific rows reliably.

Foreign Keys

Pointers that connect rows in different tables.

customer_id uuid references customers(id)

What it does: Ensures orders can only reference customers that actually exist.

Relationships in Action

Here's how tables connect in a simple e-commerce system:

customers                    orders                      order_items
┌──────────┬─────────┐      ┌──────────┬─────────────┐   ┌──────────┬──────────┬────────┐
│ id       │ email   │      │ id       │ customer_id │   │ id       │ order_id │ qty    │
├──────────┼─────────┤      ├──────────┼─────────────┤   ├──────────┼──────────┼────────┤
│ customer1│ alice...│ ←──── │ order1   │ customer1   │   │ item1    │ order1   │ 2      │
│ customer2│ bob...  │      │ order2   │ customer1   │   │ item2    │ order1   │ 1      │
└──────────┴─────────┘      │ order3   │ customer2   │   │ item3    │ order2   │ 3      │
                            └──────────┴─────────────┘   └──────────┴──────────┴────────┘

Reading the relationships:

Alice (customer1) has two orders (order1, order2)
Order1 contains two different items
Each order_item links to exactly one order

Common Relationship Patterns

One-to-Many

One customer can have many orders.

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

-- orders table
create table orders (
  id uuid primary key default gen_random_uuid(),
  customer_id uuid references customers(id) not null,
  total decimal(10,2),
  created_at timestamptz default now()
);

Many-to-Many (with Junction Table)

Products can be in many orders, orders can contain many products.

-- products table
create table products (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  price decimal(10,2)
);

-- junction table connects orders and products
create table order_items (
  id uuid primary key default gen_random_uuid(),
  order_id uuid references orders(id) not null,
  product_id uuid references products(id) not null,
  quantity integer not null,
  price_at_time decimal(10,2) -- price when ordered
);

Data Integrity Benefits

Referential Integrity

Foreign keys prevent orphaned data.

-- This will fail if customer doesn't exist
insert into orders (customer_id, total) 
values ('nonexistent-id', 100.00);

Constraints Prevent Bad Data

-- Ensure email addresses are unique
email text unique not null

-- Ensure quantities are positive
quantity integer check (quantity > 0)

-- Ensure required fields are filled
name text not null

Designing Your Schema

Start with entities: What "things" does your app track?

Users, projects, tasks
Customers, orders, products
Posts, comments, likes

Identify relationships: How do these things connect?

Users create projects
Projects contain tasks
Tasks are assigned to users

Add constraints: What rules must the data follow?

Email addresses must be unique
Tasks must belong to a project
Prices must be positive

Schema Evolution

Tables can change as your app grows:

-- Add a new column
alter table customers add column phone text;

-- Add an index for faster searches
create index idx_customers_email on customers(email);

-- Add a new table
create table customer_addresses (
  id uuid primary key default gen_random_uuid(),
  customer_id uuid references customers(id) not null,
  street_address text not null,
  city text not null,
  is_primary boolean default false
);

Well-designed relationships make your data reliable, your queries powerful and your app scalable. Start simple, but think about how your tables will connect from day one.

Learn