Financial Statement table

database-design

I want to ask about database design for financial statement.
I want to combine balance sheet, profit loss, cash flow,and ratio into one big table since each statement is dependent each other.

Here is my table structure:

Id, 
RegistrationNo, 
Year (date),  
ProfitLossNetSales, 
ProfitLossMonthlySales, 
....., 
BalanceReliabilityFinInformation, 
BalanceCashBank, 
BalanceTimeDeposit,
.....,
CashFlowInterestExpenses,
......

Is it okay to have table structure like this (having many columns in one table)?

Update:
In application, the user input balance sheet form and profit loss form (like input data in excel).
The Cash Flow and Ratio form is read only and the value are based balance and profit loss.

Best Answer

It is OK to have many columns in one table. Indeed, this is the normal situation. It is not OK, however, to mix different types of information in a single table.

Each table models a single class of "thing" in the real world. The rules of normalisation guide a database designer to separate classes from one another. Denormalisation (storing several things in one table) is a legitimate technique but should be used sparingly, with caution, and proper risk mitigation in place.

In your specific case you will have to consider what is your business domain. Are your users interested solely in the balance sheet bottom-line numbers for several corporations over several years (e.g. investment and trading point of view), or do they need the detail of how those numbers are calculated (e.g. an accountancy / book-keeping point of view)? For the former the table you mentions would be OK in my opinion. For the latter you need to model individual transactions, double-entry ledgers etc., and calculate your bottom-line numbers from these as required.

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! :-)

Postgresql – Database Normal Form (financial statement)

My team once had to build a database for reporting financials with alternative time windows, including fiscal years, fiscal quarters, and fiscal months. The relationship between dates and these units was documented, but really messy. So here is what we did.

We created a table, call it Almanac, with one row per date. (In reality, we had one row per work shift, but I'm leaving out that complexity for the sake of clarity). We wrote an Almanac generating program that applied the strange rules for fiscal years, quarters and months to each date over a ten year period. The PK for the Almanac table was the Date, although we couldn't use the name Date because it was a keyword. The other columns were items like the fiscal year, the fiscal quarter, and flags like "Company Holiday". Notice that this program had one output, but no inputs.

Once the almanac was built, joining any other table with the almanac over a date field made it trivial to slice and dice by fiscal time periods, regardless of how obscure the rules were. This worked like a charm.

(If you must know, the Almanac generator was written in COBOL+SQL. This was a long, long, time ago, when Oracle was very young.)

Best Answer

Related Solutions

Table structure for keeping track of multiple similar accounts

Postgresql – Database Normal Form (financial statement)

Related Question