tables for different sources of payments Selling software products. I have a table "licences". Each software licences is created due to a payment. I am creating tables to accept payments from different sources (e.g. manual or PayPal.) I'm having trouble creating the tables for the payments and the foreign keys to see the association between the licence and a its payment and to ensure data integrity.

OPTION 1

My initial design was to have a single payments table and then more details tables for the various payment types. The common columns go in the payments table and the uncommon columns go in the details table.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
details_table
details_id

paypal_payments
---------------
id
ipn
payer_status
payer_email
...

manual_payments
---------------
id
notes
...


The details_table could have a value "paypal_payments" or "manual_payments" and is an indication of the payment type. With this design, a foreign key on details_id is not easy to make, enforce, or have cascade.

OPTION 2

To rescue the design in option 1, I thought about moving the foreign key to the details tables.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
details_table

paypal_payments
---------------
id
payment_id
ipn
payer_status
payer_email
...

manual_payments
---------------
id
payment_id
notes
...


This does not seem ideal because a paypal payment and a manual payment could both refer to the same payment which is nonsense.

OPTION 3

I've thought about just pushing everything into one payments table and allowing NULL depending on the source of the payment. A trigger procedure could ensure that certain columns are not NULL if the particular type requires it.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
type
ipn
payer_status
payer_email
...
notes
...


I know this is the inelegant sledgehammer approach and I don't want to do it.



What to do? I've encountered this issue in many thought experiments and have always been stumped.

Thanks.
I normally go with modified version of (2). 

payment_type (payment_type_id, PK(payment_type_id) );
payments(payment_id , payment_type_id, [other_attrubutes],
  PK(payment_id), UNIQUE(payment_id,payment_type_id), FK(payment_type_id) 
);

paypal_payment(payment_id , payment_type_id, [paypal_attributes], 
 PK(payment_id), FK(payment_id , 
payment_type_id)   REFERENCES payments(payment_type_id,payment_type), 
 CHECK  (payment_type_id='paypal')
)
manual_payment(payment_id , payment_type, [manual_attributes], 
 PK(payment_id), FK(payment_id , 
payment_type_id)   REFERENCES payments(payment_type_id,payment_type), 
 CHECK  (payment_type_id='manual')
)


Check constraints on each detail table ensures that manual table details never go into paypal table, and vice versa. Unique constraint allows detail tables to have a reference to 2 columns.

(3) is also not so bad, but I don't like exclusive arcs - adding new payment type will require changes to payments, and trigger used to enforce that only one of columns is not null.