Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Aug 2001
    Posts
    24

    Unanswered: Table Design (96 Columns Vs 9 Columns)

    Hi,
    I have a requirement of creating a table which has data for 12 months in 8 fields. eg:- Jan-F1, Jan-F2, Jan-F3...Dec-F8. So, a total of 96 columns. I'm looking at alternative approaches.
    How does it compare to creating the same table but with 8 columns F1 to F8 and a month column. So, a total of 9 columns.

    All of the fields are of numeric type.

    There would be 10,000 records in the first case.But, If I convert it into the second case, it would be 10,000 * 12 = 120,000 records. So, any thoughts on which design to choose ???

    Case 1:
    Fields---> JanF1,JanF2, JanF3.....DecF8 (96 Cols)
    Records: 10,000

    Case 2:
    Fields---->Month, F1, F2, F3....F8
    Records: 10,000*12 = 120,000

    Thanks in Advance
    ~ HK

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    you can even get away with just 3 columns: F, M, V, where F will indicate whether it's F1, F2...or F8, M for month, and V for the value corresponding to F and Month. the real question is what's the intended usage of the table? the usage will dictate the structure.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You desperately need to read up on the subject of data normalization before you risk creating reports and applications around a database crippled with a poorly designed schema.

    Option 2 is the way to go.

    blindman

  4. #4
    Join Date
    Aug 2001
    Posts
    24
    That results in total records of 120,000 * 8 = 960,000
    The intended usage is a report based on some search criteria.

  5. #5
    Join Date
    Aug 2001
    Posts
    24
    Originally posted by blindman
    You desperately need to read up on the subject of data normalization before you risk creating reports and applications around a database crippled with a poorly designed schema.

    Option 2 is the way to go.

    blindman
    Actually, the table is a dump from a 3 dimensional cube. That's the reason it has got so many fields. I'm trying to cnvert it into a relational table.

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    that's what i thought, so you don't "desparately" need what blindman suggests

  7. #7
    Join Date
    Aug 2001
    Posts
    24
    Originally posted by ms_sql_dba
    that's what i thought, so you don't "desparately" need what blindman suggests
    So, what do I do ???
    I believe it should be either 96 Columns or it should be a 3 Colums with 960,000 records.

    I'm still undecided about it.

  8. #8
    Join Date
    Oct 2003
    Posts
    7
    Ult. it depends on your reporting needs, but on the surface I'm all for Blindman's suggestion. The biggest reason is that should the number of F's change, your design is still valid.

    My 2 cents

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sure he does. Whatever the format the data is in at the moment, he is trying to convert it to a relational table, and from his original post he seems foggy on the principles of normalization.

    Again, Option 2 is going to give you the best flexibility and performance, and make for easier coding.

    Do NOT go with the 3 column method. I think ms_sql_dba was just trying to make a point about different options available.

    blindman

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by hkamatgi
    Actually, the table is a dump from a 3 dimensional cube. That's the reason it has got so many fields. I'm trying to cnvert it into a relational table.
    The cube is built by a relational database (Usually)...

    What's the source of the Cube?

    Why not get the data from there...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Sep 2003
    Posts
    522
    well, the cube has probably created the agregates of the original data from relational database, so if you go after the actual source you'll have to re-do what the cube has already done for you. in this case (now that we know almost everything about this particular situation) i'd rather prefer to have 12 tables one for each month with fields F1 through F8 and a unionized view on top, if needed. the reports probably should be based on stored procedures, if not functions (prefer the latter).
    Last edited by ms_sql_dba; 10-24-03 at 13:18.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    12 tables one for each month with fields F1 through F8?

    If database design was a religion, we be in a holy war right now.

    Infidel!

    blindman

  13. #13
    Join Date
    Sep 2003
    Posts
    522
    i guess you're missing the point...again. this is not oltp!

    ok osama, calm down

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but it is only 120,000 rows! A pittance to the Almighty SQLServer!

    Thou shalt not needlessly partition tables just to join them together later in UNION queries!

    So sayeth the prophet blindman.

  15. #15
    Join Date
    Sep 2003
    Posts
    522
    poobah-poobah...let me know when you hit your first terrabyte, i know you'll talk differently. and you're not there yet to be called a prophet. and speak english!

Posting Permissions

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