Supercharge developer productivity with a flexible data model

How you store your data is important. The world of a startup is filled with evolving requirements, rapid iteration, and quick prototyping. A flexible data model allows you to navigate this ever-changing environment with ease, while an inflexible one will leave you feeling frustrated and constrained. Even worse, a bad data model will lead your code to become more entrenched in messy bad practices as additional functionality is added. Iteration speed will make or break a startup, so it’s crucial to make decisions that increase developer productivity rather than hinder it. So transitively, the structure of you data model will directly impact the success of your business.

I’m going to walk you through how I originally designed momo’s data model for representing a “goal” and how an iterative refactor made adding goal-related functionality a million times easier.

Some things you should know

Some useful concepts to understand to make the most of this article:

The basics of relational database schema design (Primary keys, Foreign keys, types of table relationships, etc.)

Inheritance (Object-Oriented Programming)

What an ORM is

Typescript + TypeORM (helpful)

Here’s a quick overview of how TypeORM entities (the code you’ll see below) are structured and how it relates to the database schema:

// An entity maps to a table of a database
@Entity()
class TestTable extends BaseEntity {
  // A column represents, well, a column of a table
  // A primary column denotes the column as a Primary Key of the table
  // We pass the type of the column into the decorator
  @PrimaryColumn('varchar')
  // The ! or ? signal whether the value can be null or not
  // The type following the colon is the type of the value
  // returned from the DB 
  id!: string;

  // We also use decorators to express relationships between tables
  // This decorator specifies that our TestTable has a Foreign Key 
  // called different_table_id that maps to the Primary Key of the
  // DifferentTable table and that multiple rows in TestTable can 
  // map to the same key in DifferentTable
  @ManyToOne(() => DifferentTable)
  @JoinColumn({ name: 'different_table_id' })
  differentTable: DifferentTable;

  // If a column can have NULL values, we need to pass that
  // option to the decorator as well as tell Typescript using ?
  @Column({ type: 'varchar', nullable: true })
  nullable_column?: string;
}

@Entity()
class DifferentTable extends BaseEntity {
  @PrimaryColumn('varchar')
  id!: string;
}

The original situation

The overarching mission of momo is to help you achieve your financial goals faster than ever before (starting with credit cards).

After signing up, momo asks you what your current goals are:

The way we currently represent goals is simply a toggle - you either have a certain goal, or you don’t. If you press ‘Other’, you can enter a custom goal in a text input.

Here’s how we originally stored this information in our database:

@Entity()
class MomoUser extends BaseEntity {
  @PrimaryColumn('varchar')
  id!: string;
  ...
  @Column({ type: 'varchar', nullable: true, array: true })
  momo_goals?: string[];
}

Quick note: momo’s backend is written in Typescript and uses a PostgreSQL database. We use TypeORM to interact with our database, so all of the code snippets will use this syntax.

Additionally, we use a third-party service for managing authentication, so we use the id of a user from that service as the PK (Primary Key) of our table rather than generate one.

We stored all of a user’s goals as strings in a list, and if the user selected “Other”, we would store a string of “Other: “ followed by whatever the user typed in the text input.

This never felt very optimal to me (especially how we handled the ‘Other’ case), but it was the first implementation I thought of and it didn’t cause any headaches initially, so I went with it.

The problem

In order to help users understand how to use momo effectively, we designed a walkthrough for each goal that momo supports:

One of the requirements for this functionality is to keep track of which walkthroughs the user has completed. We want to show a purple check next to modules they’ve completed, and a gray check next to those they have not completed. My first instinct was to create an array on the MomoUser entity, just like I did for the goals:

@Entity()
class MomoUser extends BaseEntity {
  @PrimaryColumn('varchar')
  id!: string;
  ...
  @Column({ type: 'varchar', nullable: true, array: true })
  momo_goals?: string[];
  @Column({ type: 'varchar', nullable: true, array: true })
  completed_goal_walkthroughs?: string[];
}

This approach didn’t sit right with me for a few reasons:

Both momo_goals and completed_goal_walkthroughs are stored as varchar so we have no validation that the content of these arrays represented valid goals

It is possible to insert an entry into completed_goal_walkthroughs that didn’t correspond to a user’s goal

When a user completes a walkthrough module, we need to iterate through the list to make sure we don’t add a duplicate

In the future, we want to allow users to set achievement dates for their goals - how would that work with this model?

So I decided to consult my two good friends, Google and Stack Overflow, to piece together how I could make our schema more flexible.

If you’re looking to practice your database design skills, think through how you would design this before reading on!

…

The initial refactor

The two most pressing issues that I wanted to address were:

Move the representation of a “goal” from an array column to its own table. This would allow us to store additional data, like whether the walkthrough module for that goal was completed.

Standardize the goals we support. Storing a goal type as varchar is too lenient for something we know the possible values of upfront.

After a bit of reading, I arrived here:

enum MomoGoalType {
  OpenNewCard = 'OpenNewCard',
  EarnMoreRewards = 'EarnMoreRewards',
  ...
  Other = 'Other'
}

@Entity()
class MomoUser {
  @PrimaryColumn('varchar')
  id!: string;
  ...
}

@Entity()
class MomoGoal {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column()
  user_id!: string;

  @ManyToOne(() => MomoUser)
  @JoinColumn({ name: 'user_id' })
  user!: MomoUser;

  @Column({ type: 'enum', enum: MomoGoalType })
  type!: MomoGoalType;

  // Should only be present for `Other` goals
  @Column('varchar', { nullable: true })
  other_goal?: string;
	
  @Column('boolean', { default: false })
  is_walkthrough_completed?: boolean;
}

This is pretty good! We now have an easy way to store additional data for goals, as well as a way to validate the type of a goal.

After doing some more reading, though, I learned that using enums in your DB schema has some trade-offs:

Adding a goal requires an ALTER TABLE command, which will lock the entire table while the operation is running

Enums are not SQL standard, making the schema locked-in to a specific DBMS

Additionally:

As we add more goals, we will need to add more goal-specific fields. For example, the EarnMoreRewards goal might have a goal_amount field, but no other goal type should have that field. This will get a bit unwieldy as the number of goals we support increases, resulting in each row having a large number of NULL fields.

We can’t guarantee that other_goal will only be present for goals of type Other. Having other_goal present on incompatible goals would heavily compromise our data quality - and this is true for any goal-specific columns that we add.

We can mitigate some of these issues by using a lookup table - so let’s refactor again.

Enum → Lookup table

A lookup table is essentially a “hardcoded” table that stores a list of valid values or types. We are using this table to perform the same function as an enum, but without the trade-offs.

Using a lookup table looks (ha, pun) something like this:

enum MomoGoalTypeEnum {
  OpenNewCard = 'OpenNewCard',
  EarnMoreRewards = 'EarnMoreRewards',
  ...
  Other = 'Other'
}

@Entity()
class MomoUser {
  @PrimaryColumn('varchar')
  id!: string;
  ...
}

@Entity()
class MomoGoalType {
  @PrimaryColumn('varchar', { length: 30 })
  name!: MomoGoalTypeEnum;

  @Column('varchar', { nullable: true })
  description?: string;
}

@Entity()
class MomoGoal {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column()
  user_id!: string;

  @ManyToOne(() => MomoUser)
  @JoinColumn({ name: 'user_id' })
  user!: MomoUser;

  @Column()
  type!: MomoGoalTypeEnum;

  @ManyToOne(() => MomoGoalType)
  @JoinColumn({ name: 'type' })
  typeEntity!: MomoGoalType;
	
  @Column('varchar', { nullable: true })
  other_goal?: string;

  @Column('boolean', { default: false })
  is_walkthrough_completed?: boolean;
}

This is a lot to unpack, so let’s explain what’s going on here.

Instead of using an enum in our DB, we created a MomoGoalType table that will be a lookup table for our different types of goals. This allows us to know the “type” of each goal while avoiding all the issues that come along with using enums.

Another benefit of using the lookup table is that we now have a description field that we can use to store additional context about the goal. This is extensible as well - we could include a deprecated column to indicate goals that momo no longer supports.

