From User Stories to Database Schema: A Step-by-Step Method (With AI Shortcuts)

Every user story you write already contains your database schema. "As a user, I want to log a habit each day so I can see my streak" names three tables — users, habits, and check-ins — plus one relationship and one derived value you should never store. Most AI-built apps skip this translation step and pay for it when v2 requires a data migration nobody planned for.

This post walks through the full manual method: stories → nouns → entities → relationships → keys and constraints → SQL, designing one product end-to-end — Streakly, a habit-tracking SaaS — from eight user stories to runnable PostgreSQL DDL. Then: the six schema mistakes in almost every AI-generated codebase, and what to verify when you use a database schema generator instead.

Why Schema-Last Kills AI-Built Apps

A database schema is the set of tables, columns, relationships, and rules that define how an application stores data. When builders using AI tools like Lovable, Bolt, or Cursor prompt their way to a working app without designing the schema first, the AI improvises one table at a time — each prompt adds columns wherever convenient. The result mirrors the order of your prompts, not the shape of your domain. Code is cheap to regenerate; data is not. A bad component gets rewritten in one prompt, but a bad schema requires migrating live user data — which is where most vibe-coded apps stall at v2. The fix: treat the schema as the one artifact you design deliberately before generating anything, because it's the only artifact that gets harder to change every day your app is live.

The failure pattern in practice: you prompt "add a habits feature," and the AI puts a habits text column on the users table. You prompt "let users share habits with friends," and it bolts a shared_with array onto habits. Six prompts later you can't query "who shares this habit" and a JSON blob sits where your streak history should be. None of these are code bugs — they're data model bugs, and they compound silently until a feature like "leaderboard of longest streaks" turns out to be unbuildable without a migration.

We covered the broader architecture-first workflow in how to plan your app's architecture from a single prompt. The schema is the highest-leverage slice of that plan, and the one you can get right in an hour.

The Method: Stories → Nouns → Entities

The fastest reliable way to design a database from requirements is noun extraction: read every user story, underline each noun, and sort the nouns into three buckets — entities, attributes, and noise. An entity is a thing your system must track independently, with its own identity and lifecycle; it becomes a table. An attribute is a property of an entity — it becomes a column. Noise is everything else ("dashboard" is a screen, not data). The technique dates back to Abbott's textual analysis method from the 1980s and works because user stories are written in domain language: "As a user, I want to create a habit with a reminder time" hands you two tables and a column in eleven words. If a noun appears in multiple stories and other nouns point at it, it's an entity.

Here are Streakly's eight user stories:

1. As a user, I want to sign up with email so I can save my data.
2. As a user, I want to create a habit with a name and schedule (daily or specific weekdays).
3. As a user, I want to check in on a habit each day so my streak grows.
4. As a user, I want to see my current and longest streak per habit.
5. As a user, I want to add an optional note to a check-in.
6. As a user, I want to join a group and see other members' streaks.
7. As a user, I want a reminder at a chosen time per habit.
8. As a user, I want to archive a habit without losing its history.

Now sort the nouns:

Noun	Verdict	Why
user	Entity	Independent identity, referenced everywhere
habit	Entity	Own lifecycle (created, archived)
check-in	Entity	An event you count, one per habit per day
group	Entity	Users join it; exists independently
member	Relationship	A user in a group — junction table
schedule	Attribute(s)	Columns on habit
streak	Derived	Computed from check-ins — never stored
note	Attribute	Column on check-in
reminder	Attribute	Column on habit (a time, not a thing)
archive	Attribute	A state — archived_at timestamp

Notice two judgment calls. "Streak" is a noun in three stories but it's derived data — always computable from check-ins, so storing it creates a value that can drift out of sync. And "member" isn't an entity; it's a many-to-many relationship wearing a noun costume. Both are exactly where AI generators go wrong.

If your stories are vague, noun extraction fails upstream — garbage stories, garbage nouns. Our guide to writing user stories with the INVEST framework covers getting the inputs right.

Relationships and Cardinality

Once you know your entities, the next question for each pair is cardinality: how many of A can relate to how many of B? There are only three answers. One-to-many (1:N) — a user has many habits, each habit belongs to one user — is modeled with a foreign key on the "many" side. Many-to-many (M:N) — users join many groups, groups contain many users — cannot be modeled with a single foreign key and requires a junction table: a third table holding a pair of foreign keys, one row per pairing. One-to-one (1:1) is rare and usually a sign two tables should merge. To find every relationship, ask the question for each entity pair in both directions; the two answers together give you the cardinality.

For Streakly:

• user → habit: one user, many habits. FK user_id on habits. 1:N
• habit → check-in: one habit, many check-ins. FK habit_id on check_ins. 1:N
• user ↔ group: many users per group, many groups per user. M:N → junction table group_members
• user → check-in: reachable through habit, but for a streak app you'll constantly query "all my check-ins across habits" — a second FK here is a defensible read-optimization.

Sketching this as an entity-relationship diagram (ERD) — boxes for entities, lines annotated with cardinality — takes five minutes and catches missing junction tables before they become missing features:

