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.

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.

share|improve this question
    
Martin Fowler recommends Single Table Inheritance (option 3) as the best place to start for table inheritance. You can always use CTI later. –  Neil McGuigan Jul 11 at 3:02

1 Answer 1

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.

share|improve this answer
    
Thank you for this suggestion. I did not think about foreign keys involving multiple columns like this. Should "paypal_payment(payment_id , payment_type" be "paypal_payment(payment_id , payment_type_id" ? Is the reptition of the payment type for every row in the paypal_payments and manual_payments tables not a concern? –  Peter Michaux Dec 31 '13 at 2:06
    
1.Sure, it should be "payment_type_id", sorry for type. 2. I'd call it minor concern - yes, it breaks normalization , but it's quite common practice to avoid data anomaly (one example of similar approach is even in en.wikipedia.org/wiki/Boyce–Codd_normal_form , latest example). On the other hand , for many system it's more or less ok not to enforce everything on db level (say, you are using reliable framework which you believe handle such cases properly and don't let manual details appear in "paypal_payment), so (2) might be good as it is. –  a1ex07 Dec 31 '13 at 15:26

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.