MySQL database design for image options relationships

I have two tables, images and image_data.

image_data:

image_id | slide_id | language_id  | type   |
101      | 1        | 1            | CQ     |
101      | 2        | NULL         | NULL   |
56       | 5        | 1            | TN     |
56       | NULL     | 2            | NULL   |

Each image will have different options.

I can use GROUP_CONCAT() to turn values in multiple rows into a single concatenated string.

image_id | slide_id | language_id  | type   |
101      | 1,2      | 1            | CQ     |
56       | 5        | 1,2          | TN     |

But it will be difficult to update the rows with my backend system.

I can determine which ones to check based on the database since I have it all in one row since I concatenated it. But when I go to click "Save" and update the rowst here can be more than 1 row of the same image id, but I don't know how to update the right one.

If I checked off another slide for image id 101 then I would need to create a new row for it. If after that I wanted to add another language_id to it, then I would need to make sure to not add a new row since one exists with a NULL value, and to just replace the NULL value with the new language id.

How do I program this?

What you need to do is implement N:M (many-to-many) relationships between your images and slides / languages / types tables so that your design is more normalized (one fact in one place).

Think of it this way: one image can have multiple slides, and one slide may be an option of multiple images. -- this is a N:M relationship. Same goes for languages and types.

What you need to do is get rid of your image_data table which houses the options between ALL entities and have three separate cross-reference tables instead. Here's how you would model it:

---

Base tables:

images(image_id [PK], ...)

slides(slide_id [PK], slide_name, ...)

languages(language_id [PK], language_name, ...)

types(type_name [PK], ...)

Cross-Reference tables:

images_has_slides(image_id [PK], slide_id [PK])

images_has_languages(image_id [PK], language_id [PK])

images_has_types(image_id [PK], type_name [PK])

How it would look in ER:

With this type of design, you wouldn't have to deal with NULL values or figuring out which row to update because you now have just one fact in one place. To get all options, you would still have to do GROUP_CONCAT() like so:

SELECT
    a.*,
    GROUP_CONCAT(c.slide_name) AS slides,
    GROUP_CONCAT(e.language_name) AS languages,
    GROUP_CONCAT(f.type_name) AS types
FROM
    images a
LEFT JOIN
    images_has_slides b ON a.image_id = b.image_id
LEFT JOIN
    slides c ON b.slide_id = c.slide_id
LEFT JOIN
    images_has_languages d ON a.image_id = d.image_id
LEFT JOIN
    languages e ON d.language_id = e.language_id
LEFT JOIN
    images_has_types f ON a.image_id = f.image_id
GROUP BY
    a.image_id

Then to update image options, you would use INSERT and DELETE on the cross-reference tables:

Let's say you wanted to add two languages to an image, you would do

INSERT INTO images_has_languages (image_id, language_id) 
VALUES (101, 4), (101, 5);

The above query adds languages with id's of 4 and 5 to the image that has an id of 101.

To remove options (unchecking on the form) - let's say you wanted to remove 2 slides from an image

DELETE FROM images_has_slides WHERE image_id = 101 AND slide_id IN (3,6)

This removes slides with id's of 3 and 6 from the image that has an id of 101.

So in your application, you could figure out if you need to do insert/delete queries based on if the user unchecked or checked values in the form for the image.