Shifting Your User Defined Field Pattern

My last three posts laying out a brief history of User Defined Field Patterns past, present, and future.  This post lays out a framework for migrating.  If your CRM is using the Relational or NoSql pattern and you’re ready to move to the more efficient, cheaper, and simpler future, this is the post for you!

Migration Philosophy

Before going into how to migrate, a reminder of my philosophy:

  • Minimize risk by taking small incremental steps
  • Focus on providing value to the customer

There are many ways to migrate from one pattern to another.  This strategy will minimize risk and maximize customer value.

Step 1 - Extend Your Relational Database

The Relational and NoSql patterns make use of a relational database.

Step 1 is to add a JSON column to your existing contacts column.

Your new schema should look like one of these two models

That’s it - deploy an additive schema update to your database.  Since there’s no code to access the new columns, there’s no coordinated deployment.  Just the regular, minimal, risk of updating your database.

Step 2 - Query The New Schema

Now that the new schema is in production, it is time to extend your query code.

Add a new code path that checks to see if any data is present in the new schema.  If there is data available, execute the query using the new JSON column.  When there’s no data, use the original query method.

You will need to develop this code hand-in-hand with the code for migrating the data from your original system to the new schema.  The important piece is that you should always be deploying with the READER code on, WRITER code off.

When you deploy this code, there won’t be any data in the JSON column.  The new code will be available, but unused.

Since the new code won’t be used, this step is also extremely low risk.

Step 3 - Double Write

At this point your system will use the new schema whenever data is present.  

This gives you a single switch to flip - to use the new system, start writing to the new column IN ADDITION to the original method.

Mistakes at this step are the most likely to cause customer impact!  It is also the most expensive in time and resources because you are writing the data twice.

However, this also gives you a very quick fallback path.  The original writing process is untouched!  

If there’s a problem, turn off the double write and delete the data in the new column.  Thanks to the work in Step 2 you’ll instantly fall back with NO DATA loss.

Migrations are hard!  Preventing data loss minimizes the risk.

Step 4 - Only Hybrid Write

The final step is to stop writing to the original data store.  This ends your ability to fall back so make sure to confiscate copies of the data deleter from Step 3!

Ending the double write should be low risk because you were only doing it as a fallback at this point.  You should see an immediate bump in performance and drop in costs.  This trend will continue as the data migrates from the old system to the new.

Step 5 - Clean Up

At some point you’ll be ready to shut down the old system.

The last step is to decide what to do with the unmigrated data.  Depending on how long you’ve waited you’re looking at customer data that hasn’t been accessed in months.  Look at your retention promises; maybe you don’t have to migrate the data at all.

Either way, clean it up and shut down the old system at your leisure.

Conclusion

You can migrate User Defined Field code to the latest pattern with very little risk by using the 5 step strategy laid out in this article.

The Hybrid Solution offers excellent scalability and performance for reasonable costs.  If your CRM is using one of the earlier patterns, it is time to start migrating.

Take control of the process with small, low risk, steps and never rewrite!

User Defined Field Patters 3 – Hybrid Relations

Part 2 covers how NoSQL emerged as an improvement over the classic relational database solution for User Defined Fields. NoSQL delivers speed and scalability by being expensive and fragile.  In part 3 I’m going to cover the emerging Hybrid Database solution for User Defined Fields.

Hybrid Databases allow you to combine the best aspects of the relational and NoSQL models, while avoiding most of the downsides.

A hybrid implementation looks like this:

The hybrid model brings the data back to a single server, but without the Contact->Field relation.  Instead the field data is stored as a JSON object in the Contact table itself.

Pros:

  • No meta programming and no filters, everything is back to SQL.  Hybrid databases allow you to directly query JSON fields as if they were regular columnar fields.
  • You can create indexes on the JSON data.  This is an improvement over both the classic and NoSQL models.  It can significantly improve performance by allowing the database engine to optimize queries based on usage.
  • Having a single system makes things simple to set up and easier to maintain.
  • The database will enforce valid JSON structures, which makes it difficult to poison your data.

Cons:

  • There’s no enforced relationship between the JSON data and your User Defined Fields.  This means that data can get lost because your system no longer knows to display or delete it.
  • While Hybrid Databases should scale far beyond the needs of your SaaS, the scaling isn’t quite as open ended as the NoSQL model.  If you out-scale the Hybrid model, congratulations, your company’s services are in high demand!

Conclusion

If your SaaS is implementing User Defined Fields from scratch today, go with the Hybrid model.  If you already have the classic or NoSQL pattern in place, it’s a good time to start thinking about how to evolve towards a hybrid solution.

