How to Design Your First Database (Without a Computer Science Degree)

AI tools will happily invent a database for your app. It'll usually work for the demo. But the shape of your data is one of the few decisions that's genuinely hard to change later — every page, query, and feature is built on it. Fifteen minutes of planning now saves weeks of awkward workarounds.

This guide uses a SQL database like PostgreSQL. (SQL vs NoSQL.)

Step 1: list the nouns

Describe your app in a few sentences:

Users keep track of their plants. Each plant has a species and a watering schedule. Users log each time they water a plant. Users can share plants with other users.

The important nouns become your tables: users, plants, species, waterings. ("Schedule" is a property of a plant, not a thing of its own — at least for now.)

Step 2: give each table its columns

For each table, list what you need to know about one of them:

users

column	type	notes
id	integer or UUID	unique identifier
email	text	unique
name	text
created_at	timestamp	when they signed up

plants

column	type	notes
id	integer or UUID
owner_id	→ users.id	who owns it
species_id	→ species.id
nickname	text	"Fernando"
water_every_days	integer	3
created_at	timestamp

Rules of thumb:

Every table gets an id — a unique identifier that never changes. Don't use an email or name as the ID; those change.
Pick real types. Numbers as integers or decimals, dates as timestamps, true/false as booleans. Storing everything as text causes sorting bugs ("10" before "9") and bad data.
Money in a decimal type or as whole cents — never a floating-point number, which can't represent 0.10 exactly.
Add created_at to most tables. You'll want it.
One value per column. Not tags: "fern, indoor, shade" — that's a sign of a missing table.

Step 3: connect tables with foreign keys

plants.owner_id holds the id of a user. A column that points to another table's row is a foreign key. Declaring it as one tells the database to enforce it: you can't create a plant for a user that doesn't exist.

This is the one-to-many relationship — one user has many plants, each plant has one owner — and it's the most common shape in any app. The "many" side holds the foreign key.

Step 4: many-to-many needs a table in the middle

"Users can share plants with other users" is different: one plant can be shared with many users, and one user can have many plants shared with them. That's many-to-many, and it needs a join table:

plant_shares

column	type
plant_id	→ plants.id
user_id	→ users.id
permission	text ("view" or "edit")

Each row is one share. Join tables often carry useful extra data, like permission here.

The whole picture:

users ──< plants >── species
  │          │
  └──< plant_shares >──┘
             │
plants ──< waterings

(──< reads as "one to many.")

Step 5: think about the questions you'll ask

Go through your app's main screens and ask what each needs:

"Show my plants, with the ones due for water first." → needs plants.owner_id, water_every_days, and the latest waterings date.
"Show plants shared with me." → plant_shares by user_id.

If a question is hard to answer from your tables, adjust now. And the columns you filter or sort by most will likely need indexes later. (Database Indexes.)

Mistakes that are painful to fix later

Storing lists in a single column (comma-separated values or JSON arrays) for things you'll search or join on. Use a separate table.
Duplicating data: copying the user's name into every plant row. When the name changes, you'll have to update everywhere. Store it once and refer to it.
No foreign keys, leaving "orphan" rows pointing at deleted users.
Deleting when you should hide: if you might need the data later (orders, invoices), consider marking rows as deleted instead of removing them. (Soft Deletes and Audit Logs.)
Forgetting multi-user from the start: if you'll have teams or organisations, add them now. Retrofitting team_id onto every table later is hard. (Multi-Tenant Postgres Patterns.)

Handing it to your AI tool

Now give Claude Code or your builder the plan, not just the idea:

Create the database schema with these tables: users, plants, species, waterings, plant_shares. [Paste your tables.] Use foreign keys with the relationships shown. Generate it as a migration.

You'll get a much better result than "make a database for a plant app" — and you'll understand what it built. (What Are Database Migrations?.)

EasySpawn gives each project a managed Postgres database from the start, so the schema you design is created by real migrations on a real database, not an in-memory stand-in. See how it works or join the waitlist.