Which table structure is better

Question

We have to create few tables for the sole purpose of reporting in Oracle.

Option 1

Receivable Table

• RefNo
• Date
• TrnType eg:Tax, Fee, Premium
• Amount

Option 2

Receivable Table

• RefNo
• Date
• Tax
• Fee
• Premium

Note: For a given RefNo all types Tax, Fee and Premium or a sub set of them can exist.

What would be the optimal structure(Tables will have over 100k records)

Answer 1

If your transaction types are explicitly defined and unlikely to be sparsely populated (ie, most records will have values for all 3) then the latter is more likely to be your best option. It also represents the data in a format that's closer to how you'd think about it in reality.

Even if the values can be sparse, "gut instict" makes me still lean toward the column-based approach rather than the row-based.

Answer 2

Neither of those is actually the best (in terms of how DBAs think). The best would be (assuming RefNo is unique and therefore the primary key):

Receivables:
    RefNo
    Date
ReceivableDollarVals:
    RefNo
    Type
    Amount

If RefNo/Date is the primary key, add date to the second table as well.

This allows you to minimize storage space for those rows that don't have all three types (although savings is minimal). Then you use WHERE clauses combining the two tables (or JOINs) to do your queries.

It also allows you to add other types at will without restructuring the database.

However, you need to keep in mind that third normal form is an ideal. It's quite acceptable to violate the rules to gain performance as long as you understand the implications.

100,000 records is actually quite small so, unless you think you're going to be adding more types in the near future, I'd go for your option 2 and use zeros for those values that don't exist. NULLs would most likely make your queries a little more complicated.

Answer 3

The real advantage of the fully normalized version in the first answer comes when the requirements change -- when someone changes the specs so you have to add types beyond the 3 you've identified.

Like discount, refund, whatever. Those changes do happen.

The normalized structure should let you do that more easily, without needing to change either table structure or most of the programs that use the data.

But the normalized structure does require more investment in the beginning -- every new transaction involves inserting into 2 tables, you need to have a check constraint to control the types, etc.

Generally, you'll do better in the long term with the normalized structure. However, with a simple case lik this, you can sometimes get away without normalizing and not have to pay the consequences (at least, nobody has to pay until you're long gone and it's somebody else's problem).

Professionally, reasonable levels of normalization should be your standard strategy and you should require yourself to have very good reasons for denormalizing.

Answer 4

If Table will have over 100k records. Then Option 2 is the good choice. Bcz option 1 slow down the accessing of data.