When I first read Phil Factor’s article on SQL Smells I had to look up Polymorphic Association to make sure that I understood what he was getting at. He’s right – this is a bad SQL smell! In my option it is one that can always be avoided.
But what is a “polymorphic association” in SQL?
Polymorphic Association
The reason I didn’t recognise Phil Factor’s polymorphic association, is that I knew by the name “alternative parent”. Polymorphic Association is probably known by other names too!
Relational Databases don’t recognise the idea the idea of sub-typing. The construction Phil Factor doesn’t like is the result of a flawed attempt to provide sub-typing.
We need to deal with some real situations like:
- “Customer” – has the sub-types:
- Person
- Corporate Entity (or “Business”)
- “Something we sell” (perhaps “Saleable item”?) – has the sub-types:
- Product
- Service
One way to do this is to define entities with sub-types. Doing sub-typing like this is a decision in the “Conceptual Model”. I need to point out that this is contentious because it builds the sub-types into the structure of the database itself. They become “hard-coded”.
In the rest of this article I’m going to use the “Customer: Person/Business” example.
What are we trying to achieve with “polymorphic association”?
In the example, someone has decided that we need to be able to record that Orders. Every Order is placed by either a Person or a Business. They have decided to have separate entities for Person and Business because they have different attributes. I have seen this conceptual requirement represented in several different ways, including:
- “Person” and “Business” surrounded by a box representing “Customer”
- A bar (often curved) drawn across the lines representing the relationships between Person or Business and Order.
- A Customer super-type with Person and Business sub-types.
The requirements are fairly simple:
- An Order is always placed by a Customer
- A Customer has to be a Person or a Business (as far as we are concerned, it can’t be both)
- So every Order is placed by either a Person or a Business.
What’s the problem with “polymorphic association”?
Phil Factor identifies a problem with “polymorphic association” when the database has reached the Physical Design stage. We can spot the potential for this problem to occur much earlier, when we are gathering Requirements and creating the Conceptual Design.
The problem in the Physical Design is that the Foreign Key in Order can refer to more than one table. The Cust_Type column tells use which table to use, but the database manager will not understand that. There are also further clues in the names of the various columns – If you look, you will notice a lot of use of the “super-type” in the names!
There are several problems with this Physical Design (Phil Factor’s article gives a more technical description):
- It is not easy to spot in the database, and that can be a problem in itself.
- It will result in complex procedural code.
- The Order table cannot use constraints to check that the foreign keys are valid.
- JOINs will be complicated
- This is construction is likely to perform poorly.
Polymorphic Associations – One solution
One solution to this problem is to recognise the “super-type” and make it explicit.
This slightly different construction with a Customer entity or table, enables us to solve the difficulties with the polymorphic association or alternative parent design. Notice how the ends of the relationships nearest the Person and Business tables are not the familiar “crow’s feet” but indicate that there must be either one or zero rows.
When we use this design all the Foreign Keys in the Order table now reference rows in the Customer table.
Spotting potential Polymorphic Associations
Deal with Polymorphic Associations during the Conceptual Design of the database. The indications that the Requirements create a situation where someone might try to use a Polymorphic Association are:
- There are several entities which are sufficiently similar that you consider having one entity, but are sufficiently different that you think multiple entities are justified.
- Something (like an order) needs to have an association with one (but only one) from among this group.
- Finally, watch for the word “OR” in your thinking. A Person OR a Business places and Order.
If you encounter these conditions, first you should challenge whether sub-typing is the correct solution. If you think it is, then use the single super-type and multiple sub-types form and avoid the polymorphic association.
Summary
I agree with Phil Factor. If you encounter the polymorphic association, stop! It is very unlikely to the the best solution in SQL. Don’t try to implement a polymorphic association. Create an explicit table for the super-type and then a separate table for each of the sub-types instead.
Where next?
In the next article I’m going to look at something which is responsible for a lot of bad “SQL Smells”. This multiple bad smell is the naming of SQL objects.
Great Article! But how then we query all customer table along with the data from person and business table?
Hello Jalu, I’m glad you found the article useful. You ask “(how do) we query all customer table along with the data from person and business table?” Look closely at the diagram with the title “A solution to polymorphic association with a Super-type”. One way to do what I think you want to do would be to have two queries, one of which JOINed Customer to Person, and the other of which JOINed Customer to Business and then UNION the results together. The column headings of the results would look like “CustId, CustType, ForeName, BusinessName” and for rows which were Businesses the ForeName would always be NULL and for rows which were Person the BusinessName would always be NULL. I’m afraid that’s a little messy, but that is the kind of thing that happens when you have these complicated requirements.
Polymorphic association is not always bad though I also don’t agree with this schema. However, how on earth isn’t UNION an sql smell. In every instance I’ve seen UNION in my career of 18 years of database design and engineering I’ve never seen a UNION that didn’t immediately point to a severe design flaw in the schema. That the *solution* in this article would be a UNION is frightening and I’d hate for someone to find this article and think that UNION is ever okay. It isn’t. Fix the schema.
Hi Aaron, I think I understand your point. Please bear in mind that the schema given here was only ever intended to be an illustration.
The whole point about the idea of “bad smells” is not that they indicate that something is absolutely wrong – instead they indicate that something might be wrong and deserves further investigations. Schemas and systems are rarely perfect and often we tolerate imperfections because we consider the effort required to change them after the event is too high. Would it have been better to get the original schema “right” (or at least “better”) in the first place? Probably.
Is UNION an SQL smell in its own right? Again, possibly. UNION is a fundamental part of the SQL language, and it’s there because it satisfies a need. If it is there, people will use, and abuse it. By all means regard UNION as a smell in its own right. Let that guide you to considering alternative solutions and if you find something better for your situation then use it.
Thank you for your interest.
Great article indeed. But how does your suggestion solve the problem of enforcing that a customer can only be a person or a business? If I get it right, in this model it would be perfectly valid to have a customer which is both, or a customer which is none, or even a customer who is 3 businesses at once.
I have the exact same problem where I have to model a customer discount system. A discount can apply to a single customer, a customer group or all customers (“audience”). And a discount can apply to a single item, a group of items or all items (“target”). But I must enforce that a discount applies exactly to one audience and exactly to one target. In the end I decided to go with a polymorphic association as the problem of having ambiguous discounts seemed worse than having invalid discounts with dangling foreign keys.
Hello David, Thanks for your comment. You are right. My “subtyping” solution doesn’t solve the problem of enforcing that a Customer can only be a Person or Business (and only 1 as well). The logic which enforces those rules would have to go somewhere – personally I would probably put it into the application code which created a “Customer” in the first place, and possibly back that up with something (constraints or triggers) as a backstop in the database.
The underlying problem here is that the relational model doesn’t really handle “subtypes” at all well. If the business requirement really does involve something like a subtype, then the situation is likely to require some serious thought and the solution may be tricky.
Your example is a case in point. You decided that “having ambiguous discounts seemed worse than having invalid discounts with dangling foreign keys” and who am I to say that you are wrong? In such a case, I think it would be a kindness to those who will work with the code (database and application) in the future to leave comments to explain the thinking behind what you have done and that is about the best that you can do.
This discussion is quite a good example of the application of “a bad smell”. There is something here which makes us uncomfortable – something isn’t actually wrong but it makes us uncomfortable. If we are going to use “Polymorphic Association” then we need to make the effort to investigate alternative solutions and justify our choice.
Thanks again for the comment.