January 17, 2012 at 2:02 pm
Firstly this is my understanding when designing a table: the bias should always be vertical (rows) rather than horizontal (columns), for datatypes of the same type.
For example
The vertical design
For bank account table i would design the the table like
>>Trantype : Indexed non clustered
[TranDate] DATETIME PRIMARY KEY NOT NULL
[TranType] varchar(100) NOT NULL
[Amount] MONEY NOT NULL
[Reference] varchar(100)
[CreatedOn] DATETIME
So i would insert into this table
2012-01-01, deposit, $100, NULL, 2012-01-01 00:00:00
2012-01-02, withdrawal, $50, NULL, 2012-01-02 22:01:50
2012-01-03, fee, $1.50, NULL, 2012-01-03 01:05:25
The reason to have field like [TranType] is that the READING of the table is assited by indexes or if you a real keen horizontal partitions on Trantype data ranges (or TranDate). This is consider better design I believe.
The horizontal design
[TranDate] DATETIME PRIMARY KEY NOT NULL
[Deposit] MONEY NOT NULL
[withdrawal] MONEY NOT NULL
[Fees] MONEY NOT NULL
[Reference] varchar(100)
[CreatedOn] DATETIME
Having more columns for datatypes of the same data type is not considered best practice. I have been told that some folk say having lots of columns (same data type or not) can be read just as quickly as the vertical design when you use 'partitions', is this correct ?
Please advise, when using a horizontal design is better than the vertical design and the reading of the data is just as quick ??
January 17, 2012 at 2:18 pm
I have never heard of this vertical vs horizontal but I would use your first schema. The second one is just plain strange to me. A transaction does not have a withdrawal, a deposit or a fee. It does however have an amount and a transaction type. This second type would also require some constraint so that you can only have data in one of the three columns. This seems very awkward to me.
Your first example is more in line with the way I would lay this out. Just my opinion.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 17, 2012 at 2:33 pm
Thanks..
I have never heard of this vertical vs horizontal but I would use your first schema.
ME: I wasnt being technical, just using some colour to explain my point of view.
The second one is just plain strange to me. A transaction does not have a withdrawal, a deposit or a fee. It does however have an amount and a transaction type.
ME: True, once again, just making the post easier to understand.
This second type would also require some constraint so that you can only have data in one of the three columns. This seems very awkward to me.
ME: true
January 25, 2012 at 12:33 pm
I have to agree with Sean. Use # 1. First of all, it adheres to the rules of normalization, plus it takes advantage of the fact that you have access to a relational database. Don't "flat file" design your tables for OLTP databases. It just makes your life more difficult down the road.
January 26, 2012 at 6:19 am
Digs (1/17/2012)
ME: I wasnt being technical, just using some colour to explain my point of view.
As a DBA talking to DBAs better to get technical - data modeling language is colourful enough, why not saying "normalized" vs. "de-normalized" design?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 1, 2012 at 6:26 pm
#1 of course, and the TranType should probably be a foreign key from another table where you describe tran types, rather than a varchar field in your transaction table.
February 3, 2012 at 10:46 am
The partition design is different.
For the example above keep the first table definition.
So create a different table for each transaction type, all pointing tho the main transaction table.
The transaction table holds the common properties to the many transactions types.
The others tables holds details properties uniques for that type.
Its enable faster access to the common tables properties (no unions)
and for particular properties for each transaction type (no "transaction_type = x" clauses).
Its also enabled different relations for each type.
Its good to avoid scenarios where lots of columns are left unused for most cases.
And is great where entity relations can "change" depending of a property like tranType.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply