Polymorphism in Relational Databases

0
Posted by: Bogey on April 14, 2024

Polymorphism in relational databases is a powerful design concept that is available for developers/programmers to help better data organization, improve query efficiency, reduce unnecessary redundancy in data and code and promotes flexibility within the application’s code base and the overall storage of data.

Consider a social network and all the features that it provides. Many of those features, that are aimed towards it’s registered users, have common characteristics and relate to each other in one way or another. While there are other ways to tackle the issue, one legitimate way is by using polymorphic relationship.

Of course its a decision to be made whether or not you should use polymorphism in your database.

What is polymorphism

Polymorphism is a table that morphs (or changes) to different associations based on the table it relates to. Polymorphism in relational databases are tables that hold data that could be associated to different tables.

Consider the fact that in social networking applications almost every function has a commenting feature. A video you could watch will allow you to comment on it as well as posts or reels. You could set up a one-to-many relationship between video_comments and video and than post_comments and posts and so on and so forth and that is a valid solution.

Another solution is by using a polymorphic relationship between comments and the other functional features that a user could comment on.

Trade-offs

Before we dive into the intrinsics of polymorphism, it is worth noting that there are cons to the pros of polymorphism that a developer should consider before deciding which approach to use.

Polymorphic relationships aren’t like traditional relationships which simply use foreign keys that references a primary key on the related table, polymorphic tables require an additional column besides the foreign key to make it work and because the table could be associated with different other tables, there are no native to database system way to set up integrity actions or constraints to enforce data integrity.

The foreign key in a polymorphic table is not the traditional foreign key that you would set up in your database that points to a specific related table. In this case, since we are relying on a second column which tells us which table each record is associated with, the foreign key is simply an integer column. Since the “foreign key” cannot be designated as the foreign key within the database system, there are no native way to set up integrity actions or constraints.

While that seems like a big trade-off for simplicity sake, most of the time you would be using a framework (like Laravel) to build your application, and these frameworks usually have (I know Laravel does) built-in means to deal with polymorphic relationships and help make it easier to maintain the integrity of the data stored between the associated tables.

Another point to consider before deciding if you should use a polymorphic relationship is the size you expect your application to be and how active each feature you expect to be. The comments table could grow to a huge size which could have an affect on performance depending on server resources, indexing and query optimization.

Polymorphic relationship usually involve complex conditional JOIN queries which is another point to consider in deciding whether you want to use them. On the plus side, you could reuse the queries for every morphable table, so you would need one conditional query to retrieve the data from a polymorphic relationship with the conditional portions being dynamically set with variables in the language you are programming in.

Polymorphism

There are different types of polymorphic relationships just like there are traditional relationships among tables. Each relationship type serves it’s own purpose.

You could follow along using SQLFiddle using the sample SQL to help mess around with various polymorphic relationship types.

One-to-One Polymorphism

CREATE TABLE `media_assets` (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    description TEXT,
    morphable_id INT,
    morphable_type VARCHAR(255)
);

CREATE TABLE `images` (
    id INT PRIMARY KEY,
    resolution VARCHAR(50),
    file_size INT,
    color_mode VARCHAR(50)
);

CREATE TABLE `videos` (
    id INT PRIMARY KEY,
    duration TIME,
    format VARCHAR(50),
    resolution VARCHAR(50)
);

INSERT INTO `images` (id, resolution, file_size, color_mode) VALUES
(1, '1920x1080', 1024, 'RGB'),
(2, '1280x720', 512, 'CMYK'),
(3, '2560x1440', 2048, 'RGBA');

INSERT INTO `videos` (id, duration, format, resolution) VALUES
(1, '00:10:30', 'MP4', '1920x1080'),
(2, '00:05:45', 'AVI', '1280x720'),
(3, '00:15:20', 'MOV', '2560x1440');

INSERT INTO `media_assets` (id, title, description, morphable_id, morphable_type) VALUES
(1, 'Image 1', 'Description for Image 1', 1, 'images'),
(2, 'Image 2', 'Description for Image 2', 2, 'images'),
(3, 'Image 3', 'Description for Image 3', 3, 'images'),
(4, 'Video 1', 'Description for Video 1', 1, 'videos'),
(5, 'Video 2', 'Description for Video 2', 2, 'videos'),
(6, 'Video 3', 'Description for Video 3', 3, 'videos');

In this database schema, we have a polymorphic table media_assets that holds common information between various media types, in this case images and videos. This could be part of a media storage system where you hold general information about each piece of media in media_assets and then more concise information in the respective data tables.

