0

To start off, I have the following tables:

  • products - a list of products
  • images - a list of images (which may or may not belong to a product)
  • product_images - a join table to connect products to images (this table just has id, product_id, and image_id)

I need a way to mark one image as the default image to use for a product, but I'm not quite sure how to set it up. I've thought of a few options but none of them feel correct.

Options I've thought of:

  1. Add an image_id field to the products table for the default image. This seems bad cause the image on the product might not be an image that belongs to that product (I could take care of this on the application side, but I'm looking for more elegant solutions)

  2. Add a column on the product_images table that marks that image as default, and add a unique constraint on the combined product_id field and default field (the product_images table would then look like: id, product_id, image_id, default). I've never used unique constraints before, and I'm not sure if they're the right way to go.

Hope you guys have some better advice!

5
  • 1
    id is unnecessary (and inefficient) in a mapping table; see mysql.rjweb.org/doc.php/…
    – Rick James
    Jan 8, 2018 at 21:58
  • 2
    UNIQUE(prod_id, img_id, default) will not achieve your purpose.
    – Rick James
    Jan 8, 2018 at 21:59
  • @RickJames You're right. I was initially planning on including another table called product_default_images which just had the id of the row on the product_images table (so I needed the id).
    – dragmosh
    Jan 8, 2018 at 23:54
  • Do you guys think having a table called product_default_images which holds the id of the product_images table is overkill? I'd still need to have application level logic to make sure there aren't multiple entries per product.
    – dragmosh
    Jan 8, 2018 at 23:56
  • Not unreasonable. (One might argue that there is some redundancy.) Go ahead and try it. In any new database app, plan on major design changes, including schema changes, a few months into the project.
    – Rick James
    Jan 9, 2018 at 0:21

1 Answer 1

3

A UNIQUE constraint on (product_id, default) won't work since you can have several images that are not default for each product. A similar approach is to add a display_order attribute:

CREATE TABLE product_images
( product_id ... NOT NULL
      references ...
, image_id ... NOT NULL
      references ...
, display_order SMALLINT NOT NULL
,     PRIMARY KEY (product_id, display_order) )

To get the default image for a product choose the one with the smallest display_order for that product.

Depending on your business requirements you may add an alternative key. Examples:

-- An image may not be reused between products
ALTER TABLE product_images ADD CONSTRAINT ...
    UNIQUE (image_id)

-- An image may not be used several times for each product
ALTER TABLE product_images ADD CONSTRAINT ...
    UNIQUE (image_id, product_id)

To get the default image for a product:

SELECT image_id
FROM product_images
WHERE product_id = ?
ORDER BY display_order
LIMIT 1;

EDIT: I keep forgetting this bug in MySQL ...

To assign a new default image for a product:

UPDATE product_images x     
    SET display_order = (SELECT min from (SELECT MIN(display_order)-1 as min                                        FROM product_images                          
                                          WHERE product_id = ?) as y)
WHERE product_id = ?   
  AND image_id = ?;

or possibly:

UPDATE product_images x     
    SET display_order = (SELECT min 
                         FROM (
                             SELECT display_order-1 as min                         
                             FROM product_images                          
                             WHERE product_id = ? 
                             ORDER BY display_order LIMIT 1
                         ) as y
                        ) 
WHERE product_id = ?
  AND image_id = ?;
6
  • This would cover it, but would require application level maintenance. Do you have an ideas for solutions that don't require extras on the application side?
    – dragmosh
    Jan 8, 2018 at 23:52
  • 2
    What do you mean with application level maintenance? Jan 9, 2018 at 5:25
  • As in, I have to update the display_order field on the application side for all images whenever I make a change to the which image is the default image. Do you have any ideas for a solution where I can just edit the old default and new default and nothing else?
    – dragmosh
    Jan 9, 2018 at 21:35
  • I have edited my answer Jan 10, 2018 at 6:20
  • Ah, so you just assign a lower number than all the existing ones instead of renumbering the entire list. Might lead to integer overflow, but for my use case that'll never happen. Works for me, thanks!
    – dragmosh
    Jan 10, 2018 at 17:44

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.