In our code, we type the name and FK (Foreign Key) type as MomoGoalTypeEnum because those enum values will be the only values we insert into that table (as per the “hardcoded” reference earlier). To make this table resilient to data loss, I hardcoded the list of types (each value of MomoGoalTypeEnum) along with descriptions of each and wrote a script that uses that list to populate the lookup table. That’s outside the scope of this post, so I won’t go in to depth on that here.

There’s still some room for improvement, though. We still have the following issues mentioned above:

As we add more goals, we will need to add more goal-specific fields. For example, the EarnMoreRewards goal might have a goal_amount field, but no other goal type should have that field. This will get a bit unwieldy as the number of goals we support increases, resulting in each row having a large number of NULL fields.

We can’t guarantee that other_goal will only be present for goals of type Other. Having other_goal present on incompatible goals would heavily compromise our data quality - and this is true for any goal-specific columns that we add.

Let’s fix the first one of these and set ourselves up to fix the second.

Representing inheritance in a relational schema

If you’ve taken a beginner Object-Oriented Programming course, you’ve probably heard of inheritance.

Basically, we want to represent each goal type as a subclass while sharing some properties of a superclass. In other words, all goals will have a is_walkthrough_completed field, but only “Other” goals will have an other_goal field.

Based on this Stack Overflow post, I evaluated three designs for modeling this relationship:

Single table inheritance - Keep everything in one MomoGoal table like we have above.

Concrete table inheritance - Use separate tables for each goal. We would have entities for GetANewCardMomoGoal, EarnMoreRewardsMomoGoal, OtherMomoGoal, and so on.

Class table inheritance - One base table MomoGoal with common properties and entities with goal-specific data, like MomoGoalOther, that have a one-to-one relationship with MomoGoal

I decided on option 3. It felt like a balanced approach and a happy medium between one unwieldy table and entirely separate tables with duplicated columns. I would highly recommend reading the Stack Overflow answer + comments to more thoroughly understand the trade-offs here.

So here’s what our schema looks like after this refactor:

enum MomoGoalTypeEnum {
  OpenNewCard = 'OpenNewCard',
  EarnMoreRewards = 'EarnMoreRewards',
  ...
  Other = 'Other'
}

@Entity()
class MomoUser {
  @PrimaryColumn('varchar')
  id!: string;
  ...
}

@Entity()
class MomoGoalType {
  @PrimaryColumn('varchar', { length: 30 })
  name!: MomoGoalTypeEnum;

  @Column('varchar', { nullable: true })
  description?: string;
}

@Entity()
class MomoGoal {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column()
  user_id!: string;

  @ManyToOne(() => MomoUser)
  @JoinColumn({ name: 'user_id' })
  user!: MomoUser;

  @Column()
  type!: MomoGoalTypeEnum;

  @ManyToOne(() => MomoGoalType)
  @JoinColumn({ name: 'type' })
  typeEntity!: MomoGoalType;

  @Column('boolean', { default: false })
  is_walkthrough_completed?: boolean;
}

@Entity()
class MomoGoalOther {
  @PrimaryColumn()
  id!: string;

  @OneToOne(() => MomoGoal)
  @JoinColumn({ name: 'id' })
  goal!: MomoGoal;
	
  @Column('varchar', { nullable: true })
  other_goal?: string;
}

With this approach, we extract the other_goal field into a separate entity called MomoGoalOther, which represents the additional data for the “Other” goal.

Although we only have other_goal in this case, it’s important to note how flexible this is - if we add a new goal that has 5 goal-specific fields, this model can handle that nicely.

We also specify a one-to-one relationship between MomoGoalOther and MomoGoal. Because our join column is the PK for the table (id), we can ensure that only one MomoGoalOther can map to a given MomoGoal. If we weren’t using the PK as the join column, TypeORM would include a UNIQUE constraint on the join column which would provide the same restriction.

This design is extremely flexible - but we still have one issue: we can’t guarantee that a goal will only have additional fields specific to that goal.

