title: Structuring Databases for Financial Statements
body: <p>I am looking for the best way to structure my database. I have quarterly financial statements for 1000’s of companies from 1997-2012.  Each company has three different statements, an income statement, a balance sheet, and a cash flow statement. </p>
  
  <p>I want to be able to perform calculations on the data such as adding up each quarter to get a yearly total for each line item on each statement.</p>
  
  <p>I have tried two ways so far:</p>
  
  <p>1)  Storing each line item for each statement in it’s own table i.e. Sales would be one table and have only sales data for all companies I am tracking, with company as the primary key, and each quarters data as a separate column.  This seems like the easiest way to work with the data, but updating the data each quarter is time consuming as there are hundreds of tables.</p>
  
  <pre>
  Sales Table
  Company q32012  q22012  q12012
  ABC Co.  500    100 202
  XYZ Co. 230 302 202
  </pre>
  
  <p>2) The other option which is a little easier to update but harder to work with the data is to have a separate table for each company for each statement. For example, the income statement for Royal Bank would have it’s own table, with the primary column being the line item.</p>
  
  <pre>
  Income Statement for Royal Bank
  Line_Item   q32012  q22012  q12012
  Sales            
  Net Profit           
  </pre>
  
  <p>The problem here is when I try to annualize this data, I get a really ugly output due to the group by</p>
  
  <pre><code>SELECT    
  (CASE WHEN Line_Item = 'Sales' THEN SUM(q4 + q3 + q2 + q1) ELSE '0' END) AS Sales2012, 
  (CASE WHEN Line_Item = 'NetProfit' THEN SUM(q4 + q3 + q2 + q1) 
        ELSE '0' END) AS Inventories2012
  FROM            dbo.[RoyalBankIncomeStatement]
  GROUP BY Line_Item
  </code></pre>
  
  <p>Any help would be appreciated.  </p>