I have a table for images.
I have tables for entities such as people, cars, houses, trains and dogs to which I need to assign images.
I need to assign images to entities. The obvious solution is to create many many-many tables:
people_images (person_id, image_id, sort_position, ...)
cars_images (car_id, image_id, sort_position, ...)
houses_images (house_id, image_id, sort_position, ...)
And perhaps modify the images table like so:
images (image_id, url, is_person_image, is_cars_image, is_houses_image, ...)
I am wondering if there is a better solution or common pattern for this.
Best Answer
Does every image belong to one and only one entity (i.e. to one person only, to one car only, etc)? If so, your image table needs a key which will identify to which entity it belongs - instead of having a table for each kind of entity. If several entities can use the same image, consider one table of images and one table whose primary key is composed of the image primary key and an entity identifier.