-
Universal data storage method
My Data Warehousing friends,
I want to create a motherlode table for all our raw data. Here's a couple ideas on how to tackle it and I'd like to get your thoughts on how best to tackle this problem.
1. Wide, 1 row per collection - Table with 1,000 columns (varchar) and views or materialized views.
2. Long, 1 row per element in a collection - Tag file format.
MOTHERLODE
TAG
DataType
NumVal
CharVal
DateVal
3. Store the data in XML (CLOB), parse to extract
-
Either one sounds like a nightmare -- especially the second option. It sounds like you are trying to avoid defining different tables for different sets of data. Is that correct?
-
Good question.
Maybe this would help.
We collect survey data from several sources (paper, phone, web, in-person interviews, etc.) Essentially the data is very simular (questions and responses). But they can get very complicated with skip patterns, question variations, and response scaling for example.
We have in the past devised elaborate database designs to store any data from any questionnaire.
These plans worked well in theory and on paper, but because of it's complex nature (in order to be all encompassing), new simple projects were reluctant to put their data into this complex structure.
Our plan now is to put all the data from these various collection methods into a simple, common, and flexible place. The "MOTHERLODE".
So far it's working very well on a couple projects. They insert their data via views, and report aggregates via materialized views.
It's working well for a couple projects. I need to know if this is the best way to do this though.
Sorry for the long explanation. Thanks for your patience in reading. Your comments are greatly appreciated.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|