I have inherited a database that has a normalized structure, except for its data tables which are aggressively denormalized. Our system mostly renders grids into a browser, and the previous developer created a system where every grid designed in the web ui gets its own table put into the database, WYSIWYG.
This presents many problems, not the least of which is the amount of dynamic sql required for it to function because generic processes now have to work with N different data tables instead of one data table, and any external loading of data has been made into dynamic sql as well so that one procedure can load into any of these very different tables with very different schemas (as determined by the admin user in the ui). Running sp_BlitzCache on our machine screams about 100% plans being created in the last 24 hours, and we have extremely limited resources in production (1 cpu and 8 gig ram). I cry myself to sleep at night because the team didn't have time to address these issues.
Until now. There is a new business initiative to bring versioning of data into our system. I have brought up the fact this is pretty much impossible with our denormalized data tables that have frequently changing schemas. I may now have a chance to pitch some suggested changes.
I would argue for a normalized system with tables for Grids, Rows, Columns, and Cells. Any changes that previously would have affected the schema, will now simply be entries in the column table for example, instead of physically altering or dropping and recreating data tables.
The two issues I really want to address with my idea are:
The best way to handle cell data that could be any type and small or large. I worked on a previous project where they had cells with varchar(max) values that are converted - and that was ridiculous. I think it is more reasonable to have a pre-determined largest-possible value to feasibly render in a grid and make that the value. Then we also return a bit, HasBigValue, that would allow the user to click on an icon or details button to get the paragraph or whatever huge amount of info might have made max necessary. Stored as a varchar, and we let the mid-tier handle converting to the actual datatype.
Does this seem like a good solution? I am having a hard time conceptualizing anything better. It seems worse to me to have a separate table or column to store each possible data type as its actual type and would require tons of left joins to build out a grid.
How to audit the data for versioning. ATM I have two ideas. We either
(a) have one central table called Audit that any updates/deletes/inserts cause a new row to be created with the same shared uniqueidentifier but a new primary key value, and then a new row gets created in the corresponding table such as grid or row, and marked as the current value; OR
(b) we have a homebrew solution similar to temporal tables (We are on SQL Server 2012) where our data tables and any other important tables all have a history table created where updates and deletes cause that table to have the old value inserted with the same id and with timestamps.
Is there a better approach or a caveat? The big thing I am worried about is what happens if a schema DOES need to change (which shouldn't be as often if we can normalize grids).