Take the 2-minute tour ×


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.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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 approach to solve the object relational impedance mismatch.

You can normally 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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
@Frank - you're unable to comment because you're using one of your many accounts that has low reputation (and therefore prevents you from commenting). Multiple accounts are a pain to deal with, as you're seeing, and they're frowned on at SO. Please refrain from doing that. Ask the admins to merge your various accounts. –  Michael Petrotta Jun 15 '10 at 1:42
@Petrotta - To my knowledge, I only have two accts, Frank Computer & Frank Developer. I dont have a problem with these being merged. I'll even register!.. I have a yahoo business email address, but dont know how to obtain the OpenID for it, if it already exists? –  FrankComputerAtYmailDotCom Jun 15 '10 at 2:43
you had six accounts; a few named "Frank Computer" and a few named "Frank Developer". Don't worry; the site admins just merged them. FYI, if you want to use the '@' to get someone's attention in a comment, use the first part of their name - @Frank or @Michael. The last name doesn't trigger notifications. –  Michael Petrotta Jun 15 '10 at 3:23
I can't really help you with OpenID; I just used my Google account and was good to go. You might look here. If you keep having problems, ask for help on SO's sister site, Meta. –  Michael Petrotta Jun 15 '10 at 3:27
add comment

Your Answer


