I have a client doing scientific research. The current database design is 3NF but very few transactions are happening. There are several “pipelines” that add new data to the tables and scientists might make an update to some data or “discard” some data as erroneous but overall very little is happening transaction wise.
However they do have a “reporting” aspect to their work and that is what the scientists are doing most of the day. Looking at the data (genetics stuff) and seeing if these mutations have been seen before or what are start and stop markers etc.
For example one of their “reporting” or data analysis web pages calls a view. That view takes 4+ minutes to run. When I look at that view it’s basically gathering all the data from the different 3NF tables and flattening it out to 1NF to then display.
So here are my thoughts / questions:
-
Could 3NF and 1NF tables exists in the same db and same schema? I know that you could literally do it but would that be wise / problematic? Is it some sort of “anti-pattern” to mix and match like that?
1a. If you did that would you modify the pipelines to put the new incoming data right into the 1NF tables or would you still let the pipeline insert to 3NF and then have a trigger or ETL process etc. do the 1NF table updates?
1b The thought here is that if a table were to exists in 1NF then we could just do a scan from that table rather than all the joins, cte, sub queries etc. that are currently in the long running view.
-
Should I migrate the whole operation to 1NF with the understanding that the few transactions there are will be slower but %90 of everything else (sp, vw, fx, etc) will be less complex and faster.
2a. If I did migrate everything to 1NF would you still have “staging” tables that the pipeline writes the new incoming data to then an ETL process loads the new data into the 1NF tables?
Overall I’m wanting to plan a long term solutions. Sure I can cut this 4 minute view down in time but long term what should we be thinking about especially as the volume of data continues to grow. (%20 increase last year alone).
TIA