Post reply

Database table design for huge number of columns

  • January 24, 2008 at 2:47 pm

    #202713

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

    Your help is greatly appreciated.

    Thanks

  • January 25, 2008 at 11:38 am

    #771941

    Here are my inputs

    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

    It is right based on the normalization concept. But it takes more time to retrieve data

    2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier.

    It is wrong based on the normalization concept. But it is faster to retrieve data.

    3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.

    It is a kind of compromization. You may create a cube, or something like that.

  • February 14, 2008 at 4:29 pm

    #778631

    Thanks for detailed reply. That helps.

  • February 14, 2008 at 5:01 pm

    #778640

    What about adding computed columns to a view and running reports on a view? No increased data size and easier reporting.

Viewing 4 posts - 1 through 3 (of 3 total)

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