As it stands right now, we could have a MomoGoal of type EarnMoreRewards, but still create a MomoGoalOther entity that maps to that goal, resulting in the same loss of data integrity as before.

Discovering the solution to this problem was the most mind blowing part of this refactor for me, so get ready!

The “one-to-either” relation

In this article, the author outlines the idea of the “one-to-either” relation, and how to create one.

In summary, the one-to-either relation is similar to a one-to-one relation, but we want to only contain one one-to-one relationship out of a set of possible one-to-one relationships.

In our case, each MomoGoal has a potential one-to-one relationship with MomoGoalOther.

If we add additional goal-specific tables, we will have possible one-to-one relationships with MomoGoalEarnMoreRewards, MomoGoalGetANewCard, and so on.

But we only want one of these relationships to exist for a given MomoGoal - and specifically the one that corresponds to the type of the goal.

A MomoGoal of “Other” should only have one corresponding one-to-one relationship, and on the other side of the relation should be a MomoGoalOther entity.

The article shows a very clever way to enforce this constraint, and here’s our new code with this strategy applied:

enum MomoGoalTypeEnum {
  OpenNewCard = 'OpenNewCard',
  EarnMoreRewards = 'EarnMoreRewards',
  ...
  Other = 'Other'
}

@Entity()
class MomoUser {
  @PrimaryColumn('varchar')
  id!: string;
  ...
}

@Entity()
class MomoGoalType {
  @PrimaryColumn('varchar', { length: 30 })
  name!: MomoGoalTypeEnum;

  @Column('varchar', { nullable: true })
  description?: string;
}

@Entity()
@Unique(['id', 'type'])
class MomoGoal {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column()
  user_id!: string;

  @ManyToOne(() => MomoUser)
  @JoinColumn({ name: 'user_id' })
  user!: MomoUser;

  @Column()
  type!: MomoGoalTypeEnum;

  @ManyToOne(() => MomoGoalType)
  @JoinColumn({ name: 'type' })
  typeEntity!: MomoGoalType;

  @Column('boolean', { default: false })
  is_walkthrough_completed?: boolean;
}

@Entity()
@Check(`"type" = '${MomoGoalTypeEnum.Other}'`)
class MomoGoalOther {
  @PrimaryColumn()
  id!: string;

  @Column()
  type!: MomoGoalTypeEnum.Other;

  @OneToOne(() => MomoGoal)
  @JoinColumn([
    { name: 'id', referencedColumnName: 'id' },
    { name: 'type', referencedColumnName: 'type' },
  ])
  goal!: MomoGoal;
	
  @Column('varchar', { nullable: true })
  other_goal?: string;
}

If your mind isn’t blown quite yet, that’s okay - let’s walk through how and why this works.

Let’s start by looking at MomoGoalOther. We added a type column, and a CHECK constraint to ensure that every MomoGoalOther entity has a type value of “Other”. This may seem redundant, but it is what makes this whole thing possible.

Quick note: We could also use a persistent computed value here (like in the article), but this doesn’t seem possible using TypeORM. But typing type as MomoGoalTypeEnum.Other provides us some static type-checking to warn is if we write code that will hit that constraint at runtime.

Here’s where the magic comes in.

We also modified the JoinColumn decorator on goal - now we are specifying two columns (id and type) instead of just id.

By using a composite foreign key, we can ensure that both id and type match the id and type of the MomoGoal on the other end of the relationship.

And since every MomoGoalOther entity will have a type of “Other”, trying to create a relation between MomoGoalOther and a MomoGoal of type “EarnMoreRewards” will be invalid for violating the foreign key restraint.

This accomplishes exactly what we wanted - ensuring each MomoGoal has only one relation to a goal-specific-data table, and that the table matches the type of MomoGoal.

Quick note: We also added a UNIQUE constraint to MomoGoal. This is redundant, since each id is a generated uuid, but is necessary in order for our foreign key relationship to work.

Wrapping up

So there we are!

With this final refactor:

Each goal can now have goal-specific metadata without having a one large, unwieldy table while ensuring each goal only has data specific to that goal.