Should I denormalize Loans, Purchases and Sales tables into one table?

Based on the information I have provided below, can you give me your opinion on whether its a good idea to denormalize separate tables into one table which holds different types of contracts?.. What are the pro's/con's?.. Has anyone attempted this before?.. Banking systems use a CIF (Customer Information File) [master] where customers may have different types of accounts, CD's, mortgages, etc. and use transaction codes[types] but do they store them in one table?

I have separate tables for Loans, Purchases & Sales transactions. Rows from each of these tables are joined to their corresponding customer in the following manner:

customer.pk_id SERIAL = loan.fk_id      INTEGER; 
                      = purchase.fk_id  INTEGER; 
                      = sale.fk_id      INTEGER;  

Since there are so many common properties among these tables, which revolves around the same merchandise being: pawned, bought and sold, I experimented by consolidating them into one table called "Contracts" and added the following column:

Contracts.Type char(1) {L=Loan, P=Purchase, S=Sale}

Scenario:

A customer initially pawns merchandise, makes a couple of interest payments, then decides he wants to sell the merchandise to the pawnshop, who then places merchandise in Inventory and eventually sells it to another customer.

I designed a generic table where for example:

Contracts.Capital DECIMAL(7,2) 

in a loan con开发者_如何学运维tract it holds the pawn principal amount, in a purchase holds the purchase price, in a sale holds sale price.

Is this design a good idea or should I keep them separate?

Your table second design is better, and, is "normalised".

You first design was the denormalised one!

You are basiclly following a database design/modelling pattern known as "subtype/supertype" for dealing with things like transactions where there is a lot of common data and some data specific to each tranasaction type.

There are two accepted ways to model this. If the the variable data is minimal you hold everthing in a single table with the transaction type specfic attributes held in "nullable" columns. (Which is essentially your case and you have done the correct thing!).

The other case is where the "uncommon" data varies wildly depending on transaction type in which case you have a table which holds all the "common" attributes, and a table for each type which holds the "uncommon" attributes for that "type".

However while "LOAN", "PURCHASE" and "SALE" are valid as transactions. I think Inventory is a different entity and should have a table on its own. Essentially "LOAN" wll add to inventory transaction, "PURCHASE" wll change of inventory status to Saleable and "SALE" wll remove the item from inventory (or change status to sold). Once an item is added to inventory only its status should change (its still a widget, violin or whatever).

I would argue that it's not denormalized. I see no repeating groups; all attributes depending on a unique primary key. Sounds like a good design to me.

Is there a question here? Are you just looking for confirmation that it's acceptable?

Hypothetically, what would you do if the overwhelming consensus was that you should revert back to separate tables for each type? Would you ignore the evidence from your own experience (better performance, simpler programming) and follow the advice of Stackoverflow.com?