Hello!
I'm designing a database (Oracle 9i) for payments registration system, obviously it will hold a VERY big amount of data, which is going to be changing constantly. There are so called “list payments”, which have several beneficiaries bound to it, additionally for transferring money to some beneficiaries a commission fee might be applied, which is to be bound to the payment, or rather to certain beneficiary, as well. “List payment” has about 6 properties, which are common for all beneficiaries. Commission fees and “list payments” beneficiaries properties are very similar to those of payments. Firstly I've been planning to create the following structure: single self-referenced table PAYMENTS for all payments types, including “list payments”, payment beneficiaries and commissions fees. Field PAYMENTS.ID would be a unique primary key to identify objects stored into PAYMENTS table. Records representing payments beneficiaries would have field PAYMENTS._ID filled with PAYMENTS.ID value of master “list payment” it is bound to. If commission fee would to be applied to the money transfer to certain beneficiary, then it would be stored in the same table PAYMENTS, with PAYMENTS._ID equal to PAYMENTS.ID of beneficiary. My question is how is such structure going to affect performance, is not it better to store beneficiaries in the separate table from point of view of performance (coz from point of view of convenience it is better to store all the data in the same table... at least for me :) )? Which approach would be preferable and why? Any advice will be appreciated.

This is really an issue for generic database design, the platform doesn't matter.

If I understand you, a "list Payment" is a payout to a group of individuals. Each payment might or might not have a commission attached to it. If so your structure would be like this:

Beneficiaries
BeneficiaryID (Primary Key)
Other info about beneficiary

MasterPayment
MasterPaymentID (PK)
PaymentDate
PaymentSource
Other info specific to the total payment

Payments
PaymentID (PK)
MasterPaymentID (Foreign key)
BeneficiaryID (FK)
PaymentAmount
Other info about individual payment

Commissions
CommissionID (PK)
PaymentID (FK)
CommissionRate
Other info about commission

Hello Scott!
At first place I want to thank You for the prompt reply.
I'm trying to figure out which structure is better in terms of performance as soon as it is the critical issue - one table containing all the data joined with self-references. Or two tables one of which will hold payments data and the other beneficiaries data.

The thing is that all four - simple non-“list payment”, “list payments”, payments commissions and beneficiaries data structures are very much similar (they have account numbers, sums to transfer, payment currencies etc.). That's why I am considering an option of storing all the data in the same table bound together by means of self referencing foreign keys.

1st option:

[Table Payments]
PaymentID (PK)
<PaymentData>
<CommissionData>
<BeneficiaryData>
MasterPaymentID (FK) - points to “list payments” PaymentID (PK) in the same table in case if current record is beneficiary data, or to beneficiary PaymentID (PK) in records representing payments commissions. And in case if the record is “list”or a simple payment it obviously will be NULL.


2nd option:
[Table Payments]
PaymentID (PK)
<PaymentData>
<CommissionData>

[Table Beneficiaries]
<BeneficiaryData>
MasterPaymentID - points to “list payments” PaymentID (PK) in the “Payments” table
CommissionPaymentID - points to “commission payment” PaymentID (PK) in the “Payments” table.

What I'd like to know is which design is more suitable for the system which is targeted to processing HUGE amounts of data, which will perform better? I'm trying to conduct a test which would give me an answer to the question myself, but I really need some “third” opinion from an expert or a person, who had an experience of implementing/using similar system in production environment.

I told you what my recommendation would be. What you need to do is read up on the rules of normalization. This is a KEY design guideline for relational databases. One of the main principles of relational databases is to reduce or eliminate redundancy. The structure I gave you is a more normalized one.

Thank You very much for the recommendation, I will definitely take it into account.

Hey Vladimir, Scott's answer was excellent...

I'll chime in and say that while yes, performance is important, so is ease of maintnenance and future growth. One giant self joining table will not make that happen for you.

In the future, you can always partion your data to improve performance.