Storing summed values

best practicesdatabase-design

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

Best Answer

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.

Related Solutions

Table structure for keeping track of multiple similar accounts

A separate table for each account will be difficult to work with. For example, if you need aggregate values spanning all 30 accounts, you would need to look in all 30 tables. And if you need aggregate values for only a subset, say 5, of those accounts you would need to look in just 5 tables but then you must specify the names of those 5. It gets too darn fiddly fast.

You should consolidate those account tables into a single Accounts table which includes a field to identify which account each row is associated with. And when you want to see all the information for any one account, instead of an actual table, you can dynamically create a virtual table with an appropriate SELECT query.

SELECT [field expression list]
FROM Accounts
WHERE acct_num = 32;

Also think about happens when/if you need to modify the table structures. You really don't want to have to make the same change to 30 tables. BTW, I'm also uneasy about needing to add a new column each year. Aim for a design which doesn't require that type of change. Store information for different years by row rather than by column. A rule of thumb is "columns are expensive; rows are cheap". Try to work cheap! :-)

Storing Phone Numbers – Proper design

The anti-null brigade would insist you normalize further to create the suggested PhoneNumber table. The practical folk would consider your original design perfectly acceptable. I'm in the latter camp.

Normalize until it hurts, de-normalize until it works.

Best Answer

Related Solutions

Table structure for keeping track of multiple similar accounts

Storing Phone Numbers – Proper design

Related Question