One Field, or multiple fields?

[clubcar]

I was working with IT. I suggested tables built for various financial statements. A table for the balance sheet with fields (columns)for alll the accounts (cash, accounts receivable... 80 accounts). A table for income statement with associated fields. This was how my last employer did this (statistical agency). It was a pain to pull data, esp since they had two BS, and two IS, etc.

My IT people are suggesting one table. One submission would be 80 rows for the balance sheet, etc. To get this into Excel for use, I have to transform the data. Not really a problem, actually seems easier than the way the old employer had it.

Is there a preferred method? I know the old employer had extraordinary difficulty building a web tool to access the table for our customers (we are govt)

It seems like you really get a lot of records and must maintain a very good map of the data the way we are going to do it now.

 

[pbaldy]

I believe you're thinking about it the wrong way. A balance sheet or income statement is a report, not a table. You would store the transactions, then summarize those transactions into the appropriate report. I vote for the 1 table approach (1 transaction table anyway). Perhaps more info about your situation would help, as I don't know what you mean by one submission being 80 records.

 

[clubcar]

I was going to make a data table for the balance sheet and income statement (and then several additional schedules to follow). It would show the following fields:

Company, Date, Type (Qtrly,Annual), Version (p or f), Cash, Accounts receivable, derivative assets, .... 80 fields each representing a different account of the balance sheet. (Same with income statement). This would give me 5 to 10 records a year, (4 quarterly, 1 annual, preliminary, final) for each company...There are say 100 fields for each account on the income statement...

IT is designing the database as so:
Company, Date, Type, Version, Account, Amount.

Instead of each account being a field, it is now a record. This means 1 datatable with say 180 records x 5 to 10 submissions a year. So it gets a large number of records, but appears easy to maintain...

which is the better way. Now that I can transform the data (couldn't get my where clause to work for the longest, the transform is easy), the IT solution seems the better way for me. But is this the best way for most other needs?

 

[pbaldy]

The IT department is designing the database properly, IMHO. To have a field for each account and a table for every report would violate normalization rules and be a royal pain to maintain.

 

[clubcar]

That's what I was thinking as I work through this...

I see that it is much easier to transform the data...

Wonder why my old employer, who really has a jammin IT department, went the other way. I can only assume thier IT wasn't so swift when they developed this years ago, or transform was impossible then...

 

[pbaldy]

The old design looks like it was done with "spreadsheet thinking", which is great for spreadsheets but not so good for databases.