I’ll cover how to evolve your existing solution in Part 4.

User Defined Field Patterns 2 – NoSql Relations

In part 1 - I covered the classic solution for User Defined Fields; simple but unscalable.

NoSQL emerged as a solution to relational fields in the late 2000s.  Instead of having a meta table defining fields in a relational database, the User Defined data would live in NoSQL.

The structure would look like this:

This model eliminates the meta programming and joining the same table against itself.  The major new headache that this model creates is difficulty in maintaining the integrity of the field data.

Pros:

  • No complicated meta programming.  Instead you write a filter/match function to run against the data in the Collection Of Fields.
  • No more repeated matching against the same table.  Adding additional search criteria has minimal cost.
  • Open ended/internet level scaling.  For a CRM or SaaS, the limiting factor will be the cost of storing data, not a hard limit of the technology.

Cons:

  • Much more complicated to set up and maintain.  Even with managed services supporting two database technologies doubles the difficulty of CRUD.  Multiple inserts, multiple deletes, tons of ways for things to go wrong.
  • Without a relational database enforcing the data structure, poisoned or unreadable data is common.  Being able to store arbitrary data collections means you’ll invariably store buggy data.  You’ll miss some records during upgrades and have to support multiple deserializers.  You will lose customer data in the name of expediency and cost control.
  • It’s more expensive.  You’ll pay for your relational database, NoSQL database, and software to map between the two.  

Conclusion

NoSQL systems solve the scaling problems with setting up User Defined Fields in a relational database.  The scaling comes with high costs in terms of complexity, fragility and costs.

Reducing the complexity, fragility, and costs leads to the upcoming 3rd shift, covered in part 3.

User Defined Field Implementations For CRMs

This series covers a brief history of the 2 historic patterns for implementing User Defined Fields in a CRM, the upcoming hybrid solution that provides the best of both worlds, and how to evolve your existing CRM to the latest pattern.  If you care about CRM performance, scaling, or cost, this series is for you!

What are User Defined Field Patterns?

Every CRM provides a basic fields for defining a customer.  Every CRM’s basic field set is different depending on the CRM’s focus.  So, every user of a CRM needs to expand the basic definition in some way.  Birthdays, purchase history, and interests are three very common additions.

The trick is allowing users to define their own fields in ways that don’t break your CRM.

The Three Patterns

At a high level, there have been three major architectures for implementing Custom Fields.  Most of the design is driven by the strengths and weaknesses of the underlying database architecture.

Pattern 1, generalized columns in a database, spanned the dawn of time until the rise of NoSQL around 2010.

Pattern 2, NoSQL, began around 2010 and continues to today.

Pattern 3, JSON in a relational database, began in the late 2010s and combines the best of the two approaches

Pattern 1 - All in a Relational Database

Before the rise of NoSql there was pretty much one way to build generic user defined fields.

The setup is simple, just 3 tables.  A table of field definitions, a table for contacts, and a relational table with the 2 ids and the value for that contact’s custom field.

The Pros

  • This design is extremely simple and can be implemented by a single developer very quickly.
  • Basic CRUD operations are easy and efficient.

The Cons

  • Building search queries requires complicated techniques like metaprogramming.
  • Every search criteria results in a join against the ContactFields table.  This results in an exponential explosion in query times.
  • The lack of defined table columns handicaps the database’s query optimization strategies.

Conclusion

The classic relational database pattern is easy to set up, but has terrible scaling.  This super simple example would bog down by 1,000 contacts and 50 fields.  

There are lots of ways to redesign for scale, but this is a SHORT history.  Suffice it to say that it takes extremely complex and finicky systems to scale past 100,000 contacts and 1,000 fields.

The solutions to the classic pattern’s scaling led to the NoSQL revolution, covered in part 2.

Do You Punish Customers For Loyalty?

Does your Customer’s experience with your service get better over time?

Does it get worse?

SaaS software often punishes long term clients in subtle and frustrating ways.

Do your CRM customer screens show a decade of buying history?

How many emails can a contact open before you can’t open the contact?

Do marketing campaigns, contact lists, and tags accumulate over the years?

Do database inserts slow down as you write the 10 millionth row into a log table?

There are countless ways to punish customers for staying with you for years.  It’s not a startup problem, it sneaks in as you become a scaleup.  The flood of new customers blinds you to the slow leak as your most loyal customers churn.

When your longest customers complain about performance more than your largest, chances are your software is punishing them for being loyal.

The Implications Of Your Characteristics

Part Three of my series on iterative delivery 

