Laravel & MySQL join a table with polymorphic association

Question

Update: The following schema is just a simplified example of one module (comments) that need to have a polymorphic relationship to other modules. The application has many modules (30+) and users can add new modules via admin. Therefore, each module can be "linked" to another module, yet we do not know the module names and table names at coding time. Therefore we need a polymorphic relationship.

I am trying to perform a join with dynamic table names using polymorphic association.

Assuming the following MySQL tables

modules table
module_id | name    | table_name
1         | Quote   | quotes
2         | Order   | orders
3         | Product | products

Note: The system allows adding new modules via admin, therefore we do not know table names and column names at coding time. Module name or table_name can also change over time for existing modules.

quotes table
id | name
22 | "Quote #Q-22"
23 | "Quote #Q-23"

orders table
id  | name
122 | "Order #O-122"
123 | "Order #O-123"

products table
id  | name
55 | "Product #P-55"
56 | "Product #P-56"

comments table
id | module_id | record_id | text
1  | 1         | 23        | hello
2  | 2         | 122       | big
3  | 3         | 55        | world

I want to run a query that will return the following rows:

comments
id | name          | text
1  | Quote #Q-23   | hello
2  | Order #O-122  | big
3  | Product #P-55 | world

Laravel allows polymorphic relationship by storing the ORM class name or table name directly on the parent table.

This leave it to the ORM to take care of this relationship and, to my understanding, usually comes with some performance price. The reasons I believe there will be a noticeable performance hit are:

1. Eloquent overhead. For 1000s of rows it will create an eloquent object for each one. I love Eloquent but its performance is far less than ideal when it comes to retrieving large amount of rows. The Query Builder is more suitable for this but does not support Polymorphism.
2. Since the association is done at Eloquent level, I believe it will require an additional query. I haven't tested this but, otherwise, how will Eloquent know which tables to join? I will be happy to be mistaken on this one.
3. It could also sometimes lead to other maintenance issues, such as updating table names or changing ORM class name/namespace (although there are solutions to this).
4. This bypasses foreign key constraints.

I generally don't like the idea of coupling the table name or model directly with the record table. Another important (to me at least) drawback of this approach is that it tightly couples the database schema with the application. Or in other words, relies on the "magic" of Eloquent. If we at some point wanted to utilize Node.js, Python or GoLang (we're currently implementing a microservices architecture) we will probably not be able to use it and have to restructure the code and the database.

I've read few other articles and questions such as this one, or this one.

One approach I had in mind is to use MySQL functions or Stored Procedures to get the table name dynamically and join the result. I was able to use Stored procedures and functions to get the table name dynamically but it seems impossible to join the result of a stored procedure. Functions don't seem to solve this as well.

The second approach was to save the record names in a separate table. This doesn't work for getting the whole record (Quote, Order, etc) but since I only needed the record name, it works fine (with proper indexing of course).

record_name table
module_id | record_id | name
1         | 23        | Quote #Q-23
2         | 122       | Order #O-122
3         | 55        | Product #P-55

This approach has a few drawbacks - the first, it only works for getting selected keys (name in this case). The 2nd is that it duplicates data and violates other database principles. And lastly, it requires maintenance to update the module_name table.

My questions, therefore, are as following -

1. Are there good alternative ways to model this relationship at the database level (MySQL) or this has to be done at the application level?
2. Can a MySQL Stored Procedure or Function solve this?

Answer 1

You schema is wrong and you will notice that when you check your foreign key constraint of comments.record_id.

fix the foreign key constraints and out should come something like this:

comments table
id | quotes_id | orders_id | products_id | text
1  | 22        | null      | null        | Text comment
2  | null      | 122       | null        | Text comment
3  | null      | null      | 55          | Text comment

A proper database schema (relation) is more important than Laravel/Eloquent magic. In fact, less magic is easier and better to work with. You should also consider not using a database abstraction of this kind at all, it does not make things easier and only introduces problems.

A different, maybe even better approach would be using relation-tables:

- comments table
id | text
1  | hello
2  | world

- quote_has_comment table
quote_id | comment_id
22       | 1
(with constraint quote_id+comment_id beeing unique

)