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.

enter image description here

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

score 3 · Answer 1 · answered Mar 05 '15 at 14:31

This is a limiting way to solve the problem. What happens when you add a new payment type? A new table goes up and your JOIN condition on all of your queries that utilize Orders and Payment Types have to be rewritten.

If you might put in more payment types in the future, consider having a single payment type table that will hold the payment type and attributes that are ALWAYS common to all of your payment types, then a separate attribute table that will hold the attributes in rows based on the payment types. This way when you scale things up you increase record count, not object counts (tables and fields).

If you stay with what you have you will need to either write the order number into your Payment Types table so you can join to them, or you will need to write the Payment Type Type as well as the Payment Type Primary Key to the Order table. Something like:

Order Number | Payment Type Type | Payment Type Key
    1              PaymentType1           5
    2              PaymentType2           5
    3              PaymentType1           5

Then:

SELECT 
    o.Number,
    COALESCE(pm1.key, pm2.key),
    CASE WHEN o.PaymentTypeType = "PaymentType1" 
        THEN pm1.pm1_attr1
        ELSE pm2.pm2.attr3
        END as "Friendly Attribute Name"
FROM ORDER o
   LEFT OUTER JOIN PaymentMethod1 pm1 ON
       o.PaymentTypeType = "PaymentType1" AND
       o.PaymentTypeKey = pm1.key
   LEFT OUTER JOIN PaymentMethod2 pm2 ON
       o.PaymentTypeType = "PaymentType2" AND
       o.PaymentTypeKey = pm2.key

You can see that may get ugly pretty quick. Especially when you add a new PaymentType into the mix a year from now.

I'd avoid the [EAV](http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) model for an "attribute table". You ever try querying against EAV? It gets nasty quickly. If you're going to have common columns in one table and branch off to another table (again, not EAV), then you're still branching to multiple tables, and you've lost your foreign key constraints again. The design you suggest is a common pitfall. — Marcus Adams, Mar 05 '15 at 17:34
@JNevill, sorry for the late response. Was having exams. Can you please elaborate on the `Attributes` table you mentioned in your suggestion? How can I design **one** `Attributes` table that will hold all the extra attributes of different payment types? Don't you think there'll be considerable amount of `null` values in it (since the table consists of all possible extra attributes, some of them might be `null` for a particular payment type). Also, I might need to add an extra attribute in `Attributes` table if I'm allowing a new payment method in future. — Nikunj Madhogaria, Mar 31 '15 at 12:32

Marcus Adams · Accepted Answer · 2015-03-05T17:37:19.997

2

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.

edited Mar 05 '15 at 17:37

answered Mar 05 '15 at 14:35

Marcus Adams

53,009
9
91
143

here the `order` table will have a 1:1 relationship with each of the junction tables, right? – Nikunj Madhogaria Aug 13 '15 at 18:38
For the second solution, the order table would have a 1:0..1 relationship with each of the junction tables. – Marcus Adams Aug 13 '15 at 19:58
right, that's what I meant. I'll accept your answer. :) – Nikunj Madhogaria Aug 13 '15 at 20:07
Sorry to bring you back to this question; needed a final piece of advice. If presently I have 5 payment modes and in future I might consider adding (with new and easier payment modes coming up everyday) or removing (some of them becoming obsolete) them, which one will be a better solution? IMHO the second one is a better solution in both the scenarios. Presently, I won't be having 4 NULL values for every record in `order` table; instead I'll be saving 3 spaces by storing only 2 in the junction table. In future if I add/remove payment modes, I'll be dealing with tables instead of columns. – Nikunj Madhogaria Aug 15 '15 at 08:02
1

Yes, easier to add/remove tables on a production database than alter columns. – Marcus Adams Aug 15 '15 at 13:33

No two rows of different tables have same primary key?

2 Answers2