# Thread: Table Design (96 Columns Vs 9 Columns)

1. Registered User
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

~ HK

2. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
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. Registered User
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. Registered User
Join Date
Sep 2003
Posts
522
that's what i thought, so you don't "desparately" need what blindman suggests

7. Registered User
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.

8. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Window Washer
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...

11. Registered User
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 12:18.

12. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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. Registered User
Join Date
Sep 2003
Posts
522
i guess you're missing the point...again. this is not oltp!

ok osama, calm down

14. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
...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. Registered User
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
•