Unanswered: Database table design for huge number of columns
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2
Sales tax = 10%
Use Tax = 10%
Share1 = 60%
Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax
So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on..
and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size.
Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 3:
1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or
2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.
3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
It's hard to say without knowing how widespread this calculation is used but as a rule I would not store data that can be calculated. I would create a view or SP specifically for your report or do the calcs in the front end. If you do the calcs in many places then a computed column (2) makes some sense.
It's hard to say without knowing how widespread this calculation is used but as a rule I would not store data that can be calculated.
Yes, but the exception is when the record represents a snapshot in time, as I suspect is the case with this issue. The table needs to record the sales tax amount that was charged at the time the item was sold, so it is not something that should be subject to dynamic recalculation if some of the underlying parameters change.
If it's not practically useful, then it's practically useless.
Agreed. Anything that may vary over time needs to be stored appropriately. The impression I had was that he was proposing all data was to be stored so this would not be an issue. I suggeted removing anything that can be derrived from other data in the row and making it a calc. Your point is worth making and clarifies mine. Thanks.