You describe the app. The AI tool builds it. Two weeks later, you want to add a feature: users should be able to soft-delete their projects so they can recover one if they tap the wrong thing.
You open the file. There's no deleted_at column on the projects table. There's no easy way to add one without rewriting every query that fetches projects. The AI is happy to "fix" this, but it touches 14 files and breaks two unrelated things in the process.
This is the cost of skipping database design when you're not a developer. The AI writes whatever schema seems convenient at prompt one, and you find out the limits at prompt forty. Database design for non-developers is one of the highest-payoff things you can sketch before you start prompting, and it doesn't require knowing SQL. This post walks through what to think about, with concrete examples.
What a database actually is, in three sentences
A database is a set of tables. A table is like a spreadsheet: columns describe the fields, rows are the records. Tables can reference each other (a "project" row knows which "user" row owns it), and that's how the app stitches things together.
That's the whole mental model you need to start. Forget normalisation theory and joins for now. If you can describe a spreadsheet, you can describe a table.
The pieces:
- Column: a single field.
email,created_at,title. - Row: one record. One user, one project, one logged-in session.
- Primary key: the unique ID for a row. Usually called
id, often a UUID (a long random string that's unique forever). - Foreign key: a column on table A that points at a row in table B. The
user_idcolumn on aprojectstable is a foreign key to theuserstable.
If you understand those four words, you understand 90% of database design.
Sketch your tables before the AI does
Before you open Lovable, v0, or Cursor, sit with a piece of paper or a Notion doc and answer one question: what are the nouns in your app?
A habit tracker has: users, habits, check-ins.
A book-tracking app has: users, books, reading sessions, tags.
A freelance invoicing tool has: users, clients, projects, invoices, line items.
Each noun is probably a table. That's the rough starting list. Now ask, for each table, what does a row need to remember?
A user row needs: email, password hash (you don't store passwords directly, ever), name, created_at, last_login_at.
A habit row needs: user_id (which user owns it), name, target_frequency, color, created_at.
A check-in row needs: habit_id (which habit it logs), checked_at, note.
This is the entire design sketch. Six lines of writing, no SQL. If you hand this to your AI tool as part of your project's spec, the schema it generates will be coherent rather than improvised.
The fields you'll wish you'd added on day one
There are a handful of columns that almost every table should have, and AI tools skip half of them by default. Add them by name in the spec, and you save yourself a refactor later.
id: the primary key. Always there. The AI gets this one right.
created_at: the timestamp the row was created. The AI sometimes adds this, sometimes doesn't. Always ask for it. You will want it for analytics, for sorting, for everything.
updated_at: the timestamp the row was last changed. Trivially useful. Add it.
deleted_at: nullable timestamp. When the user "deletes" a row, set this instead of actually deleting. Now you can show "recover from trash" features, and you can audit what happened later. This is called soft delete. Add it everywhere users can delete things.
user_id: any row that belongs to a user needs this. The AI is usually good about this, but check; sometimes it gets lazy and ties things to sessions or other proxies.
status: when a thing has states (a project is draft, active, archived), put it in a status column. Don't use three boolean columns (is_draft, is_active, is_archived) because the moment a row gets into two states at once, debugging is misery.
That's six columns. Apply them generously. Storage is cheap. Refactoring schemas later is expensive.
One-to-many and many-to-many: the only two relationships you need to understand
Tables relate to each other in two main shapes.
One-to-many is the common case. One user has many projects. One project has many tasks. The "many" side carries the foreign key: the projects table has a user_id, not the users table holding a list of project IDs.
This is so common it almost doesn't need explanation. Whenever you say "X has many Y," X is one and Y is many, and Y carries the foreign key.
Many-to-many is when both sides have many of the other. A book has many tags, and a tag applies to many books. The trick: you create a third table in the middle. book_tags has book_id and tag_id. Each row is one link.
This is the table the AI most often skips or implements wrong. If your app has tagging, sharing, multiple-owners, or any kind of relationship where both sides can have multiples, name the join table out loud in the spec. "Books and tags are many-to-many through a book_tags table." The AI will then get it right.
The rookie mistakes that bite later
Some patterns look fine on day one and ruin you by day thirty. The AI loves all of them.
JSON columns for everything structured. The AI will store user settings as one big JSON blob in a settings column. Quick to build, painful to query. "Show me all users who turned on notifications" becomes a slow scan instead of an indexed lookup. Use JSON columns only for genuinely free-form data, not for fields you'll filter or sort on.
Hard deletes. The user clicks delete, the row is gone. Forever. Now a confused user emails you asking "where did my project go," and you have nothing. Soft delete (the deleted_at column above) saves you here.
No indexes on foreign keys. An index is a database structure that makes lookups fast. Foreign keys especially need them, because every "find all projects for user X" query uses one. The AI sometimes forgets these. Ask for indexes on every foreign key, every column you'll filter or sort on, and every unique constraint.
Booleans for things that have a future third option. is_archived works fine until the day you add pending review and you're stuck. A status column with a string value is more flexible. Same for is_paid: a subscription_status column with trialing, active, past_due, canceled ages better.
Storing display strings. "The user's plan is 'Pro Plan ($29/mo)'." No. Store an ID or enum value; render the display string in the UI. Otherwise renaming the plan is a database migration.
When the spec actually pays off
The thing nobody mentions about database design for non-developers: the AI is a brilliant junior developer who's never seen your app before. Every prompt is a fresh start. If the schema is implicit, the AI guesses, and the guesses drift.
If the schema is in writing, in the project spec, the AI builds against a single source of truth. You don't have to explain that "projects soft-delete" on prompt twenty, because it was true on prompt one.
This is one of the things Draftlytic asks about during project creation, gently. You don't write SQL; you describe the nouns and their relationships in plain language. The spec captures the data model so when you hand it to Cursor, Lovable, or Claude Code, the schema doesn't have to be rediscovered every session. Sketching a data model before you prompt goes deeper on the pre-prompt sketching workflow.
You don't need to be a developer to design a sensible database. You need to be able to name the things in your app, name the columns each thing should remember, and know that one-to-many is normal and many-to-many needs a join table. That's the whole job for the first version. Everything else is an optimisation you can do later, with help, when you actually have users.