users 1──N habits 1──N check_ins
  │
  └──N group_members N──1 groups

Keys, Constraints, and Indexes

Constraints are rules the database enforces so bad data physically cannot exist — the cheapest bug prevention you will ever ship. Every table needs a primary key (a column that uniquely identifies each row — use UUIDs or bigint identity). Every relationship needs a foreign key with an explicit ON DELETE behavior, so orphaned rows can't accumulate. Unique constraints encode business rules: "one check-in per habit per day" is a single UNIQUE (habit_id, checked_on) line that eliminates a class of duplicate-data bugs application code will eventually miss under concurrent requests. NOT NULL and CHECK do the same for required fields and valid ranges. Indexes, by contrast, enforce nothing — they make queries fast, and you add them to match actual read patterns: foreign keys you join on, columns you filter or sort by.

The decision AI generators consistently botch is ON DELETE. The PostgreSQL foreign key documentation offers CASCADE (delete children with the parent), RESTRICT (block the delete), and SET NULL. Deleting a habit should cascade to its check-ins; deleting a user with billing history shouldn't cascade at all — one reason soft deletes show up in story #8. For indexes: cover every foreign key plus your two or three most frequent queries, and skip the rest until you have query stats — the PostgreSQL index docs are explicit that every index slows down writes.

The Full Streakly Schema in PostgreSQL

Here is the complete worked example: eight user stories reduced to five tables, four relationships, and roughly sixty lines of DDL, with every constraint annotated back to the user story that demanded it. Notice what's absent as much as what's present: no current_streak column (derived from check-ins), no is_deleted boolean paired with actual deletions (archival is a timestamp), no free-text schedule string (a typed column plus an integer array of weekdays instead). A schema this size covers a real SaaS MVP. If a story of your own won't map onto a table, column, or constraint, it's either out of scope for v1 or you've found a missing entity — both useful discoveries to make before writing application code.

-- Story 1: sign up, save data
create table users (
  id            uuid primary key default gen_random_uuid(),
  email         text not null unique,
  display_name  text not null,
  created_at    timestamptz not null default now()
);

-- Stories 2, 7, 8: habits with schedule, reminder, archive
create table habits (
  id            uuid primary key default gen_random_uuid(),
  user_id       uuid not null references users (id) on delete cascade,
  name          text not null check (char_length(name) between 1 and 80),
  schedule_type text not null default 'daily'
                check (schedule_type in ('daily', 'weekdays')),
  schedule_days int[] check (schedule_days <@ array[0,1,2,3,4,5,6]),
  reminder_at   time,                    -- story 7: optional reminder
  archived_at   timestamptz,             -- story 8: soft archive, null = active
  created_at    timestamptz not null default now()
);

create index habits_user_id_idx on habits (user_id)
  where archived_at is null;             -- partial index: most queries want active habits

-- Stories 3, 4, 5: check-ins drive streaks; notes optional
create table check_ins (
  id          uuid primary key default gen_random_uuid(),
  habit_id    uuid not null references habits (id) on delete cascade,
  user_id     uuid not null references users (id) on delete cascade,
  checked_on  date not null,
  note        text check (char_length(note) <= 500),
  created_at  timestamptz not null default now(),
  unique (habit_id, checked_on)          -- story 3: one check-in per habit per day
);

create index check_ins_user_date_idx on check_ins (user_id, checked_on desc);

-- Story 6: groups and membership (M:N junction table)
create table groups (
  id          uuid primary key default gen_random_uuid(),
  name        text not null check (char_length(name) between 1 and 60),
  created_by  uuid not null references users (id) on delete restrict,
  created_at  timestamptz not null default now()
);

create table group_members (
  group_id   uuid not null references groups (id) on delete cascade,
  user_id    uuid not null references users (id) on delete cascade,
  joined_at  timestamptz not null default now(),
  primary key (group_id, user_id)        -- a user joins a group once
);

create index group_members_user_id_idx on group_members (user_id);

And the streak — the noun we refused to store — becomes a query:

-- Story 4: current streak = consecutive days ending today
with gaps as (
  select checked_on,
         checked_on - (row_number() over (order by checked_on))::int as grp
  from check_ins
  where habit_id = $1
)
select count(*) as current_streak
from gaps
where grp = (select grp from gaps order by checked_on desc limit 1)
  and (select max(checked_on) from gaps) >= current_date - 1;

If the query gets slow at scale, cache it in the application layer or a materialized view — the check-ins remain the source of truth.