The media_assets has two columns that are used to establish a connection between it and the proper table for each record that it holds: morphable_id and morphable_type. morphable_id is the “foreign key” referencing the primary key of the table it references in morphable_type.

This way, when we want to retrieve the images, we select from the media_assets table where the morphable_type is set to images (the name of the images table) and then we join the images table setting the morphable_id to equal to the primary key in images table.

SELECT
    *
FROM
    `media_assets`
INNER JOIN `images` ON `media_assets`.morphable_id = `images`.id
WHERE
    `media_assets`.morphable_type = 'images';

That SQL will select all images and their data from the media_assets table. To make this work dynamically between all morphable tables, all you need to do is change all instances of the images table to a variable that will change to the table you want to retrieve. In PHP, that would look like:

$table = 'videos';

$sql = "SELECT
            *
        FROM
            `media_assets`
        INNER JOIN `$table` ON `media_assets`.morphable_id = `$table`.id
        WHERE
            `media_assets`.morphable_type = '$table';";

This way, regardless what table you need to retrieve the data from, all you need to do is change the variable and get the data from that table in this polymorphic relationship. You could have this be in a function that returns the proper SQL for the specified table you could enter into the argument.

One-to-Many Polymorphism

A one-to-many polymorphic relationship is what you would have between a comments system and other features of your application like images and videos.

CREATE TABLE `comments` (
    id INT PRIMARY KEY,
    body TEXT,
    commentable_id INT,
    commentable_type ENUM('images', 'videos')
);

INSERT INTO `comments` (id, body, commentable_id, commentable_type) VALUES
(1, 'Comment 1 on Image 1', 1, 'images'),
(2, 'Comment 2 on Image 1', 1, 'images'),
(3, 'Comment 1 on Image 2', 2, 'images'),
(4, 'Comment 2 on Image 2', 2, 'images'),
(5, 'Comment 3 on Image 2', 2, 'images'),
(6, 'Comment 1 on Image 3', 3, 'images'),
(7, 'Comment 2 on Image 3', 3, 'images'),
(8, 'Comment 3 on Image 3', 3, 'images'),
(9, 'Comment 4 on Image 3', 3, 'images'),
(10, 'Comment 1 on Video 1', 1, 'videos'),
(11, 'Comment 2 on Video 1', 1, 'videos'),
(12, 'Comment 1 on Video 2', 2, 'videos'),
(13, 'Comment 2 on Video 2', 2, 'videos'),
(14, 'Comment 3 on Video 2', 2, 'videos'),
(15, 'Comment 1 on Video 3', 3, 'videos'),
(16, 'Comment 2 on Video 3', 3, 'videos');

Here we add comments to our images and videos we’ve set up earlier. Each of the features could have many comments but each comment could belong to only one feature, therefore we have a one-to-many relationship, and since a single table holds the comments for multiple features, it’s a polymorphic one-to-many relationship.

SELECT
    `comments`.id, `comments`.body
FROM
    `videos`
LEFT JOIN `comments` ON `videos`.id = `comments`.commentable_id
    AND `comments`.commentable_type = 'videos'
WHERE
	`videos`.id = 2
ORDER BY
    `comments`.id;

That would be the SQL to retrieve the comments for a video that has an ID of 2. Just like the previous example, we could have some dynamic variables to make the SQL reusable and possible be in a function to retrieve comments for whatever media element the user is checking out.

function getComments($table, $id) {
    return "SELECT
                `comments`.id, `comments`.body
            FROM
                `$table`
            LEFT JOIN `comments` ON `$table`.id = `comments`.commentable_id
                AND `comments`.commentable_type = '$table'
            WHERE
                `$table`.id = $id
            ORDER BY
                `comments`.id;";
}

$sql = getComments('images', 1);

Here we have a simple way to generate the required SQL for the table and record ID we need to retrieve. The getComments('images', 1); would return the SQL required to retrieve the comments for the record that has an ID of 1 from the images table.

Many-to-Many Polymorphism

A many-to-many polymorphic relationship has the same definition of a standard many-to-many relationship except for it’s in a table that holds the data that associated with multiple other tables. Let’s add a tagging feature to our images and videos. Each image could have multiple tags and each of those tags could represent many different images or videos.

