0

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?

1 Answer 1

1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.