Results 1 to 3 of 3

Thread: Design issue

  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Design issue

    Hi All,

    We r enhancing a system for one of our client. We are using SQLServer. Our client's existing system have CubeDetail table with below structure.

    CubeDetails
    -----------
    CubeDataId
    ContractNo
    ContractName
    ContractMemId
    TagId1
    CubeId1
    CubeSize1
    TagId2
    CubeId2
    CubeSize2
    TagId3
    CubeId3
    CubeSize3
    TagId4
    CubeId4
    CubeSize4
    DateCasted
    EnteredBy
    Status
    TimeStamp

    One contract can have minimum of 2 cubes and maximum of 4 cubes. Is the above table structure normalized or shoould it need to be amended like below.

    Contract
    -----------
    CubeDataId
    ContractNo
    ContractName
    ContractMemId
    DateCasted
    EnteredBy
    Status


    CubeDetails
    -----------
    CubeDataId
    TagId1
    CubeId1
    CubeSize1
    TagId2
    CubeId2
    CubeSize2
    TagId3
    CubeId3
    CubeSize3
    TagId4
    CubeId4
    CubeSize4
    DateCasted
    TimeStamp


    Thanks and Regards,
    Sagar

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    it depends....

    if it is OLTP then I'd say don't even think about first version (not all tables need to be normalized, but this is little bit too much).


    If it is ROLAP or HOLAP source , then first version is common - it follows star schema.


    I'm not sure how deeply you know OLTP and OLAP, but ....

    I stick into bucket OLTP all kinds of read-write databases (I knows it's incorrect, but...) - Simply say any kind of relational database, where user inputs data into and gets data from. NORMS 1,2 and usually 3 should be followed.

    If we talk about Data Warehouse and ROLAP or HOLAP (MOLAP does not care so much about the source), then it is READ only database and your main goal is to get your data from server to user (analyst) quickly. That's why you don't follow norms at all (it's read only source) and use STAR or SNOW FLAKE schema (star is one big table with smaller lookup tables, SNOW FLAKE can have more bigger tables and lookup tables have other lookup tables - data schema really looks like a snow flake). So you can have first and also second data structure version.


    if I'd go into small sample:

    imagine that you own AMAZON, you have huge database of customers and when what and why I bought. It is classic relational database and you will have CUSTOMERS table and PURCHASE table and PRODUCTS table and.....

    Because you have lot of analysts who want to work with the data and analyze who buys what and when, you don't give them access to online database, you create special data structure and import the data let's say once a month from real time database. Your goal is to simplify the database and speed it up, so they won't wait 2 hours for query "how many customers from California we have who buy books for kids". You will probably have (maybe not) one big ORDER table and that table will have all details about each order (with some small lookups). So each ORDER will have let's say customer's ZIP code instead of lookup into customer table..... why? cauze you want the answer quickly... you can go different way and even more ways at the same time, but it is ROLAP-HOLAP-MOLAP discussion ...





    does it help?


    jiri
    Last edited by playernovis; 02-10-03 at 00:54.

  3. #3
    Join Date
    Feb 2003
    Posts
    2

    Thanks

    Hi jiri,

    Thanks for the info. It did help me.

    Rgds,
    Sagar

    Originally posted by playernovis
    it depends....

    if it is OLTP then I'd say don't even think about first version (not all tables need to be normalized, but this is little bit too much).


    If it is ROLAP or HOLAP source , then first version is common - it follows star schema.


    I'm not sure how deeply you know OLTP and OLAP, but ....

    I stick into bucket OLTP all kinds of read-write databases (I knows it's incorrect, but...) - Simply say any kind of relational database, where user inputs data into and gets data from. NORMS 1,2 and usually 3 should be followed.

    If we talk about Data Warehouse and ROLAP or HOLAP (MOLAP does not care so much about the source), then it is READ only database and your main goal is to get your data from server to user (analyst) quickly. That's why you don't follow norms at all (it's read only source) and use STAR or SNOW FLAKE schema (star is one big table with smaller lookup tables, SNOW FLAKE can have more bigger tables and lookup tables have other lookup tables - data schema really looks like a snow flake). So you can have first and also second data structure version.


    if I'd go into small sample:

    imagine that you own AMAZON, you have huge database of customers and when what and why I bought. It is classic relational database and you will have CUSTOMERS table and PURCHASE table and PRODUCTS table and.....

    Because you have lot of analysts who want to work with the data and analyze who buys what and when, you don't give them access to online database, you create special data structure and import the data let's say once a month from real time database. Your goal is to simplify the database and speed it up, so they won't wait 2 hours for query "how many customers from California we have who buy books for kids". You will probably have (maybe not) one big ORDER table and that table will have all details about each order (with some small lookups). So each ORDER will have let's say customer's ZIP code instead of lookup into customer table..... why? cauze you want the answer quickly... you can go different way and even more ways at the same time, but it is ROLAP-HOLAP-MOLAP discussion ...





    does it help?


    jiri

Posting Permissions

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