There are 3 tables - records ( items ), pages ( pages ) and images ( images ). The image table stores the paths to the image files. The problem is how to organize the structure. Those. I had to create the owner_type field and sample by this condition, i.e. like this

 SELECT * FROM images WHERE owner_id = id AND owner_type = 'item'

because Record id and page can match and the image will be overwritten or at least get what you need. Simplified table of images looks like this

 images ------------------------- id | owner_id | owner_type

Accordingly, the owner_type just gets a string or 'page' or 'item'. Actually the question is how to get rid of this owner_type ?

  • And how much do the items and pages have in common, besides the fact that each can have images, they cannot be represented as a single table? - Mike
  • I think not, because there are a lot of fields there, and each table (items and pages) has a different number of them + a different type. - Guest
  • 2
    Pages and Items should reference Image, and not vice versa. - cpp_user
  • Can you please in more detail? - Guest
  • @ The guest cpp_user suggests storing the image id somewhere else. True, I suspect that you may have multiple images for a single object. Then for pages and items you will have to create additional tables, each with its own, like page-id: image-id - Mike

1 answer 1

If you leave one common table with images, you will not be able to completely get rid of the additional column owner_type . You will have to somehow identify the image belonging to one or another type.

However, the overhead and size of the image table can be reduced. For example, instead of the string value owner_type you can use the numeric owner_type_id . If desired, it can be made the foreign key of the owner_types table (it may not participate in queries, but simply act as an informational one). This will reduce the size of the image table, which will speed up requests to it, moreover, instead of a line in the WHERE condition there will be a number that is processed faster and whose index takes up less space.

Another way to optimize can be the absence of the name and path to the file. Those. we store only the file identifier (when it is loaded, it is renamed), and the path to it is calculated by the identifier / path / to / file / 233532.jpg. Here 23532 is the image id. It will also reduce the size of the table of images, and, consequently, increase the speed of processing requests.