CREATE TABLE `tags` (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE `taggables` (
    tag_id INT,
    taggable_id INT,
    taggable_type ENUM('images', 'videos'),
    PRIMARY KEY (tag_id, taggable_id, taggable_type)
);

INSERT INTO `tags` (id, name) VALUES
(1, 'landscape'),
(2, 'nature'),
(3, 'outdoors'),
(4, 'abstract'),
(5, 'art'),
(6, 'colorful'),
(7, 'cityscape'),
(8, 'urban'),
(9, 'architecture'),
(10, 'tutorial'),
(11, 'education'),
(12, 'how-to'),
(13, 'comedy'),
(14, 'entertainment'),
(15, 'funny'),
(16, 'documentary'),
(17, 'film'),
(18, 'cinematic');

INSERT INTO `taggables` (tag_id, taggable_id, taggable_type) VALUES
(1, 1, 'images'),
(2, 1, 'images'),
(3, 1, 'images'),
(4, 2, 'images'),
(5, 2, 'images'),
(6, 2, 'images'),
(7, 3, 'images'),
(8, 3, 'images'),
(9, 3, 'images'),
(10, 1, 'videos'),
(11, 1, 'videos'),
(12, 1, 'videos'),
(13, 2, 'videos'),
(14, 2, 'videos'),
(15, 2, 'videos'),
(16, 3, 'videos'),
(17, 3, 'videos'),
(18, 3, 'videos');

Here we have sample tags for every image and video that we have stored and each of these tag associations are stored in one place. A huge benefit to this is that we have only one table for tags that we need to maintain and these tags we use for every taggable piece of information we store on our site.

SELECT
    `images`.*,
    COALESCE(GROUP_CONCAT(`tags`.name SEPARATOR ', '), 'No Tags') AS tag_names
FROM
    `images`
LEFT JOIN
    `taggables` ON `images`.id = `taggables`.taggable_id AND `taggables`.taggable_type = 'images'
LEFT JOIN
    `tags` ON `taggables`.tag_id = `tags`.id
WHERE
    `images`.id = 3
GROUP BY
    `images`.id;

Here we have the SQL to select the tags for a specific record from either the images table or the videos table. In this specific example it is retrieving the image with a primary key of 3. We’re using COALESCE SQL function to return ‘No Tags’ if there are no tags associated with the record. We’re also using GROUP_CONCAT to combine all tags into one string and form them in a string separated by a comma.

Than you could explode through the tag_names and loop through each tag as you need to in your application.

$tags = explode(', ', $row['tag_names']);

Just like the previous examples, we could make this SQL be a reusable function to make it easier to retrieve the required data from whatever table we need to.

function getTags($table, $id) {
    return "SELECT
                `$table`.*,
                COALESCE(GROUP_CONCAT(`tags`.name SEPARATOR ', '), 'No Tags') AS tag_names
            FROM
                `$table`
            LEFT JOIN
                `taggables` ON `$table`.id = `taggables`.taggable_id AND `taggables`.taggable_type = '$table'
            LEFT JOIN
                `tags` ON `taggables`.tag_id = `tags`.id
            WHERE
                `$table`.id = $id
            GROUP BY
                `$table`.id;";
}

With this, these queries are more flexible to our needs and promotes code reusability as well as simplifies the way we store and handle our data, since we don’t need different tags tables for different types of features we might have in our application.

Conclusion

In conclusion, polymorphism in relational databases offers a powerful way to model diverse and complex data relationships. By using polymorphic relationships, you can design more flexible and scalable database schemas that accommodate varying types of entities and their associations.

It’s important to know all the drawbacks of using a polymorphic relationships and weighing their pros and cons to decide wether it’s worth it in your application or if you need to avoid them. Key takeaways from exploring polymorphism in relational databases include:

  1. Versatile Data Modeling: Polymorphic relationships enable you to model diverse entities with shared attributes in a single table, reducing redundancy and improving data organization.
  2. Scalability and Extensibility: With polymorphism, your database schema can easily adapt to changes and additions of new entity types, making it more scalable and extensible as your application evolves.
  3. Query Flexibility: Polymorphic associations allow for flexible querying, where you can retrieve related data regardless of the specific type of entity, enhancing the versatility of your data retrieval operations.
  4. Consideration of Performance: While polymorphic relationships offer flexibility, it’s essential to consider performance implications, such as indexing key columns and optimizing queries, especially in large databases with complex relationships.

Incorporating polymorphism effectively requires a balance between data modeling flexibility and performance optimization, tailored to the specific needs and characteristics of your application. By understanding and leveraging polymorphic relationships, you can design robust and efficient database structures that support dynamic and diverse data interactions.

Below is the entire SQL used throughout the post.

CREATE TABLE `media_assets` (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    description TEXT,
    morphable_id INT,
    morphable_type VARCHAR(255)
);

CREATE TABLE `images` (
    id INT PRIMARY KEY,
    resolution VARCHAR(50),
    file_size INT,
    color_mode VARCHAR(50)
);

CREATE TABLE `videos` (
    id INT PRIMARY KEY,
    duration TIME,
    format VARCHAR(50),
    resolution VARCHAR(50)
);

CREATE TABLE `comments` (
    id INT PRIMARY KEY,
    body TEXT,
    commentable_id INT,
    commentable_type ENUM('images', 'videos')
);

CREATE TABLE `tags` (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE `taggables` (
    tag_id INT,
    taggable_id INT,
    taggable_type ENUM('images', 'videos'),
    PRIMARY KEY (tag_id, taggable_id, taggable_type)
);

INSERT INTO `images` (id, resolution, file_size, color_mode) VALUES
(1, '1920x1080', 1024, 'RGB'),
(2, '1280x720', 512, 'CMYK'),
(3, '2560x1440', 2048, 'RGBA');

INSERT INTO `videos` (id, duration, format, resolution) VALUES
(1, '00:10:30', 'MP4', '1920x1080'),
(2, '00:05:45', 'AVI', '1280x720'),
(3, '00:15:20', 'MOV', '2560x1440');

INSERT INTO `media_assets` (id, title, description, morphable_id, morphable_type) VALUES
(1, 'Image 1', 'Description for Image 1', 1, 'images'),
(2, 'Image 2', 'Description for Image 2', 2, 'images'),
(3, 'Image 3', 'Description for Image 3', 3, 'images'),
(4, 'Video 1', 'Description for Video 1', 1, 'videos'),
(5, 'Video 2', 'Description for Video 2', 2, 'videos'),
(6, 'Video 3', 'Description for Video 3', 3, 'videos');

INSERT INTO `comments` (id, body, commentable_id, commentable_type) VALUES
(1, 'Comment 1 on Image 1', 1, 'images'),
(2, 'Comment 2 on Image 1', 1, 'images'),
(3, 'Comment 1 on Image 2', 2, 'images'),
(4, 'Comment 2 on Image 2', 2, 'images'),
(5, 'Comment 3 on Image 2', 2, 'images'),
(6, 'Comment 1 on Image 3', 3, 'images'),
(7, 'Comment 2 on Image 3', 3, 'images'),
(8, 'Comment 3 on Image 3', 3, 'images'),
(9, 'Comment 4 on Image 3', 3, 'images'),
(10, 'Comment 1 on Video 1', 1, 'videos'),
(11, 'Comment 2 on Video 1', 1, 'videos'),
(12, 'Comment 1 on Video 2', 2, 'videos'),
(13, 'Comment 2 on Video 2', 2, 'videos'),
(14, 'Comment 3 on Video 2', 2, 'videos'),
(15, 'Comment 1 on Video 3', 3, 'videos'),
(16, 'Comment 2 on Video 3', 3, 'videos');

INSERT INTO `tags` (id, name) VALUES
(1, 'landscape'),
(2, 'nature'),
(3, 'outdoors'),
(4, 'abstract'),
(5, 'art'),
(6, 'colorful'),
(7, 'cityscape'),
(8, 'urban'),
(9, 'architecture'),
(10, 'tutorial'),
(11, 'education'),
(12, 'how-to'),
(13, 'comedy'),
(14, 'entertainment'),
(15, 'funny'),
(16, 'documentary'),
(17, 'film'),
(18, 'cinematic');

INSERT INTO `taggables` (tag_id, taggable_id, taggable_type) VALUES
(1, 1, 'images'),
(2, 1, 'images'),
(3, 1, 'images'),
(4, 2, 'images'),
(5, 2, 'images'),
(6, 2, 'images'),
(7, 3, 'images'),
(8, 3, 'images'),
(9, 3, 'images'),
(10, 1, 'videos'),
(11, 1, 'videos'),
(12, 1, 'videos'),
(13, 2, 'videos'),
(14, 2, 'videos'),
(15, 2, 'videos'),
(16, 3, 'videos'),
(17, 3, 'videos'),
(18, 3, 'videos');

Comments

No Comments. Be the first to make a comment.

Need to Register or Login to comment on post