0

At the moment I have 3 table: products, images, products_images. So, basically I store product's images in "products_images" table. When a product is updated, I remove all rows from "products_images" table related to that product, and add new one. Also, I need to save the order, now the order is how they are stored in database.

Is this correct approach? Or maybe there are a better database structure to store product's images?

jahoza
  • 49
  • 6
  • You can have one column in product_images table isDeleted once you add or remove anything check in the table if the link between product and image already exist then update the bit to 0 or 1 as needed. And for new records add a row with default bit isDeleted 0. – devedv Jan 02 '19 at 08:19
  • Read the [answer here](https://stackoverflow.com/questions/7762582/deleting-related-rows-in-a-many-to-many-relationship) by GBN. – Tim Biegeleisen Jan 02 '19 at 08:22

1 Answers1

1

Entity relationship consideratoin

If you don't share images between products (never use one image for more than 1 product) then your relationship is really One-To-Many and there is no need for products_images junction table. This would simplify your model to:

  • Products (id_product, ...)
  • Images (id_image, ..., id_product)

And id_product in Images table would have a Foreign Key pointing at Products(id_product). That way, when you want to detach all images from a product you would simply run an update statement:

UPDATE images SET id_product = NULL WHERE id_product = ?

Sorting consideration

Since you need to save the order in which images are stored in the database, depending on what columns you have in Images table you could use for example id_image if it was declared as SERIAL (auto-incremented integer value). As a rule of thumb in PostgreSQL (where there are no clustered indexes) remember that if you need the output sorted, you need to do that yourself (example: add an ORDER BY id_image) at the end.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72