Part 4: Database Design for Non-Developers – From Chaos to Clean Schema

🗄️ Your Database Is Your Business

Here's something most founders don't realize:

A poorly designed database is like building a house on sand.

You can fix bugs in code. You can redesign the UI. But changing your database structure once you have thousands of users? That's surgery while the patient is awake.

In Part 3, we covered performance. Now let's make sure your data foundation is solid.

---

🧱 Database Basics (2-Minute Crash Course)

What's a Table?

Think of it as a spreadsheet. Each row is a record, each column is a field.

users table:
┌────┬──────────────────┬─────────────┬────────────┐
│ id │ email            │ name        │ created_at │
├────┼──────────────────┼─────────────┼────────────┤
│ 1  │ alice@gmail.com  │ Alice       │ 2024-01-01 │
│ 2  │ bob@gmail.com    │ Bob         │ 2024-01-02 │
└────┴──────────────────┴─────────────┴────────────┘

What's a Relationship?

Tables connect to each other. A user HAS MANY posts. A post BELONGS TO a user.

posts table:
┌────┬───────────┬────────────────────┐
│ id │ user_id   │ title              │
├────┼───────────┼────────────────────┤
│ 1  │ 1         │ "Hello World"      │  ← Alice's post
│ 2  │ 1         │ "Second Post"      │  ← Alice's post
│ 3  │ 2         │ "Bob's First"      │  ← Bob's post
└────┴───────────┴────────────────────┘

What's a Migration?

A version-controlled change to your database structure. Like Git, but for your schema.

---

💀 The 5 Database Mistakes That Will Haunt You

1. Not Using UUIDs for IDs

The Problem: Auto-incrementing integers (1, 2, 3...) expose information and cause headaches.

-- ❌ Sequential IDs
id SERIAL PRIMARY KEY  -- 1, 2, 3, 4...

Why it's bad:

• Users can guess other users' IDs (/users/1, /users/2)
• Merging databases becomes a nightmare
• Competitors can estimate your user count

The Fix:

-- ✅ UUIDs are random and secure
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- a1b2c3d4-e5f6-7890-abcd-ef1234567890

---

2. Storing Repeated Data (No Normalization)

The Problem: Copying the same data across multiple rows.

orders table (❌ WRONG):
┌──────────┬───────────────────┬──────────────────┐
│ order_id │ customer_email    │ customer_address │
├──────────┼───────────────────┼──────────────────┤
│ 1        │ alice@gmail.com   │ 123 Main St      │
│ 2        │ alice@gmail.com   │ 123 Main St      │  ← Duplicated!
│ 3        │ alice@gmail.com   │ 123 Main St      │  ← Duplicated!
└──────────┴───────────────────┴──────────────────┘

Why it's bad:

• Alice changes her address → You must update 100 rows
• Miss one? Now you have inconsistent data
• Wastes storage space

The Fix:

customers table:
┌─────┬───────────────────┬──────────────────┐
│ id  │ email             │ address          │
├─────┼───────────────────┼──────────────────┤
│ 1   │ alice@gmail.com   │ 123 Main St      │
└─────┴───────────────────┴──────────────────┘

orders table (✅ CORRECT):
┌──────────┬─────────────┐
│ order_id │ customer_id │
├──────────┼─────────────┤
│ 1        │ 1           │
│ 2        │ 1           │  ← References customer, not duplicated
│ 3        │ 1           │
└──────────┴─────────────┘

---

3. No Timestamps

The Problem: Not tracking when records were created or updated.

-- ❌ No timestamps
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT,
  price DECIMAL
);

Why it's bad:

• "When was this product added?" → No idea
• "What changed recently?" → Can't tell
• Debugging issues becomes guesswork

The Fix:

-- ✅ Always add timestamps
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price DECIMAL NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Auto-update updated_at on changes
CREATE TRIGGER update_timestamp
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

---

4. Wrong Data Types

The Problem: Using the wrong type for your data.

-- ❌ Common mistakes
price TEXT,           -- Should be DECIMAL
is_active TEXT,       -- Should be BOOLEAN
quantity TEXT,        -- Should be INTEGER
email VARCHAR(50),    -- Too short!

