How to Build Your First Database

When building a product for the first time, many people get stuck on database design. Should the user table be split? How should orders be stored? Should projects, tasks, logs, and config all be created from day one? Will fields change later? If the design is wrong now, will it be hard to fix?

The database matters, but the first version does not need to be perfect. Its most important job is to support the core flow reliably while leaving room for change. You are not designing a final model that never changes. You are designing a data structure that can evolve as the product is validated.

The early database has three goals: clear core objects, correct key relationships, and traceable important states. These matter more than having many tables.

Work Backward From the User Flow

Do not start database design by asking “what tables should I have?” Start with the user flow.

Write the path users take to reach the core result: signup, create project, submit input, generate result, view result, export result, and possibly upgrade. Then ask what data each step creates, what must be stored long term, and what is only temporary state.

For an AI report product, core objects may only be users, reports, report inputs, generation tasks, and subscriptions. Early versions may not need team tables, tag tables, comment tables, or complex audit tables. If the core flow works, the first version can stay small.

If an object does not appear in the main flow, do not rush to create a table for it. Many predesigned tables become useless after the product direction changes.

The first database principle is: store core business facts, not imagined future features.

Identify Core Entities First

Core entities are the most important nouns in the product. They often include users, projects, content, tasks, orders, subscriptions, files, and configuration. Not every product has all of them, but most SaaS products have some.

Use this question to identify entities: does this thing need to be created, queried, updated, deleted, related, billed, or tracked by status? If yes, it may be an entity.

For example, “report” is an entity because it is created, viewed, exported, and owned by a user. “Generating” is not an entity; it is a task or report status. “Advanced model” is usually not an entity; it may be configuration or an enum. Whether “one submitted form” is an entity depends on whether you need to store and reuse it long term.

Do not turn every noun into a table. More tables create more relationships. In the first version, keep core entities and store unstable details in fields or JSON until the need becomes stable.

Relationships Matter More Than Fields

Fields can be changed. Relationships are more painful to change. When designing the first database, think carefully about object relationships first.

Common relationships include: one user has many projects, one project has many tasks, one task generates one result, one user has one current subscription, one order belongs to one user, and one file belongs to one resource.

These relationships define data ownership and permission boundaries. Does a report belong to a user or a project? Does an order belong to a user or a team? Can one file be reused by multiple reports? If relationships are messy, permissions, billing, and admin all become complex.

Do not over-abstract too early. For personal products, resources can belong directly to user_id. Add workspace_id or team_id only after team collaboration becomes a real need. Premature multi-tenancy makes the first version heavy.

Design Status Fields Clearly

Many product problems come from messy states. Is a task pending, processing, successful, or failed? Is an order paid, refunded, canceled, or past due? Is a report draft, ready, archived, or deleted?

Status fields should be few and clear. Every status needs a defined meaning and allowed transition. Do not add states casually, and avoid expressing one concept through multiple booleans such as is_paid, is_canceled, and is_refunded together. These combinations easily become contradictory.

A better pattern is one main status field plus time and reason fields. For example: status = failed, failed_at, and failure_reason. This expresses the current state and helps debugging.

For async tasks, payments, emails, and uploads, status design is especially important. You need to know where the process is, why it failed, and whether it can be retried.

Do Not Skip Time Fields and Soft Delete

The first database should at least include created_at and updated_at. These fields look ordinary, but they are useful for debugging, sorting, analytics, and admin operations.

Many core tables should also consider deleted_at. Soft delete is not required for every table, but directly deleting important data such as user content, projects, reports, and orders can be risky. Soft delete lets you recover accidental deletion and preserve audit clues.

Soft delete also adds query complexity. You must make sure default queries filter deleted data, and admin tools display deleted state clearly. Do not implement soft delete and then forget to filter it.

Time fields and deletion strategy are basic engineering hygiene. Getting them right early saves trouble later.

JSON Fields Are Useful, but Do Not Overuse Them

When requirements are unstable, JSON fields are useful. AI generation parameters, raw third-party webhook data, report structure, user preferences, and experiment configuration can often start as JSON.

But JSON should not replace core fields. Anything frequently queried, filtered, sorted, related, counted, or used for permissions should be an explicit field. User ID, status, plan, price, created time, and resource ownership should not be hidden inside JSON.

A practical rule: unstable details that are not often queried can live in JSON. Stable data used in business decisions should become fields.

JSON is a buffer for iteration, not a universal bag for avoiding data modeling.

A First Database Template

A typical early SaaS can start with these tables:

users
- id, email, name, avatar_url, auth_provider, created_at, updated_at

projects
- id, user_id, name, status, created_at, updated_at, deleted_at

tasks
- id, user_id, project_id, type, status, input_json, output_json, error_message, created_at, updated_at

results
- id, user_id, project_id, task_id, title, content_json, status, created_at, updated_at, deleted_at

subscriptions
- id, user_id, plan, status, provider, provider_customer_id, current_period_end, created_at, updated_at

orders
- id, user_id, subscription_id, amount, currency, status, provider_order_id, raw_payload_json, created_at

This structure is not meant to be copied by every product, but it shows key ideas: ownership is clear, task state is traceable, results can be managed separately, payment data is bound to users, and raw third-party data is preserved.

If your product is simpler, remove projects or subscriptions. If your product has teams, add workspaces and memberships. Structure should serve the business, not the template.

Summary

Your first database does not need to be perfect. Work backward from the user flow, identify core entities, clarify relationships, design states, and keep time fields and necessary audit information. Stable data should be fields. Unstable details can start in JSON.

A good first database lets the product run, problems be investigated, and structure evolve. It does not need to cover every future feature, but it should avoid getting core relationships and states wrong. If core objects are clear, future changes are much less painful.

Homework

• Write all data created by your product’s core flow.
• Mark which items must be stored long term as core entities.
• Define the ownership relationship for each entity: user, project, or team.
• Design one main status field for tasks, orders, or content.
• Check whether core tables include created_at, updated_at, and necessary deleted_at.

Next Lesson

How to Design a User Table: a user table should not collect as many fields as possible. It should reliably carry identity, state, benefits, and future expansion.