Database Model Creation Tips

Designing a Clean, Scalable Database Model: A Practical Guide

A well-structured database model is the foundation of any robust app. Whether you’re building a simple prototype or a multi-tenant production service, a clear data model saves time, reduces bugs, and makes future improvements far easier. Below is a compact, practical guide that walks through the essential steps, best practices, and trade-offs when creating a database model — plus an example schema you can adapt.

1. Clarify the Purpose

Start by listing what the app must store and who interacts with it. Identify the core nouns (Users, Organizations, Resources) and the relationships between them. This early clarity guides table design and avoids rework.

2. Core Entities

Think in terms of tables for each major noun:

• Users — accounts and identity.
• Organizations / Teams — group ownership or tenant.
• Resources — the app’s primary objects (photos, jobs, recipes).
• Supporting tables — tags, notes, tasks, activity logs.

3. Relationships

Map relationships explicitly:

• One-to-Many: an organization has many users.
• Many-to-Many: use join tables (e.g., photo_tags).

4. Columns & Data Types

Use consistent naming and sensible types. Typical choices:

• IDs: UUID or BIGINT (UUID recommended for distributed apps).
• Strings: TEXT / VARCHAR for names and descriptions.
• Flexible data: JSONB for metadata or unpredictable fields.
• Timestamps: TIMESTAMP WITH TIME ZONE for created_at and updated_at.

5. Multi-Tenancy

If the app supports multiple organizations, add org_id to tenant-scoped tables and enforce access via row-level security (RLS) or middleware filters.

6. Normalization vs Performance

Start normalized to avoid duplication. Only denormalize later for performance-critical paths (e.g., materialized counts, precomputed status fields).

7. Security & Access Control

Implement RLS or role-based access early. Keep sensitive columns separated or encrypted and add audit logs for important actions.

8. Scalability & Future-Proofing

Use UUIDs, include created_at/updated_at, and consider soft deletes via deleted_at. Plan for logging, backups, and lifecycle policies for large objects.

Quick Example Schema (starter)

-- Users
CREATE TABLE users (
  id UUID PRIMARY KEY,
  org_id UUID NOT NULL,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  role TEXT, -- e.g., admin, member
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Organizations
CREATE TABLE organizations (
  id UUID PRIMARY KEY,
  name TEXT,
  settings JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Photos (example resource)
CREATE TABLE photos (
  id UUID PRIMARY KEY,
  org_id UUID NOT NULL,
  uploader_id UUID REFERENCES users(id),
  storage_path TEXT NOT NULL,
  exif JSONB,
  gps GEOGRAPHY, -- if using PostGIS
  width INT,
  height INT,
  status TEXT, -- uploaded / processed / ready
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Many-to-many: photo_tags
CREATE TABLE tags (
  id UUID PRIMARY KEY,
  org_id UUID NOT NULL,
  name TEXT
);

CREATE TABLE photo_tags (
  photo_id UUID REFERENCES photos(id),
  tag_id UUID REFERENCES tags(id),
  PRIMARY KEY (photo_id, tag_id)
);

-- Activity log for audits
CREATE TABLE activity_log (
  id UUID PRIMARY KEY,
  org_id UUID,
  user_id UUID,
  action TEXT,
  meta JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);
    

Key Pointers — TL;DR

• Sketch an ERD first — entities, keys, and relationships.
• Add org_id early if multi-tenant.
• Prefer UUIDs, timestamps, and JSONB for flexible fields.
• Implement access control (RLS) and logging from the start.
• Normalize first, then denormalize when performance needs it.

Want this adapted into a full ER diagram, or a tailored schema for your photo/job site app (with RLS examples and Postgres-specific DDL)? I can draft that next — just say which tables you want prioritized.

How to Prompt Cursor to Generate Your First Database Schema

When you’re building a new app, one of the first technical challenges is designing the database. Tools like Cursor can help you quickly draft a version-one schema—but only if you know how to ask for it. The key is writing a clear, structured prompt that explains your app’s purpose, core entities, and requirements. Below, I’ll walk you through a ready-to-use example prompt you can copy, paste, and adapt for your own project.

The Prompt

You are helping me design the first version of my database schema for a web app.  
The app allows companies to manage job site photos for contractors doing inspections or estimates.  

Requirements:  
- Multi-tenant: each company/organization should only see its own data.  
- Users can belong to an organization.  
- Organizations have properties (e.g., addresses).  
- Jobs happen at properties.  
- Each job can have albums, which contain photos.  
- Photos can have tags, notes, and tasks linked to them.  
- Keep an activity log of important events (uploads, comments, etc.).  
- Add `created_at`, `updated_at`, and `deleted_at` (soft delete) to all major tables.  
- Use UUIDs for primary keys.  
- Optimize for Postgres + Supabase with row-level security in mind.  

Please generate a Postgres SQL schema with `CREATE TABLE` statements that cover:  
- organizations  
- users  
- org_members (to link users to organizations with roles)  
- properties  
- jobs  
- albums  
- photos  
- photo_tags (many-to-many)  
- notes  
- tasks  
- activity_log  

Make sure to include foreign keys and indexes where useful.  
  

Why This Works

• Context is clear — You explain what the app does in plain language.
• Entities are listed — Cursor knows exactly what tables to create.
• Relationships are explicit — One-to-many and many-to-many are spelled out.
• Technical details included — UUIDs, soft deletes, and RLS considerations.

With this structure, Cursor will generate a schema that’s not just usable but also close to production-ready. From there, you can refine it, test it, and grow it as your app evolves.