Why it's bad:

• "10" + "5" = "105" (string concatenation, not math)
• Can't sort numbers properly
• No validation at database level

The Fix:

-- ✅ Correct data types
price DECIMAL(10,2),        -- 12345678.99
is_active BOOLEAN DEFAULT true,
quantity INTEGER CHECK (quantity >= 0),
email VARCHAR(255) UNIQUE,  -- Standard email length

Common Type Reference:

---

5. No Indexes on Frequently Queried Columns

The Problem: Searching without an index is like finding a book in a library with no catalog.

-- ❌ This query scans EVERY row in the table
SELECT * FROM orders WHERE customer_id = 'abc123';

Why it's bad:

• 1,000 orders: Fast (10ms)
• 100,000 orders: Slow (500ms)
• 10,000,000 orders: Timeout (10+ seconds)

The Fix:

-- ✅ Create an index on frequently searched columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Now the same query uses the index: 10ms regardless of table size

What to index:

• Foreign keys (user_id, product_id, etc.)
• Columns used in WHERE clauses
• Columns used in ORDER BY
• Columns used in JOINs

---

🔄 Migrations: Changing Your Database Safely

Once your app is live with real data, you can't just delete tables and start over.

The Wrong Way

1. Open Supabase dashboard
2. Manually add a column
3. Hope you remember what you changed
4. Pray nothing breaks

The Right Way: Migration Files

-- migrations/001_add_subscription_tier.sql
ALTER TABLE users
ADD COLUMN subscription_tier VARCHAR(20) DEFAULT 'free';

-- migrations/002_add_stripe_customer_id.sql
ALTER TABLE users
ADD COLUMN stripe_customer_id VARCHAR(255);

Benefits:

• Version controlled (in Git)
• Reproducible across environments
• Rollback if something goes wrong
• Team members know what changed

Supabase Migration Commands

# Create a new migration
supabase migration new add_subscription_tier

# Apply migrations
supabase db push

# Reset and replay all migrations (DEV ONLY)
supabase db reset

---

📐 Schema Design Template

Here's a starter schema for a typical SaaS:

-- Users (handled by Supabase Auth, extend with profiles)
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  full_name TEXT,
  avatar_url TEXT,
  subscription_tier VARCHAR(20) DEFAULT 'free',
  stripe_customer_id VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Users can only see/edit their own profile
CREATE POLICY "Users manage own profile" ON profiles
  FOR ALL USING (auth.uid() = id);

-- Example: Products or Items
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for faster lookups
CREATE INDEX idx_products_user ON products(user_id);
CREATE INDEX idx_products_published ON products(is_published) WHERE is_published = true;

-- RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users manage own products" ON products
  FOR ALL USING (auth.uid() = user_id);

CREATE POLICY "Anyone can view published products" ON products
  FOR SELECT USING (is_published = true);

---

✅ The Database Checklist

Schema Design

• All IDs are UUIDs
• No duplicated data (normalized)
• Every table has created_at and updated_at
• Correct data types for each column
• Constraints (NOT NULL, CHECK, UNIQUE) where appropriate

Performance

• Indexes on foreign keys
• Indexes on frequently searched columns
• Tested queries with real data volume

Security

• RLS enabled on all tables
• Policies restrict access appropriately
• Sensitive data encrypted or hashed

Operations

• All changes tracked in migration files
• Migrations tested in staging first
• Backup strategy in place

---

---

📚 Series Complete! 🎉

You've finished the Production Launch Guide series.

What's Next?

You now have the knowledge to take your AI prototype to production. But knowledge and execution are different things.

If you want help:

• 🔍 48-Hour Audit: We review everything above and give you a clear action plan
• 🚀 Launch Sprint: We fix the issues and deploy to production in 2 weeks
• 🛡️ Ops Retainer: Ongoing support so you can focus on growing your business

Previous: Part 3: 6 Performance Pitfalls

---

*Ready to launch? Let's talk: hello@shiptheproduct.dev*