Polymorphic Relationships

Most of the colleges with computer science course have a subject called Database Management system or DBMS where you are taught topics like Entity Relationships, Normalization, Relational Algebra, SQL, etc. Recently, I came across a new type of database relationship called Polymorphic Relationships which is very useful in optimizing database schema, but I never knew about it before last week. Lets learn about it in this blog.

A relationship in database is basically association between two database tables established using a foreign key. There are mainly 3 types of database relationships used very heavily:-

One-to-One relationship is used to to associate two tables in a way such that one record from table 1 is related to only one record in table 2. For example, one user can have only one profile.

One-to-One relationship

One-to-Many relationship is used to associate two tables in a way such that one one record from table 1 is related to multiple records in table two. For example, one user can have multiple posts.

One-to-Many relationship

In Many-to-Many relationships, each record of the first table can relate to any records (or no records) in the second table and each record of the second table can also relate to more than one record of the first table. To establish a many-to-many relationship, we create a new table that store the related records together. For example, one employee can be a part of multiple projects and a project can also have multiple employees.

Many-to-Many relationship

A polymorphic relationship is an association where a single model/entity can be related to multiple other tables. Lets understand with an example:-

Lets say we need to create a database for an e-commerce website that sells multiple products. Each for these products have different properties. Lets take a book and a t-shirt for example:-

As we can see from the above diagram, both t-shirts and books are products, but have different properties. So, how do we create a relationship between these tables?

Well, we can use polymorphic relationship here, since both t-shirt and book can belong to products. As stated before, in polymorphic relationship, an entity can belong to multiple other entities. So, both books and t-shirts can belong to products table as follows:-

In the polymorphic table (products table in this case), the type field contains the name of the table in which that type id belongs. So, if the product type is tshirt, we have to look for the corresponding product_id in the tshirt table.

The best thing about polymorphic relationships is that they are very flexible. Using these, you can relate anything quite easily. Also they are pretty easy to implement and great for ad-hoc systems.

On the downside, they introduce a whole new level of complexity with the indexes. They could be hard when used without any ORM and a lot of database interpreting tools can not identify them.