use traditional information management in database design

Apply principles from the physical world to your apps database architecture.

In Software development we want everything optimized. The programs should run fast, take less memory and be cheap to operate. In order to achieve that, we peal of some neat tricks. In this post I want to show you how the offline world can help you to optimize the right pieces.

Long before the age of computers people had do manage lots of information. ownership, money, human relationship, places, orders, payments, agreements and the list goes on and on. Today these information get handled for a large degree using computers. Doing so has allowed us, to handle much more complex information and relationships.

I want to discuss some typical pattern we face in database schemas, how we could change then to reflect findings in the offline world and what advantages we can get out of that.

First, get rid of your table fields called type or status. instead, introduce new tables for each type. For example a type ‘draft’ would in reality never be mixed with the actual items. In reality you have a pile of drafts and templates, and an other box, with the actual documents or items.

Separating typings into different tables, you get a natural sharding. the tables can potentially live on different servers. and when searching or an Item you do not need to filter the drafts. This will help you to get much faster query times. If you still need to query all at once, you can still make a union or join.

Second is a soft delete, it is quite similar, but still different. In some databases, the apps do not really delete data. The engineers then introduce a column like ‘deleted’ or ‘delete_time’. When Query that again causes the issue of filtering through deleted items. when you move deleted entries into a ‘trash’ collection, you can still pick some out, to restore then. The big advantage is again, much faster querying. You also have smaller indexes and can decide more flexible what fields really need to get indexed on the deleted entries.

Quite similar is versioning. In real world, since thousands of years, humans maintain ledgers and logs. continuously write into the version log,but keep the current state of the world separate, to work with it more efficient.

To make sure the log does not get to big over time, you can implement a strategy, to create new logs over time. This is a general practice in accounting. Every year companies make a balance and start new accounts for the new year. Companies traded in stock markets even do the balancing 4 times a year.

I have been working on a number of business apps, and a very common pattern is to introduce some kind of enterprise or organization IDs. But tell me, what a company would knowingly agree to mix the own data into one system with the competitors data. No, big enterprise require at the very least separate databases, if for some technical reason they have to be in the same system. Some information should just not be put into one place in the first place. This is something that can also greatly ease your development process. not caring for different organizations. What if one user work for two organizations? what when the email should be unique but only within one company. when developing your app with multiple organizations in mind, it will greatly increase the complexity as well as the databases work in maintaining indexes. The effort for developing a way to setup a new system for every enterprise can be done once and its complexity will not grow exponentially. In case your enterprises need do work together, they can still pass messages back and forward. Just like in the offline world.

good examples for that are messaging services like Discord or Slack, where organizations get there own servers. Or Jira and GitHub, where enterprises even can get setup within their own private networks.

I would say this have been three and a half examples, how your application design can greatly profit when learning from our physical environment. Now, it might seem like it, but I am not telling you to make a table for everything. But I want to remember, that technical decisions are not necessarily decisions about a better technical solution, but how you want to work in your team. If setting up a database is difficult and expensive for your company, maybe a simple database field can fix some problem.