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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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