No two rows of different tables have same primary key?

Question

One of my tables order has one to many relationship with two other tables PaymentMethod1 and PaymentMethod2. I have created separate "payment method" tables since they have completely different attributes so that I can avoid nulls. But, a particular row in order will link to a particular row of any one table -- PaymentMethod1 OR PaymentMethod2. This requires the primary key values to be unique in both of these tables, i.e. no two rows in PaymentMethod1 and PaymentMethod2 can have same primary key.

Am I right in chosing primary keys for PaymentMethod1 and PaymentMethod2 in this fashion? If yes, how do I implement it?

Answer 1

MySQL doesn't have a built in method for handling this type of polymorphism.

One solution is to have two foreign key columns in the order table, one for the first payment method and one for the second payment method. Allow NULL, and only fill in the appropriate one.

This method allows you to continue to use foreign key constraints, but it's not completely normalized.

Another method is to take the payment method key out of the order table and instead have two junctions tables, one for each payment type. Use those junction tables to join the order table to the appropriate payment type. This method also allows you to use foreign key constraints, and is more normalized than the previous method (no NULL values needed), but the foreign key relationships (and the queries) are a bit more complex.