Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the question is which option is best:

A. Record each payment made, this means customers have multiple records.

B. Have only one record per customer and an attribute in the database where there concatenate previous payments, so it's a string that has previous payments.

Note: The DB will have many customers that's why I'm worried about performance with this situation, especially when viewing the information on the DB.

I had never done such a system and therefore would not know how much will be affected the performance of the DB having one of these two configurations and a large number of records.

if there is a better solution is welcome

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

I would use a system where each payment is recorded separately

Typically, you'd want to keep the "balance" consistent over time and this requires them to be recorded individually.

  • the ability to link payments
  • record cancellations separately

I would never concatenate previous payments into one opaque string.

I also wouldn't worry about performance yet: this is premature optimisation. If you do expect 100s of millions of rows, there are other techniques to improved performance. Otherwise, concentrate on data integrity and correctness

share|improve this answer
    
thanks! was concerned about the performance because as I said earlier I had never developed a system like this, I estimate that approximately generate about 10000 records but customers who end their debts are cleared so always maintain a record average, so keep an optimal performance with a number like that is my priority again thank you very much! –  erik Jul 2 '12 at 7:40
    
Never is emphasized for a very good reason. (+1) It is difficult to imagine a scenario in which concatenation would be necessary. Even in such a situation it would be better to store the data separately and simply materialize the concatenation. –  Leigh Riffel Jul 2 '12 at 12:27
    
And of course, you would want related tables - Customers and payments should be in separate tables with a one-to-many realtionship. –  HLGEM Jul 2 '12 at 14:14
add comment

The only time I would ever consider a delimted list is if there is no chance you will want to look at the data separately. Then indeed it is faster. This is a very rare case, however. And in what you are doing, there is an approximately 100% chance you will want to look at individual payments separately.

Delimited lists are harder to query (and updating if someone made a typo, ugh) for individual elements (and generally slower for this than a related table would be) and harder to maintain the data integrity.

Databases can easily handle many millions of records in tables that join with correct indexing and design. If you need more performance at that point typically, you partition the database.

Sit down and read some books on database design and performance tuning. The rules for good design are different in databases than applications. Please take the time to learn them.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.