How to salvage a business model from a mismatched database?

Question

There is an application that involves billing enterprise customers for the services their customers utilize. The database schema does not properly reflect the business model. For instance, on a given account, services may become activated and deactivated. There is only a column to represent when the service was deactivated and the code in place just updates that same row if the service is reactivated rather than adding a new row. Ideally, a row would be added when a service activates and updated when deactivated, and there would be a column to represent the activation date.

As it stands, there is no solid billing history for these enterprise customers, but there needs to be from here on out. Do we set a "cutoff" for the legacy model that was corrupted and wipe the table clean? Do we add the appropriate column and start working with the table properly and just check for rows that have a 'activated date' of null to detect legacy? There must be several ways of handling this, but I have no experience with this kind of situation and would like to hear out some various approaches.

Answer 1

"Of course the way things are now, there is no solid billing history due to the mistakes that were made."

As far as I understand your question, your database scheme is missing some features (like history information), and you have problems to change it because you don't know the full impact into your existing system.

One approach for dealing with that kind of situation is to solve the problem with upward-compatible extensions to your data model. For example, you need a history in your billing table? Add a new "billing_history" table, with exactly the same columns as your billing table, and an additional "record version number" as well as a "validity date" column. Then add an "after update" trigger to that table which makes a copy of the current billing record into the "billing_history" table whenever the "deactivated" flag is reset to "activated". Now, you can add new modules to search, display and access that history table, and you don't have to change anything in your existing modules.

The details may be different in your situation, but I hope you get the idea.

Answer 2

Your issue is not the database schema. It is the question where the code influences the data. If it is structured well you can just change code and schema. Implement a piece of code which handles missing history for old records and you are done.

The fact that you would want to put tricks into your schema or database to prevent any unknown issues means that the code view there is currently is not complete. So would focus strongly on having a good overview of where the code acts with the schema.

If you find it scattered all over the place then you might consider first improving the code structure instead of directly changing to the functionality you need. That will create a better end-result.

Why: Think about all changes you might have to make in future. If you need to apply tricks now you have to take care of them all the way along. If you fix it straight from the beginning in a right way it saves you time in the end without having to over-engineer.

Answer 3

Your title suggests the business model is mismatched from the schema, but your current thought process is only focusing on tracking history.

The actual problem here is that the data schema is too closely aligned with the business model, that is, it only tracks and displays the current state of data. What you are really asking for is a level of abstraction that allows you to gather business insights over time to satisfy some new reporting requirements.

In an SQL database it is pretty easy to bolt-on audit and/or version tracking for any or all tables using triggers. Similar to the solution suggested by @Doc Brown however, I would use a generic schema for storing the changes and use dynamic logic to generate out the AFTER INSERT|UPDATE|DELETE triggers.

The benefit to this is that the existing application should not be affected, making this backward compatible. You can then add improvements to the application to view the history for records or show the current row version timestamp if you want.

When dealing with existing applications, we really want to be careful of introducing breaking changes. Depending on the code or frameworks involved we can usually add new fields and tables and relationships without breaking the existing logic as long as we use reasonable default values or allow nulls. But not all code bases will support adding new fields to existing tables, so you need to have an understanding of what types of changes the existing application will tolerate before you go too far down the design path.

Adding in a transparent audit trace or version history through the use of triggers is a good start and solves your data issues before you start making changes to application logic.

Do we set a "cutoff" for the legacy model that was corrupted and wipe the table clean?

It is not clear if the legacy model is corrupted at all. It still contains the current data, the fact that there is no history cannot be helped but you can insert the first history record right now, effectively marking the data as valid from this point on.

If there are fundamental flaws in the current schema, and or the current application is not well aligned to the business process, then you may ultimately benefit from a new user interface and schema. When approaching a Version 2 of a product it is important to decide if you will attempt to import the historic data, or if you will simply cut over to the new application and start with a clean database. Implementing an audit trace now will mean that you have a good set of data when or if you need access it to migrate data to the new system. If you need to run the old and new system in parallel then you can implement Change Data Capture by adapting your version history solution to include flags to identify if the data has been synchronized with the new system.

• In the new system you would also setup Change Data Capture if you want to support pushing changes back to the legacy system.

You will need some sort of ETL process to physically migrate the data between the two systems, but doing it in this way gives you complete autonomy to rewrite the schema to match the current business process without affecting the current user process while you develop the solution and train the users to use the new one.