Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to create a database to store values for Profit & Loss statements and I'm wondering what the best way to structure this would be. Specifically, amounts that are are equated from other numbers such as: operating income = gross income - operating expenses, and also operating expenses = wages + rent.

Should there be a field for each value, even if it is a result of an equation, or should I only keep the non-equated values, and equate the others when querying or pulling the data into a report?

In the former I would have to more fields and there's potential for the summed numbers to be entered incorrectly, the latter seems like it would have a complex structure, and would need complex queries. Keep in mind that the P&L statement I'm actually working with has many more accounts and summed accounts.

Is there a best practice for this, or place to get more info on this? I really appreciate the help

share|improve this question
    
Are you intending to store transactions or just the P&L summary data? if you are storing transactions, I'd have a table to describe each account, anf a table.for the transactions with a foreign key to the accoints table and debit and credit fields to store currency amounts. –  Max Vernon Oct 25 '12 at 3:26
    
It would not be for storing transactions, only P&L summary data. –  user1290426 Oct 25 '12 at 3:29
add comment

3 Answers

The correct answer is: it depends. Here are factors you should take into account:

  • is there any possibility of the figures changing? Accounts are sometimes restated. If you had a summary table you would have to change that as well if there was a restatement.
  • you say you are trying to create a database. Does this mean you already have an OLTP (online transaction processing) database and the new one would a data warehouse? If so then you need to research data warehouse schemas like the snowflake design and, more importantly, decide the key values you are tracking. One way of describing a data warehouse is one huge summary table. Reporting and analysis are often easier with summarized data.
  • or maybe you just want to add some tables to summarize the data and keep it in the same database as the source material. If so there are many other tools like views, materialized views that could be used that are dynamic.
  • there is a trade off between summarizing data. Once you take a snapshot you have to make sure it stays in sync with the real data. Or you can build a structure on top of the original data with views.
  • if the amount of data is small or can be broken into smaller parts you could fill a summary table with a procedure that marks each entry when it was created. Then if fiscal 2011 is restated due to whatever you just delete all fiscal 2011 entries from the summary table and rebuild with your procedure. This may or may not pass auditor standards depending on your location, your industry and your companies practices.
share|improve this answer
    
Thanks for the answer. From what you're saying it looks like I'm trying to make something like a data warehouse since overall, I'm looking to make reporting and analysis easier. –  user1290426 Oct 26 '12 at 4:02
add comment

Is there a best practice for this?

This is a classic time vs space trade-off. You either calculate the values up front, storing them permanently, taking up extra space in your database, but are able to retrieve the values more quickly. Or you only store base values in the database, and have to perform calculations on the values whenever you need them, which may take more time, but requires less space.

I’m not sure that there’s a best practice for this, it all depends on how your company operates.

FWIW, we do the latter. We have a database that stores the base values. We also have a reporting database that pulls the base values in and performs calculations on them to produce reports. The values are held in a table temporarily whilst the report is being produced. When the next report is produced, the table is wiped and the next set of values is calculated. Which can take time depending on the amount of data being processed i.e. YTD etc.

Previously calculated figures can change sometimes due to rate fluctuations and back valuations etc, so the business teams prefer to know that the values are recalculated each time.

share|improve this answer
    
Thanks for the example, I think that is what I'll end up trying to do. –  user1290426 Oct 26 '12 at 4:10
add comment

Since your question has been addressed, I want to bring up the benefits of clustered index views when dealing with these design issues. We have a system where we collect billing information every 5 minutes for over 20,000 customers. At the end of the month the reports have to aggregate all this data to run billing reports. With a clustered index view you can aggregate the data up ahead saving SQL Server the need to do it every time a query is ran. You might want to look into this. It reduces hours to seconds if done properly.

I know this wasn't directly what you asked for, but I'm really excited about the time these views save and it fell in line with what you were asking.

share|improve this answer
    
you're right, its not what I was asking for, but it does sound pretty interesting. Thanks for adding –  user1290426 Oct 26 '12 at 4:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.