Database Design Tips for Faster, Better MVPs

“Your database model is not good enough.”

That’s what my university professor from 2010 would say, if he would see this. But I want to make a case about pragmatism.

My name is Till Carlos and I document how my team builds products. And today we are talking about a question that helps my database designs.

When designing database models, I prefer using a simple notation of boxes and arrows. This approach helps me think about relationships clearly without getting bogged down in complex Entity-Relationship Model (ERM) diagrams. The fundamental building block is the “belongs to” relation, represented by a simple arrow.

class Company
  belongs_to :user
end

This notation shows that a Company belongs to a User. It’s simple yet effective for visualizing relationships.

The Key Question: What are the main queries?

When refining a database model, I’ve found it helpful to repeatedly ask myself one crucial question: “What are the main queries my app will perform?” This focus helps streamline the design and ensure it meets the actual needs of the application.

For context: A SKU ordering app for Wholesalers

Let’s apply this approach to a real-world example: an app for companies to order Stock Keeping Units (SKUs) from wholesalers. This is a partnership project.

Plug: if you want to build a product with my agency in a partnership, send me an email.

Refining the Model By considering our main query paths, we can identify potential improvements:

1. Direct product access: Initially, to get a product from an order, we had to traverse multiple tables:

   Order -> API_Request -> Agent_Wholesaler_Login -> Agent -> Product

   @order = Order.find(42)
   @order.api_request.agent_wholesaler_login.agent.product

   This is inefficient. By adding a direct relation from Order to Agent, we simplify this to:

   Order -> Agent -> Product

2. Wholesaler login queries: For debugging, we want to easily see API requests associated with a specific wholesaler login. By moving the API_Request relation directly to Wholesaler_Login, we facilitate this common query.

3. Immutable relations: Some relations, like Wholesaler_Login to Company, should be immutable once set. This ensures data integrity as the system grows.

Balancing Normalization and Practicality

While database normalization is generally desirable, our MVP approach prioritizes quick implementation and data accumulation. We’re creating more of a log-style structure where we add data rather than frequently modifying existing entries. This allows for some denormalization without significant drawbacks at this stage.

The Importance of Query Path Length

When designing databases, it’s crucial to consider how long it takes to go from one object to another in your queries. Shorter paths generally lead to faster and more efficient queries. For example, we optimized our model to allow for quicker access to products from orders.

Flexibility for Future Changes

While we’re focusing on simplicity for the MVP, it’s important to design with some flexibility in mind. As we gather feedback from real users, we may need to adjust our model. For instance, we might need to add a relation from API_Request to Agent if we find we frequently need to query all API requests for a specific agent.

Ask This:

1. How often to relations change?
2. Redundant relations could be okay, if they never change.
3. If they don’t change: how can we enforce it?