Designing the Right Database Schema for Your MVP Web App: A Three-Step Process

Building new software always comes with unknowns. We try to reduce these with research, but there are always unanswered questions about what users truly need. This is where Minimum Viable Products (MVPs) come in. As engineers, we need to strike a balance between overly loose “cowboy architecture” and building with future scale in mind. The database schema of an MVP is particularly critical. It determines the application’s data model, so flexibility is important. Yet, we need enough structure to avoid problems when it’s time to scale.

A Three-Step Process for Designing the Right Schema

1. Extrapolate to the Edges

To understand an idea, take it to extremes – both big and small. This helps expose where it breaks down. Apply this to your MVP with non-functional requirements: How should the system perform at its absolute design limits? You likely don’t need planet-scale planning, but consider how it should behave once you hit traction.

2. Identify First-Class Objects

These are entities with identities independent of others. A common example is a ‘User’. In e-commerce, a ‘Purchase Order’ (PO) might be one.  An ‘Invoice’, however, is dependent on a PO and thus wouldn’t be first-class.

3. Visualize

Diagrams are powerful! Create a visual representation of your schema to see how things relate. This exposes potential complexity. Tools like ERD Editor in VSCode work well for MVPs.

An Example

I’m working on a SaaS app with GitHub-inspired access control for individuals, teams, and organizations. I used this process for my database design:

• Extrapolation: Defined desired features & performance at scale.
• First-Class Objects: ‘Users’, ‘Organizations’, and ‘Roles’ needed independent existence for flexibility.
• Visualization: The diagram helped me simplify and spot issues in relationships.

Features/Capabilities

• Individual user accounts
• Organization accounts
  • Users can belong to zero or more organizations
• Team support
  • Organizations can have zero or more teams
• Role-based access control that is flexible
  • Different roles for users in different organizations

Non-Functional Requirements

• Simple to grok schema
• Flexible Role-based ACL
• Be performant at this scale:
  • 1000-5000 Organizations
  • 10-20 Teams per Organization
  • 5-20 User per Team
  • 100,000-500,000 Users

Limitations

A note on the limitations – for the sake schema simplicity and flexibility I chose to handle the limitations with business logic in code.

• Organization Owner - Schema lacks enforcement of an organization “owner”
• Unique Organization/Team Names - Schema lacks normalization of Organization.name, Team.name

schema & Diagram

I used Prisma ORM to generate the SQL. I’ve shared my schema as a gist: “Flexible Multi-Tenant Web App Prisma Schema.” Take a look for specifics.

Summary

Building a database schema for an MVP that scales well means finding the middle ground between adaptability and thoughtful planning. Follow these steps to get closer to that balance, as the perfect answer at the outset is unlikely.