Tall & Narrow or Short & Wide... data storage...

[Ocean West]

I know typically databases are more efficient in some respects with the columns vs rows - tables that only have a few columns vs tables with dozens of columns.

I am trying to decide were to place some data...

I have a "Loan" and each loan generates two different Amortization Schedules -

The difference between each schedule is the repayment interest rate - and each schedule

is active as one determines clients value and the other is for the fund manager. (its complicated)

Should the schedules be in one table? (Short & Wide)

Each record is a monthly payment and i would have duplicate set of columns for each values

starting balance, payment, interest, repaid, ending balance.

OR

Should I append the second schedule into the same table (Tall & Narrow) repurposing the same fields

that already exist - and add one more "TYPE" field that identify's the schedule.

OR

Should I duplicate the table schedule table and keep each set of tables unique for each type of schedule?

I think I am leaning towards the latter two, seems like less maintenance.

Thoughts?

Stephen

[dansmith65]

Should I append the second schedule into the same table (Tall & Narrow) repurposing the same fields

that already exist - and add one more "TYPE" field that identify's the schedule.

This makes the most sense to me.

All other option seem to have duplicate structure (tables or fields). Ask yourself this: what if the client wants 10 different schedules in the future? or 100? That line of thought may not always be appropriate, but it helps me from time to time.

[Fitch]

I agree with Dan: with the tall/narrow structure, you potentially enable the client to create unlimited "what if" schedules to compare.

[Ocean West]

Thanks for the push in that direction, that is the conclusion I am coming too.

I hadn't considered it fully but when printing having the data in one table sub summarized by the "type" field then I can page break it - and it will be only one print request.

In addition having to use the SQL plugin to INSERT/DELETE form one table will be more efficient.

[Ocean West]

After some testing and copious amounts of caffeine the tall & narrow is better didn't even have to add a "type" as I can use the rate value as a key to isolate they type of schedule.

[Rick Whitelaw]

"skinny tables" are best!

[Fitch]

Skinny tables are often best for collecting data, but when it comes to reporting, it's often best to create flat/wide/denormalized data warehouse tables.