locked
Is it the best practice to use Month Name as name of the column? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    We need to store many data elements (like Balance, Cost,  Budget etc) for each account  for varying time periods (month end, month to date, quarter end , quarter to date etc)  for at least 5 years before archiving them.

    To achieve the best performance for reporting, what would be the good design for this table (there will not be any transactional queries against table. It is meant to be for reporting purpose)?

    Is it good to use a Time Dimension table and add the Dimension key to the main table?

    OR

    Have 2 tables (monthly and quarterly).

    Monthly Table  will be having Account No, Amount Type, Year, and Month 1 through Month 12 as column names

    Quarterly Table  will be having Account No, Amount Type, Year, and Quarter 1 through Quarter 4 as column names

    Please suggest the best design for this requirement.

    Thanks!

    Monday, January 27, 2014 4:08 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Having two tables will be redundant. There should be one table with  columns  Account, Balance, Cost,  Budget etc ... Date(Balance as of date) .

    Then you can have views that pull your specific reports whether it is monthly or quarterly based on the dates.



    You are looking at it from a displayed report prospective.. and I don't think that is the best way to store data. If you store it as suggested you can then display it however you want.
    • Edited by Don.O Monday, January 27, 2014 5:01 PM
    • Proposed as answer by Don.O Monday, January 27, 2014 6:11 PM
    • Marked as answer by Mike Yin Wednesday, February 5, 2014 2:35 PM
    Monday, January 27, 2014 4:59 PM