horizontal vs vertical table structure

Question

horizontal vs vertical table structure

chidev

Mr or Mrs. 500

Points: 529
More actions
February 12, 2010 at 2:41 am

#216643

I had a discussion about choosing between one vertical table with the following structure
ColumnId INT
ColumnName VARCHAR,
NColumnValue DECIMAL,
CColumnValue VARCHAR
versus multiple horizontal tables with the following structure: The horizontal table will have approx 60 fields each.
ColumnId INT,
Column1 DECIMAL,
Column2 DECIMAL,
Column3 VARCHAR,.....
What are the advantages and disadvantages in terms of performance of horizontal table versus a vertical table?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

wschampheleer SSCertifiable Points: 5504 More actions · Answer 1

There's a lot to say and write about this. Which one is best for you largely depends on how you are going to use it. So you may want to elaborate a bit on that before we start throwing our suggestions at you.

From my personal experience: I'm a data warehouse guy and most of the time we use the column based approach. However, there are times when we have a large number of columns but only a few will be filled at a time. On this occasion, we sometimes switch to the row based approach, creating what is called a 'measure dimension'.

You should also note that it is always possible to move from one to the other as no information is lost in the transformation. The implications of this switch for your application on the other hand, can be quite invasive...

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

chidev Mr or Mrs. 500 Points: 529 More actions · Answer 2

Thanks Willem.

The tables will store historical financial statement data. Some data manipulation might be involved (i.e. SUM, AVG, etc). The front end app will mainly be a consumer. Let me know if I need to elaborate more.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

In the horizontal model, what will the columns represent?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chidev Mr or Mrs. 500 Points: 529 More actions · Answer 4

The fields will be id - year - revenue - net income - income tax - earnings before tax - etc

wschampheleer SSCertifiable Points: 5504 More actions · Answer 5

What is the function of your database: OLTP or data warehouse?