It’s happened again. That conundrum of expedience over longevity. The design for the immediate, the just get it done mentality, over building software using well proven design patterns that last. I was talking with a colleague who asked me what I thought about making a database change using a Materialized View and an Instead Of trigger to handle a simple database problem where they needed a table, and to maintain history of the table contents as it was modified.
This is a pretty standard database pattern where you have two copies of the table. The first has the current instance of data, which he needed. The second has a history of the first table, with some kind of version identifier such as a version number, or a DATETIME column, allowing you to differentiate between records. He actually needed the history table so that users could view the table contents and how it changed over time.
SQL Server has this history capability already built into the database engine. You simply have to configure it for historical capabilities and it’s already done. But, not every system is built on SQL Server, so you may need to roll your own solution, which was what they were up against.
That brings me to a second architectural failure of their system. They chose to use multiple micro services for their application, which I am a fan. However, instead of having a data access layer that handled all manipulation for this table on which they now need history, they put a separate implementation in each of the services. The point is that even when we use things like micro services, never write the same code twice. This table can have only one schema, no matter how many services utilize it.
So, to keep the impact to a minimum and not have to touch code in multiple places, instead of fixing the design flaw, the work was going to be put back on the database. My colleague was considering creating a new table and history table, and then building a materialized view with INSTEAD OF triggers, so that the trigger could manage all of the history stuff, and since the view looked the same as the original table, no modifications were going to be needed in the application.
So, we start out with a bad design, and instead of fixing it, we build another bad design on top of it, and expect our application to continue to perform well as it is adopted by the hungry business users. All I can say is, “OUCH!”. It’s like hitting yourself in the head with a hammer because it feels so good when you stop. Why not decide ahead of time not to hit yourself in the head with a hammer in the first place.
I am not saying there is not a place for materialized views or instead of triggers. When we used to shard data across multiple table instances, or even multiple databases, we used views to pull things all back together, and then use INSTEAD OF triggers to handle the updates to the individual table. That was before we had partitioned tables in SQL Server, and was called a Federated View. But there are performance issues that occur when you make this distinction. We’ll talk about that tomorrow. For today the point is simply, When you see things like Materialized Views, or INSTEAD OF triggers, you need to ask yourself how you got to this point, and do you have an architectural problem you are trying to solve with Database Assets? If the answer is architectural, then there better be no other solution available, such as integration with another system out of your control.
Cheers,
Ben