Unanswered: Table Design (96 Columns Vs 9 Columns)
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 ???
Fields---> JanF1,JanF2, JanF3.....DecF8 (96 Cols)
Fields---->Month, F1, F2, F3....F8
Records: 10,000*12 = 120,000
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.
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.
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.
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).