which option is better for not slow down the performance of queries?

Question

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

Answer 1

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

Answer 2

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.