Drill Down Functionality for Data Entry in Database

Question

I am building a database that houses financial statement data. Within the database and to maintain the relational model and data integrity the fields are standardized. However, I do not want to lose the supporting data that the standardized data represents. For example, an income statement from one entity may be more detailed than another entity.

Company A
Depreciation = 100
Taxes = 50
Other Expenses = 300
Total Expenses = 450 * This is what is stored in the relational model

Company B
Total Expenses = 400

If I wanted Total Expenses to be the standardized field how could I capture within that field the detailed data that supports that total expense figure?

Many Thanks!

Answer 1

One of my favorite approaches to problems like these in the context of data warehousing usually takes the form of an agile design practice, where the granularity of a system does not need to always be "rolled up" from the lowest level right away, but can be, as stated in the title of the topic "drilled down" into, as the finer granularity details become available.

Assuming you have some table Expense with columns Expense_PK, CompanyName and TotalExpenses, I would simply create a new table, ExpenseDetail, containing columns ExpenseDetail_PK, Expense_FK, Taxes and OtherExpenses, adding a detail record when one was available.

The nuance of doing this is to be stalwart in maintaining the detail record properly. Should the nature of OtherExpenses be revealed to be solely comprised of Adjustments, Dues and Assessments, it is often far better to extend the detail record with the additional atomic columns for each of the now determinant fees than to add another OtherExpensesDetail table to the mix. On the other hand, in Canada anyway, the Taxes column would pose a more than reasonable opportunity for a TaxesDetail table, defined TaxesDetail_PK, ExpenseDetail_FK, GST, PST, so that Company A could provide additional tax breakdowns in the form of Federal and Provincial taxes, where as theoretical Company C could simply be allocated the aggregated Taxes value in the detail record if that was all that was available or were operating in a province that only applied a singular harmonized sales tax ( HST ).

That said, I feel there are many ways of going about problems like these, but this is at least one example of something that is quite versatile and works quite well for what it is.