Hi

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
e.g
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 4:
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.

4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.

Your help is greatly appreciated.
Thanks

 Hi

If you're working with a data warehouse, you could just store the calculated values right into the table.

For OLTP, I usually create a view off the base table and calculate on the fly.  I do this mostly to keep the data pages as compact as possible. In addition, all data fomatting is done at the presentation (UI) tier (for example, converting floats to financial amounts)

Thanks Aprato.

I'm using this table for a Windows based application on Visual Basic 6.0, no dataware house is in picture. So what do do u suggest in such a case.

I would go with the OLTP approach where I keep the values I need to calculate the totals and then calculate the totals on the fly within a view or a stored procedure (your choice).  This is mostly because  I don't like to waste space in my tables with some columns that may never get used plus I want my data pages to be as compact as possible.

You could perhaps use computed columns within the base table but I don't like them because from a database design point of view, they're not real columns. They're virtual columns that are calculated as they're referenced.  I think the performance between a computed column in the base table vs calculating vs a view are negligible (I've never discerned a real difference) so, all things being equal,  use a view.  Your app should be using an API into the database anyway rather than going directly at the base table.

SQL Server 2005 has a feature called Persisted Computed Columns which is really cool.   I may be writing a tip on it.

-- Armando 

 Thanks for detailed reply. I might want to use a view as suggested.