Approach for flat data structure vs normalized

[Ocean West]

Monthly i receive by a third party an excel file of "statements"

 

This file is FLAT and a record is a monthly billing statement 

Name, Rent, Gas, Electric, Storage, etc...

 

In total there are about 2 dozen columns of items that are essentially "line items" to the statement. 

 

And every so often there are "adjustments" that need to be made but attributed to a specific item such as "rent" or "gas"

that would mean that in a flat structure another field for each item.

 

My instinct is to actually take the flat file and process it to generate actual line items in a separate table for each statement. 

Then any adjustments it would just be a new record for any given item.

 

Curious your reason pro or con for one method or another?

 

[Matthew F]

I think the line item approach would give maximum flexibility.  

 

If the adjustments aren't too complicated, then I'd consider placing them in a designated field on your line item, not add them as an extra record.  i.e. a line item would consist of Name, Category (e.g. Rent), Amount, Adjustment, and Total (the latter being calculated field = Amount + Adjustment).  In this way there would always be one record per item per month, not a variable number.  

[Rick Whitelaw]

Then there's the issue of history . . .

[Ocean West]

Yes this is the approach I took however the fields I set up were 'original' and 'actual'. And in my looping script I set the amount value to both fields then any adjustment is a modification of the 'actual' field preserving the the original.

Also added a difference field that compares the two.

So far it's working well.

Just have to make a temp table for import as it is now still have the duplicate fields directly in the statement table. And purge it before each import.