Mysql – ORM for Image model

database-designMySQL

I'm uncertain as to how a certain table should establish relationships, and I feel as if the solution I came up with is incorrect.

The images table is used to store an absolute path on a server (path). An image may belong to one of the following tables.

projects
comments
tickets
milestones

I decided that each image should have some nullable FKs to the respective relationships.

mysql> show columns in images;
+--------------+------------------+------+-----+---------------------+----------------+
| Field        | Type             | Null | Key | Default             | Extra          |
+--------------+------------------+------+-----+---------------------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| deleted_at   | timestamp        | YES  |     | NULL                |                |
| path         | varchar(255)     | NO   |     | NULL                |                |
| created_at   | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at   | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| project_id   | int(11)          | YES  |     | NULL                |                |
| milestone_id | int(11)          | YES  |     | NULL                |                |
| ticket_id    | int(11)          | YES  |     | NULL                |                |
| comment_id   | int(11)          | YES  |     | NULL                |                |
+--------------+------------------+------+-----+---------------------+----------------+

However, this seems counterintuitive and ludicrous to query efficiently. I feel as thought it might be more appropriate to approach a Pivot schema wherein I have images with only the path and other boilerplate data, then have multiple Pivot tables representing the relationships (many-to-many).

projects_images
milestones_images
comments_images
ticket_images

Am I correct in assuming the Pivot schema is a better approach, or is there yet a better one out there?

Best Answer

If you have a table that contains images, you can hold the ImageID as a foreign key column in those tables: projects_images milestones_images comments_images ticket_images. you asked for a "many to many" relationship solution but your design looks like a "One to many" relationship because you only got place for one integer in any of the cloumns you used for refefences(project_id ,milestone_id ,ticket_id ,comment_id).

Related Question