[FIXED] Is it possible to dynamically add SET values to a table?

Issue

I have a number of tables in which I need to reference scene IDs, which could be a SET. The problem is that I need to be able to update a set in a table that contains my login information for the app. This set needs to expand(or potentially shrink) based on the number of scenes that exist on the DB. Is it possible to do in phpmyadmin?

From what I've seen in the web interface, I must pre-define the SET values. But I cannot find any info on how to edit the SET's possible values after the column has been created.

Solution

What you have is a many-to-many relationship between logins and scenes.

The correct way to implement this is with three tables, for example:

CREATE TABLE logins (login_id INT PRIMARY KEY ...);

CREATE TABLE scenes (scene_id INT PRIMARY KEY ...);

CREATE TABLE login_has_scene (
  login_id INT NOT NULL,
  scene_id INT NOT NULL,
  PRIMARY KEY (login_id, scene_id),
  FOREIGN KEY (login_id) REFERENCES logins (login_id),
  FOREIGN KEY (scene_id) REFERENCES logins (scene_id)
);

This way you can add new scenes anytime, and you can reference any scene from any login by adding one row per login-scene pair.

This is better than using a SET because SET requires you to redefine the list of values using ALTER TABLE every time you add a scene, and this will become quite a chore.

Also a SET column only allows up to 64 distinct values. If you ever want these tables to support further scenes, you'd have to add more SET columns, or start recycling scene ids or something.

The many-to-many table is a much better solution.

Frankly, I have been using MySQL for nearly 20 years, and I've never found a good use for the SET data type.

Answered By - Bill Karwin