I need to build out a warehouse structure that is populated by periodic feeds consisting of a mix of scraped data, authority sources, and primary sources. In order to prioritize which data should be used for a data mart, I need to keep track of each data point's creation (when did we get this data?), update (when did the data change?), and existing match (when was the last time this data showed up?). From my research it looks like 5NF is the level of normalization I should use.
For example, I may get a feed which gives Student Names along a list of their favorite classes and rank order. Another feed may come in with the same data, but might have an incomplete list of classes, or lack rank order. Every feed would come in with a unique and reliable student identifier
I would was thinking there would be at least three tables for just that data:
- Student (s_id, name)
- Student_Class (s_id,class_id)
- Student_Class_Order (s_id,class_id,order_num)
Each of those would also have columns for isDeleted, created_time, created_feed_id, modified_time, modified_feed_id, matched_time, matched_feed_id.
What kind of structure would make the most sense? It seems like any new combination of datapoint should be inserted, and I should not have updates (except for a soft-delete flag). Any ideas?