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 (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. Minimum Viable Products 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: : Defined desired features & performance at scale. Extrapolation : ‘Users’, ‘Organizations’, and ‘Roles’ needed independent existence for flexibility. First-Class Objects : The diagram helped me simplify and spot issues in relationships. Visualization 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 ORM to generate the SQL. I’ve shared my schema as a gist: “ .” Take a look for specifics. Prisma Flexible Multi-Tenant Web App Prisma Schema 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. Also published here.

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

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

Too Long; Didn't Read

Luis De Avila

STORY’S CREDIBILITY

Guide

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

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

Too Long; Didn't Read

Luis De Avila

STORY’S CREDIBILITY

Guide

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES