Table CUSTOMERS (CUST_ID,...), table REPRESENTATIVES (REPR_ID,CUST_ID,....) with 1:m (optional) relation
so 1 customer may have none,1 or many representatives
there is also table PAYMENTS
Payments can be done to any combination of: customer and related representatives
For example:
customer CUST1 has related representatives REPR1,REPR2
There could be 3 payments (one for each CUST1,REPR1,REPR2)
or 2 payments (REPR2,CUST1 for example)
or 1 payment (CUST1 for example)
now what is the best design for payments according to normalization:
- PAYMENT (PAYMNT_ID,KIND_ID,WHO_ID,..)
with KIND_ID = 'C' for CUSTOMERS, 'R' for REPRESENTATIVES)
and WHO_ID = CUST_ID or REPR_ID respectively
-or-
- tables PAYCUST (PAYCUST_ID,CUST_ID,...) and PAYREPR (PAYREPR_ID,REPR_ID,..) for each group of data
never managed to solve this
I insist on this because I've met the same problem on many other situations.
TIA