TransFusion: Should I denormalize Loan, Purchase, Inventory and Sale tables into one Transaction table?

Question

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 transactions?.. 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?.. The app is single-user with max of 10TPHour. There's no high TPM's in this app, it's not a bank, it's a...

Pawnshop Application:

I have separate tables for Loan, Purchase, Inventory & 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; 
                      = inventory.fk_id INTEGER; 
                      = sale.fk_id      INTEGER;  

Since there are so many common properties within the four tables, which revolves around the same merchandise being: pawned, bought, transferred to inventory and sold, I experimented by consolidating the four tables into one table called "transaction" and added the following column:

transaction.trx_type char(1) {L=Loan, P=Purchase, I=Inventory, 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 transaction table where for example:

transaction.main_amount DECIMAL(7,2) 

in a loan transaction holds the pawn amount, in a purchase holds the purchase price, in inventory and sale holds sale price.

This is clearly a denormalized design, but has made programming a lot easier and improved performance. Any type of transaction can now be performed from within one quantum-style screen, without the need to change to different tables. The key is that the same merchandise navigates through different types of transactions over time.

As the merchandise morphs into different transaction types, I'm storing its history of all previous transactions performed with it, from the time it first made its appearance at the pawnshop through the sale of it to another customer, which could again make its way back into the pawnshop. When merchandise is pawned, it is micro-tagged with an invisible id number (the original ticket number). Example of multiple transactions: Customer A pawns it, makes couple of interest payments, decides he wants to sell it to pawnshop, pawnshop buys it, puts it in inventory, sells it to Customer B, who after a couple of months needs money and pawns it at same pawnshop and ... each transaction gets recorded with a unique serial transaction number and several fields are re-utilized depending on what type of transaction is taking place.

So, several columns are used for multiple purposes, depending on transaction type. Money columns could be credit, debit or suspense depending on tran type.

If anyone has developed a similar type app, I would be interested in hearing from you. Perhaps I could obtain some valuable input into doing this the best way possible, Thank You!

I WOULD LIKE TO HEAR FROM ANYONE WHO HAS ENCOUNTERED A SIMILAR SITUATION WHERE THEY HAVE CONSOLIDATED SEPARATE TABLES INTO ONE TABLE.

Answer 1

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?

Answer 2

As far as I know, this is not normalized if that is the actual question (it isn't clear). Specifically, you are violating first normal form because it is not the case that your table represents a single "relation" or entity and that there exists some attributes not applicable to some tuples. I.e., I highly doubt that loans, purchases, inventory and sales all have exactly the same attributes. I would bet that you have columns in your table that only apply to loans or only apply to sales and so on.

So, given that you have accepted that it is denormalized, what could go wrong?

• It will be more difficult to create dependencies. For example, if it is the case that say a purchase is associated with an inventory item or a loan there is no easy means to do that. You would need to add another column that only relates to some types of data.

• It will be harder to enforce data integrity. For example, if loans cannot have negative amounts but purchases can because of discounts or refunds, it will be cumbersome to enforce that.

• You will create a hot spot for data. For example, if there are lots of loan transactions but only a few inventory transactions, queries on inventory transactions will be needlessly slow because of the traffic from the other types of transactions. In addition, if you want some loan, purchase, inventory and sale data, you'll have to query the table multiple times.

Generally, problematic schema designs rear themselves during reporting/data analysis/querying. The more cumbersome the queries to get your information, the obvious it is that the schema does not match the business need. If you have analyzed the business needs and are taking a calculated risk based on that analysis, then my only suggestion would be to document your design and design rationale for the next developer.

Answer 3

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).