Total design time by hand: about an hour. (VibeMap's free Database Schema Generator does the same stories-to-DDL translation in about thirty seconds — but do it manually at least once so you can audit what any tool gives you.)

Six Schema Mistakes That Sink v2

Certain schema mistakes appear so reliably in AI-generated and first-time schemas that you can review for them as a checklist. Each is invisible at launch — the app works — and expensive at v2, when a feature request collides with the flaw and forces a live-data migration. The six most common: missing junction tables (a many-to-many crammed into an array column), stored derived data (a counter that drifts from the truth), hard deletes without history, premature optimization, enum abuse (free-text strings where a constraint should be), and multi-value columns (comma-separated lists that can't be joined or indexed). Every one has a mechanical fix, ten times cheaper applied before launch than after. Run this list against any schema — hand-written or generated — before the first migration.

1. The missing junction table. habits.shared_with uuid[] works until you need "all habits shared with me" — arrays can't be foreign-keyed or reverse-queried efficiently. Fix: any M:N gets a junction table, always.

2. Storing derived data. habits.current_streak int drifts the first time a check-in is deleted or backfilled. Fix: store events, compute aggregates; only cache with an explicit invalidation story.

3. No soft deletes. DELETE FROM habits cascades away months of history the user thought was "archived." Fix: archived_at timestamptz; reserve real DELETE for privacy requests.

4. Premature optimization. Sharding keys, six speculative indexes, a Redis cache column — at zero users. Fix: normalize first, index FKs plus known queries, denormalize only after measuring.

5. Enum abuse. Unconstrained status text accumulates 'active', 'Active', and 'actve' within a month. Fix: a CHECK (status in (...)) constraint — trivially amendable in a migration, unlike PostgreSQL's native enum types.

6. Multi-value columns. tags text containing "health,morning,fitness" violates first normal form — normalization being the discipline of one fact, one place — and can't be filtered without LIKE scans. Fix: a tags table and a junction, or a GIN-indexed array.

For the theory underneath these rules, the classic reference is Database Design for Mere Mortals (Hernandez).

Doing It With AI: What to Check

AI schema generation has changed the economics of this work: what took an afternoon now takes a minute, and a good database schema generator applies noun extraction more consistently than a tired human. But generated schemas fail in predictable places, so the skill shifts from writing DDL to auditing it. The five-point review: (1) every M:N relationship has a junction table, not an array; (2) no derived values stored as columns — search for anything named count, total, or streak; (3) every foreign key has a deliberate ON DELETE clause; (4) uniqueness rules from your stories appear as UNIQUE constraints, not comments; (5) anything user-facing with history uses soft deletes. Ten minutes with this checklist catches essentially every failure mode from the previous section — the difference between a schema you generated and a schema you own.

Prompt quality matters more than tool choice: vague requirements produce invented entities; eight INVEST-quality stories like Streakly's make the noun extraction nearly deterministic. For the full pipeline — prompt to spec to schema to build plan — see how to generate a complete app spec from a single prompt.

Try it on your own stories: the Database Schema Generator is free, no signup — paste your user stories, get a visual ERD plus copy-paste PostgreSQL DDL, then run the five-point audit above. Starting even earlier than the schema? The free Spec Kit covers the whole planning stack.

FAQ

How do I design a database schema from user stories?

Extract the nouns from every story and sort them into entities (tables), attributes (columns), and noise. Define the cardinality between each entity pair — one-to-many gets a foreign key, many-to-many gets a junction table. Then encode business rules as constraints: unique, not-null, check, and foreign keys with explicit delete behavior.

What is an entity in database design?

An entity is anything your system must track independently — it has its own identity and lifecycle, and other data points at it. Users, habits, and orders are entities; a "reminder time" is just an attribute. The practical test: if a noun appears across multiple stories and can exist on its own, it becomes a table.

What is cardinality?

Cardinality describes how many rows of one table can relate to rows of another: one-to-one, one-to-many, or many-to-many. You determine it by asking the relationship question in both directions — "can a user have many habits? can a habit belong to many users?" One-to-many is modeled with a foreign key; many-to-many requires a junction table.

When do I need a junction table?

Whenever a relationship is many-to-many: users and groups, posts and tags, students and courses. A junction table holds one row per pairing, with foreign keys to both sides and a composite primary key to prevent duplicates. Tempted to store an array of IDs in a column instead? That's the signal you need one.

Should I store calculated values like streaks or totals?

No — not in v1. Derived data (anything computable from other rows) drifts out of sync the moment an insert, delete, or backfill misses the update path. Store the underlying events and compute aggregates in queries. Cache a derived value only once it's measurably slow, and treat the cache as disposable, never the source of truth.

Can AI generate a database schema from requirements?

Yes, and current tools do it well when the input stories are specific. A database schema generator applies noun extraction and cardinality analysis automatically, producing an ERD and SQL in seconds. The catch: predictable failure modes — stored derived values, missing junction tables, careless delete cascades — so always audit generated output before running it.

What's the difference between an ERD and a schema?

An ERD (entity-relationship diagram) is the visual design — boxes for entities, annotated lines for relationships and cardinality. The schema is the executable implementation: CREATE TABLE statements with columns, types, keys, and constraints. Sketch the ERD to reason about structure, then translate to DDL. Good tools output both from the same source.

How many tables should an MVP have?

Most single-purpose SaaS MVPs land between four and eight tables — Streakly's eight stories produced five. Fewer than three usually means entities are crammed into JSON columns; more than twelve means you're building v3 features into v1. If a table has no user story pointing at it, cut it.