Design a database with AI

Don't touch tables yet — list the nouns

The easiest way to start: describe your product in one sentence and circle the nouns. "A user places an order to buy products" gives you user, order, product — those three are probably your three tables.

List nouns first, then think about columns. Doing it the other way around (columns first) tends to spiral into a mess.

Three questions to answer first

1. What columns and types does each thing need? A user has an email (text) and a created time (timestamp); an order has an amount (number) and a status (text).
2. How do things relate? One user has many orders (one-to-many); one order has many products (many-to-many, which needs a join table).
3. Which columns get searched a lot? If you often look up a user by email, put an index on email so queries stay fast.

Tell the AI this

Organize the noun list, columns, and relationships, then ask the AI to generate the schema and migration — and require explicitly:

• Every table has id, created_at, updated_at, and times are stored in UTC.
• Use the right types (don't store money as text, don't store booleans as "0"/"1" strings).
• Express relationships with foreign keys, and index the columns you query on.
• Migrations must be reversible (every up has a down).

Example: a simple order system

One user has many orders; one order is made of several "order items"; each item points to a product. The ORDER_ITEM in the middle is the join table that handles "many-to-many."

Traps beginners fall into

• One giant table for everything: columns explode and one change ripples everywhere. Split when it makes sense.
• Everything as text: summing totals, sorting, and comparing all become painful later.
• Not storing times in UTC: cross-timezone bugs are guaranteed. Convert to local time only when displaying.
• Over-designing up front: build what you'll use, add more when needed. YAGNI.

Next steps

• Shore up the concept: Database
• Pick a database you'll actually use: Choosing a database: D1 vs Postgres vs SQLite