MySQL Polymorphic Relationship: Options and Trade-Offs

Sometimes one table must point to several different kinds of parent: a comment can belong to a post or to a photo or to a product. Frameworks call this a polymorphic association. It looks convenient and has a price.

The polymorphic pattern

query.sql

SQL

CREATE TABLE posts (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30) NOT NULL);
CREATE TABLE photos (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, file_name VARCHAR(30) NOT NULL);

CREATE TABLE comments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    commentable_type VARCHAR(20) NOT NULL,
    commentable_id INT UNSIGNED NOT NULL,
    body VARCHAR(100) NOT NULL
);

INSERT INTO posts (title) VALUES ('First post');
INSERT INTO photos (file_name) VALUES ('holiday.jpg');
INSERT INTO comments (commentable_type, commentable_id, body) VALUES
    ('post', 1, 'Nice post'), ('photo', 1, 'Lovely photo'), ('photo', 99, 'Comment on a photo that does not exist');

commentable_type says which table, and commentable_id says which row. Querying works:

query.sql

SQL

SELECT c.id, c.body, p.title
FROM comments c
JOIN posts p ON c.commentable_type = 'post' AND p.id = c.commentable_id
ORDER BY c.id;

Output

OUTPUT

+----+-----------+------------+
| id | body      | title      |
+----+-----------+------------+
|  1 | Nice post | First post |
+----+-----------+------------+
1 row in set

The problem: no foreign key

A foreign key can point to one table only. The third comment refers to a photo that does not exist, and MySQL cannot stop it, because there is no constraint. Deleting a post leaves its comments behind (orphans), unless your application remembers to delete them. Integrity now depends on every piece of code that ever touches the table.

Safer alternative 1: one nullable foreign key per parent

query.sql

SQL

CREATE TABLE comments2 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NULL,
    photo_id INT UNSIGNED NULL,
    body VARCHAR(100) NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
    FOREIGN KEY (photo_id) REFERENCES photos (id) ON DELETE CASCADE,
    CONSTRAINT one_parent CHECK ((post_id IS NOT NULL) + (photo_id IS NOT NULL) = 1)
);

INSERT INTO comments2 (post_id, body) VALUES (1, 'Nice post');
INSERT INTO comments2 (photo_id, body) VALUES (1, 'Lovely photo');
INSERT INTO comments2 (photo_id, body) VALUES (99, 'No such photo');

Output

OUTPUT

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`comments2`, CONSTRAINT `comments2_ibfk_2` FOREIGN KEY (`photo_id`) REFERENCES `photos` (`id`) ON DELETE CASCADE)

Each parent type has its own real foreign key. The CHECK constraint insists that exactly one parent is set (CHECK Constraints):

query.sql

SQL

INSERT INTO comments2 (post_id, photo_id, body) VALUES (1, 1, 'Two parents');

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'one_parent' is violated.

It adds a column for each new parent type, which is fine for two or three types.

Safer alternative 2: a common parent table

Make every commentable thing a row in a shared table, and let comments point to that:

query.sql

SQL

CREATE TABLE commentables (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kind VARCHAR(10) NOT NULL);
CREATE TABLE articles (id INT UNSIGNED PRIMARY KEY, title VARCHAR(30) NOT NULL, FOREIGN KEY (id) REFERENCES commentables (id));
CREATE TABLE videos   (id INT UNSIGNED PRIMARY KEY, url VARCHAR(50) NOT NULL,  FOREIGN KEY (id) REFERENCES commentables (id));
CREATE TABLE comments3 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, commentable_id INT UNSIGNED NOT NULL, body VARCHAR(100) NOT NULL, FOREIGN KEY (commentable_id) REFERENCES commentables (id) ON DELETE CASCADE);

Now a comment has one real foreign key, and every kind of commentable thing is a subtype that shares the primary key. This is the most rigorous design, and it needs a little more code to create things.

Entity-Attribute-Value (EAV)

Another flexible design stores attributes as rows instead of columns, to avoid changing the schema:

query.sql

SQL

CREATE TABLE product_attributes (
    product_id INT UNSIGNED NOT NULL,
    attribute VARCHAR(30) NOT NULL,
    value VARCHAR(100) NOT NULL,
    PRIMARY KEY (product_id, attribute)
);
INSERT INTO product_attributes VALUES (1, 'color', 'blue'), (1, 'size', 'M'), (2, 'color', 'red'), (2, 'weight_g', '350');

SELECT product_id, MAX(CASE WHEN attribute = 'color' THEN value END) AS color, MAX(CASE WHEN attribute = 'size' THEN value END) AS size
FROM product_attributes
GROUP BY product_id
ORDER BY product_id;

Output

OUTPUT

+------------+-------+------+
| product_id | color | size |
+------------+-------+------+
|          1 | blue  | M    |
|          2 | red   | NULL |
+------------+-------+------+
2 rows in set

You can add any attribute without ALTER TABLE. The costs:

Every value is text. No numeric types, no date checks, no NOT NULL per attribute.
Queries are painful. Getting a row back means pivoting, as above. Filtering by two attributes needs a self join per attribute.
No per-attribute constraints or indexes.
Slow at scale.

JSON as the modern alternative

For truly variable attributes, a JSON column on the main table is usually better than EAV: one row per product, typed values inside, indexable through generated columns (JSON vs Columns).

query.sql

SQL

1
2
3

CREATE TABLE products (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, attributes JSON NOT NULL);
INSERT INTO products (name, attributes) VALUES ('T-shirt', '{"color": "blue", "size": "M"}'), ('Mug', '{"color": "red", "capacity_ml": 350}');
SELECT name, attributes->>'$.color' AS color FROM products ORDER BY id;

Output

OUTPUT

+---------+-------+
| name    | color |
+---------+-------+
| T-shirt | blue  |
| Mug     | red   |
+---------+-------+
2 rows in set

Choosing

Situation	Choice
2 or 3 parent types	One nullable foreign key per parent, with a `CHECK`
Many parent types, strict integrity	A shared parent table
Flexible attributes you rarely filter on	`JSON`
Flexible attributes you filter and aggregate a lot	Real columns (or a generated column from JSON)
A framework that insists on polymorphic columns	Accept the weaker integrity, add cleanup jobs and application-level checks

dangerous

Integrity is the point of a relational database

Each shortcut that hides a relationship from MySQL (a type column instead of a foreign key, rows instead of columns) moves a guarantee from the database into the application. Take that step knowingly.