1

My laravel application has some services and products, for simplicity's sake, I will keep it as generic as possible and I am going to call an individual service/product a resource.

I will have to implement a messaging system in such a way, so that a client can send message to the admin (and vice versa) against a single resource.

For example, consider project as being one such resource. An admin can create a project, a client can send messages to the admin who created that project. A client cannot create a project.

This is how I designed the db schema:

Message Storing Part:

conversations(id, message_id, from, to, body, is_seen)

messages(id, resource_id, resource_name)

Resource Storing Part:

projects(id, admin_id, client_id, some_other_column)

another_resource(id, admin_id, client_id, some_other_column)

yet_another_resource(id, admin_id, client_id, some_other_column)

Notice the resource_id and resource_name in the messages table:
resource_id is the id from one of the resource tables (projects, another_resource, yet_another_resource). resource_name is where my problem is. If I implement this schema, I would have to hardcode a tablename in this column. An entry in the messages table would look like this:

+----+---------------+-------------+---------------------+---------------------+
| id | resource_name | resource_id | created_at          | updated_at          |
+----+---------------+-------------+---------------------+---------------------+
|  1 | project       |           1 | 2018-01-13 15:11:07 | 2018-01-13 15:11:07 |
+----+---------------+-------------+---------------------+---------------------+

As you can see, if some client had to send a message on an entry of another_resource I would have to store the string "another_resource" as the resource_name in messages table and the id of that row of another_resource as the resource_id

I admit that I am not a good db designer but I have a strong feeling that storing a tablename as a column value is not a good idea. There must be some dynamic way of handling it. How could I improve my schema? Any modification, suggestion or advice will be greatly appreciated.

Community
  • 1
  • 1
Tanmay
  • 3,009
  • 9
  • 53
  • 83
  • 4
    When you have table that could share different resources (models), in your case messages, it could be set as [polymorphic relationship](https://laravel.com/docs/5.5/eloquent-relationships#polymorphic-relations). – Tpojka Jan 13 '18 at 17:38
  • Possible duplicate of [How are super- and subtype relationships in ER diagrams represented as tables?](https://stackoverflow.com/questions/12032348/how-are-super-and-subtype-relationships-in-er-diagrams-represented-as-tables) – philipxy Jan 13 '18 at 22:18
  • This is faq. Google my comments re sql/database subtyping. Google my comments re googling many variations of your question. [Re storing table names.](https://stackoverflow.com/a/47725225/3404097) – philipxy Jan 13 '18 at 22:21
  • Multiple tables with nearly identical columns should be the same table. (Probably with an extra column to distinguish them.) – Rick James Jan 13 '18 at 22:47
  • 1
    Thank you all. @Tpojka your suggestion worked. Actually polymorphic relation is probably the best solution for situations like this – Tanmay Jan 14 '18 at 16:24
  • I used it on project recently and it is really easy to access all of data you need. Also, take a look in code: docs show just morphMany relation, but you can set morphOne or even morphPivot [if needed](https://laravel.com/api/5.5/Illuminate/Database/Eloquent/Relations/MorphMany.html). – Tpojka Jan 14 '18 at 16:40

0 Answers0