Consolidating separate Loan, Purchase & Sales tables into one transaction table.

Question

INFORMIX-SE with ISQL 7.3:

I have separate tables for Loan, Purchase & Sales transactions. Each tables rows are joined to their respective customer rows by:

customer.id [serial] = loan.foreign_id [integer]; = purchase.foreign_id [integer]; = sale.foreign_id [integer];

I would like to consolidate the three tables into one table called "transaction", where a column "transaction.trx_type" [char(1)] {L=Loan, P=Purchase, S=Sale} identifies the transaction type. Is this a good idea or is it better to keep them in separate tables? Storage space is not a concern, I think it would be easier programming & user=wise to have all types of transactions under one table.

Answer 1

Yes, from my experience this is a better solution. Most programs are more report-oriented than input-oriented. In this case report generation speeds increase dramatically.

Answer 2

It´s a good solution. You can also create three views so that basic access doesn´t change.

By the way.: This is a typical aproach to solve the object relational impedance mismatch.

You can normaly generalise Loan, Purchase and Sale into something like: MoneyTransaction. To get all information you can do some joins, as you did, or degeneralise the whole system, as you are now doing.

Good approach. I can recommend reading http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx

Answer 3

For some reason, I'm unable to comment on your replies, therefore I'm going to comment in this answer space. @Patrick Sauerl> Creating separate views is not neccesary and even undesireable. This is the case of a Pawnshop application where, for example, merchandise can be initially pawned and the customer could opt to convert the pawn by selling it to the pawnshop, which in turn the pawnshop could later sell it to another customer, thus the tran type started out as type 'L'(Loan), converted to a 'P'(Purchase), converted to an 'I'(Inventory), then to converted to an 'S'(Sale), Under certain rules, there are many different sets of possibilities. Depending on the tran type, I'm re-using the same columns for different things, whenever possible, example: for Loans, transaction.main_amount is used for the Principal; for Purchase, transaction.main_amount is used for Purchase Price; for Sale, transaction.main_amount is used for Sale Price. I am storing a history of all previous transactions for the same merchandise as it morphs through different transaction types.