You have a goal, you have characteristics, now it’s time to ponder the implications.

The implications are things that would have to be true in order for your characteristic to be achieved.  They are levers you can pull in order to make progress.

Let’s work through an example

In part 2 I suggested that a Characteristic of a system that can support clients of any size is that API endpoints respond to requests the same amount of time for clients of any size.

What would need to happen to an existing SaaS in order to make that true?

  • The API couldn’t do direct processing in a synchronous manner.  It would have to queue the client request for async processing.  Adding a message to a queue should be consistent regardless of how large the client, or the queue, becomes.
  • For data requests endpoints would need to be well indexed and have constrained results.
  • Offer async requests for large, open ended, data.  An async request works by quickly returning a token, which can then be used to poll for the results.

Implications are measurable

How much processing can be done asynchronously today?  How much could be done last month?

How many data endpoints allow requests that will perform slowly?  Is that number going up or down?

How robust is the async data request system?  Does it exist at all?

Implications are Levers

Progress on implications pushes your service towards its goal.  Sometimes a little progress will result in lots of movement, sometimes a lot of progress will barely be noticeable.

Speaking to Implications

It is important that you can speak to how the implications drive progress towards your goal.

Asynchronous processing lets your service remain responsive.  It doesn’t mean you can process the data in a timely manner yet.  It sets the stage for parallel processing and other methods of handling large loads.

Next Steps

Before continuing on, try to come up with 3 implications for your most important characteristics.

You’ll want a good selection of implications for the next part - blockers.

We will explore what’s preventing you from moving your system in the direction it needs to go.

Picking an Iterative Goal at a Scaleup

Note: This is part of my series on Iterative Delivery

When you are in Scaleup mode, picking a goal to iterate on should be straightforward.

In Scaleup mode, picking an iterative goal should be straightforward.

What can’t you deliver?

Are you attracting larger clients and discovering your software can’t handle their size?

Do you have a swarm of small clients overwhelming the backend?

Does throwing money at your problems keep the software running smoothly, but unprofitably?

Your goal should be a single, short, aspirational sentence.  

If you get stuck, try the “We should be able to ___”  template:

We should be able to support clients of any size!

We should be able to support any number of clients!

We should be able to support clients profitably!

You don’t need to have any idea how to achieve your goal, your goal might not even be achievable.

The important thing is that you can clearly state your goal and explain it to others.

The Only Client Experience

The only client experience you can offer clients is the one you have today.  Yesterday’s experience is a memory, and tomorrow is a promise.

Delivering iteratively, baby step improvements to your client’s experience, makes the experience better today, tomorrow’s promise more believable, and memories of things improving.

Promising a moonshot keeps the only experience your client’s have exactly the same; and if it was a good one, you wouldn’t need a moonshot.

When Your Customers Struggle

When your customers struggle with a non-intuitive interface, would they rather:

Wait 6 months while you work on an amazing redesign and brand refresh, or get weekly tweaks that make using your product easier and more intuitive?

Branding and color schemes are about you, improving the UI is about your customers.

Don’t put off customer steps while working on a moonshot like a branding refresh.

Moonshots vs Baby Steps at A Scaleup

You’re A Scaleup!  What’s a Scaleup?

You become a Scaleup when your SaaS’s service offering becomes compelling and you start attracting exponentially more clients.

All at once you have a lot more clients, clients with a lot more data.

Solutions that support 1,000 clients buckle as you pass 5,000.  Suddenly, 25,000 clients is only months away.

Services that support hundreds of thousands of transactions a day fall hopelessly behind as you onboard clients with millions of transactions.

You finally know what customers want.  You quickly find the edges of your system.  Money is rolling in from customers and VCs.  You can throw money at the problems to literally buy time to find a solution.

But you’re faced with a looming question - moonshots or baby steps.

Moonshots Are About You, Baby Steps Are About Your Clients

It’s not about you or your SaaS, it’s about your client’s outcomes.

Moonshots are appealing because they take you directly to where you need to be.  Your system needs to scale 10x today and 100x next year; why not go straight for 100x?

Baby steps feel like aiming low because the impact on you is small.  But it’s not about you!  Think about the impact on your clients.

From a technology perspective, sending emails 1% faster is ::yawn::

But for your clients, faster emails means more engagement, which means more sales.

Would your clients rather have more sales this week, compounding every week for the next year, or flat sales for a year while you build a moonshot?

Clients who churn, or go out of business, won’t get value from the moonshot.  Even if you deliver greater value eventually, your clients are better off getting some value now.

Are you delivering value to your SaaS or your clients?

Site Footer