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, one relationship, and one value you should never store. Most AI-built apps skip that translation step entirely and pay for it at v2, when a feature request needs a migration nobody planned.

This post walks the full manual method, stories to nouns to entities to relationships to constraints to SQL, by designing one product end to end. Streakly, a habit-tracking SaaS, from eight user stories to runnable PostgreSQL. Then the six schema mistakes that show up in almost every AI-generated codebase, and what to check when you use a generator instead.

Why schema-last kills AI-built apps

A database schema is the set of tables, columns, relationships, and rules defining how your app stores data.

When you prompt your way to a working app with Lovable, Bolt, or Cursor without designing the schema first, the AI improvises one table at a time. Each prompt adds columns wherever is convenient. What you end up with mirrors the order of your prompts rather than the shape of your domain.

Here is the asymmetry that makes this expensive. Code is cheap to regenerate. A bad component gets rewritten in one prompt. A bad schema needs a migration against live user data, which is where most vibe-coded apps stall.

The failure pattern is easy to recognise once you have seen it. 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 cannot query "who shares this habit," and a JSON blob is sitting where your streak history should be.

None of these are code bugs. They are data model bugs, and they compound silently until something like "leaderboard of longest streaks" turns out to be unbuildable without a migration.

Treat the schema as the one artifact you design deliberately before generating anything else. It is the only one that gets harder to change every day your app is live.

We covered the broader architecture-first workflow in planning 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 to nouns to entities

The fastest reliable way to design a database from requirements is noun extraction. Read every story, underline each noun, sort the nouns into three buckets.

An entity is something your system tracks 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, and "dashboard" is noise because it is a screen, not data.

The technique goes back to Abbott's textual analysis work in the 1980s, and it 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.

The rule of thumb: if a noun appears in multiple stories and other nouns point at it, it is an entity.

Here are Streakly's eight 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, so a 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 rather than a thing
archive	Attribute	A state, so an archived_at timestamp

Two judgment calls in there are worth dwelling on.

"Streak" is a noun in three separate stories, but it is derived data. It is always computable from check-ins, so storing it creates a value that can drift out of sync with the truth. And "member" is not an entity at all. It is a many-to-many relationship wearing a noun costume.

Both are exactly where AI generators go wrong, which is not a coincidence: they are the two cases where the grammar of the story misleads you.

If your stories are vague, noun extraction fails upstream. Garbage stories, garbage nouns. Writing user stories with INVEST covers getting the inputs right.

Relationships and cardinality

Once you know your entities, the 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. A user has many habits, each habit belongs to one user. Model it with a foreign key on the "many" side.

Many-to-many. Users join many groups, groups contain many users. This cannot be modelled with a single foreign key. It needs a junction table, a third table holding a pair of foreign keys with one row per pairing.

One-to-one. Rare, and usually a sign that two tables should be one.

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 to habit: one user, many habits. FK user_id on habits. 1:N
• habit to check-in: one habit, many check-ins. FK habit_id on check_ins. 1:N
• user to group: many each way. M:N, so a group_members junction table
• user to check-in: reachable through habit, but a streak app constantly queries "all my check-ins across habits," so a second FK here is a defensible read optimisation

Sketching this as an entity-relationship diagram 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 that bad data physically cannot exist. They are the cheapest bug prevention you will ever ship.

Every table needs a primary key, a column uniquely identifying each row. Use UUIDs or bigint identity. Every relationship needs a foreign key with an explicit ON DELETE behaviour, so orphaned rows cannot accumulate.

Unique constraints encode business rules directly. "One check-in per habit per day" is a single UNIQUE (habit_id, checked_on) line, and it eliminates a class of duplicate-data bugs that application code will eventually miss under concurrent requests. NOT NULL and CHECK do the same job for required fields and valid ranges.

Indexes are different: they enforce nothing. They make queries fast, and you add them to match real read patterns. Foreign keys you join on, columns you filter or sort by.

The decision AI generators consistently get wrong is ON DELETE. The PostgreSQL foreign key docs give you 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 should not cascade at all, which is one reason soft deletes show up in story 8.

For indexes, cover every foreign key plus your two or three most frequent queries, then stop until you have query statistics. The PostgreSQL index docs are explicit that every index slows down writes.

The full Streakly schema

Eight user stories reduce to five tables, four relationships, and about sixty lines of DDL, with every constraint annotated back to the story that demanded it.

Notice what is absent as much as what is present. No current_streak column, because it is derived. No is_deleted boolean paired with actual deletions, because archival is a timestamp. No free-text schedule string, because a typed column plus an integer array of weekdays does the job properly.

A schema this size covers a real SaaS MVP. If one of your own stories will not map onto a table, a column, or a constraint, it is either out of scope for v1 or you have found a missing entity. Both are useful things to discover 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 that gets slow at scale, cache it in the application layer or a materialised view. The check-ins stay the source of truth.

Total design time by hand: about an hour.

Six schema mistakes that sink v2

Certain 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, because the app works, and expensive at v2, when a feature request collides with the flaw and forces a migration against live data.

Every one has a mechanical fix that is roughly 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 cannot be foreign-keyed or reverse-queried efficiently. Fix: any many-to-many 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, and reserve real deletes for privacy requests.

4. Premature optimisation. Sharding keys, six speculative indexes, a Redis cache column, at zero users. Fix: normalise first, index foreign keys plus known queries, denormalise only after measuring.

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

6. Multi-value columns. A tags text column containing "health,morning,fitness" violates first normal form and cannot 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 by Michael Hernandez.

Doing it with AI: what to check

AI schema generation genuinely changed the economics here. What took an afternoon takes a minute, and a good generator applies noun extraction more consistently than a tired human at 6pm.

But generated schemas fail in predictable places, so the skill shifts from writing DDL to auditing it. Five checks:

1. Every many-to-many 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 that list catches essentially every failure mode from the previous section. It is 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 close to deterministic. For the full pipeline, see generating a complete app spec from a single prompt.

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 behaviour.

What is an entity in database design?

Anything your system tracks independently, with its own identity and lifecycle, that other data points at. Users, habits, and orders are entities. A reminder time is 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?

How many rows of one table can relate to rows of another: one-to-one, one-to-many, or many-to-many. Determine it by asking the relationship question in both directions. "Can a user have many habits? Can a habit belong to many users?"

When do I need a junction table?

Whenever a relationship is many-to-many. Users and groups, posts and tags, students and courses. The junction holds one row per pairing, with foreign keys to both sides and a composite primary key preventing duplicates. If you are tempted to store an array of IDs in a column, that is the signal you need one.

Should I store calculated values like streaks or totals?

Not in v1. Derived data 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 is measurably slow, and treat the cache as disposable rather than as 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 generator applies noun extraction and cardinality analysis automatically, producing an ERD and SQL in seconds. The catch is the predictable failure modes above, so audit the output before running it.

What is the difference between an ERD and a schema?

An ERD is the visual design: boxes for entities, annotated lines for relationships and cardinality. The schema is the executable implementation, the CREATE TABLE statements with columns, types, keys, and constraints. Sketch the ERD to reason about structure, then translate to DDL.

How many tables should an MVP have?

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

---

The Database Schema Generator does the same stories-to-DDL translation in about thirty seconds, free and without signup. Paste your user stories, get a visual ERD plus copy-paste PostgreSQL, then run the five-point audit above against it. I would still recommend doing it by hand once, so you can tell when a tool is wrong.