CODEWITHSUNDEEP
sql-serverdatabasedatabase-designrelational-databasepolymorphic-associations
Dan Def
Dan Def

Reputation: 1941

Polymorphic Association - Is it always bad?

I understand the concept of Polymorphic Associations and how they can be avoided using the Table Inheritance patterns as explained by Martin Fowler.

Given the situation where you have a database that consists of a large number of tables containing many entity types (1000+) and suppose you need to have the ability to add something like a comment or note to any of the entities stored in the database.

Using Polymorphic Association you would create a table of the types of entities that could have comments or notes and a table that contained the type ID and the ID of the entity in its table and the comment. You obviously don't get referential integrity with this.

Using the inheritance based solutions that I have come across, the suggestion in this situation would be to create a table that acted as the root 'class' of all entities in the database so that you could create a foreign key between the ID in this table and an EntityID in the comments table.

This means that every row in each table would need a row in this table and you would need to insert a record in this table to generate the ID for the entity (I know you could use a uniqueidentifier but that has its own pitfalls). To me this feels like a bottleneck would emerge around this table.

Another alternative would be to create a table between each entity table and the comments table. However, if you needed comments, notes, tags etc you end up increasing the number of tables in the database by a huge amount.

Has anyone tried to do something like this in the real world and did you find that using Polymorphic Association was a better solution, despite the lack of referential integrity in the database?

Upvotes: 2

Views: 3748

Answers (1)

DrabJay
DrabJay

Reputation: 3099

This all depends on your criteria for determining which solution is better.

The easier solution is to create a single polymorphic association table. This would be initially quicker for the programmer to produce. It fits in with well documented patterns; particularly in some of the MVC type frameworks prevalent today. However, this is actually the more complex solution as there is now a single table that encompasses many concepts. Also, correctness of the data cannot be guaranteed as referential integrity cannot be implemented.

The simpler solution is to create a single table for each relationship so that there is a single table that encompasses a single concept. It allows the use of referential integrity to guarantee correctness and quality of the data and is quicker for the maintainer of the software in assessing the impact of changes to the system. However, it is the harder solution as it requires many more tables to be created initially.

It is now down to you to make a subjective choice as to whether you want to pick the easier or simpler solution.

Upvotes: 2

Related Questions