DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Universal data storage method

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    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
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width