mysql – User Settings Table Scheme Design

I have a simple design for the user settings and it works. Here it is:

CREATE TABLE IF NOT EXISTS setting (
id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
Label VARCHAR (191) NOT NULL,
Key VARCHAR (191) NOT NULL,

created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS user_setting (
id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT (10) UNSIGNED NOT NULL,
setting_id INT (10) UNSIGNED NOT NULL,
val VARCHAR (191) NOT NULL,

created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

Primary key (ID),
UNIQUE INDEX idx_user_id_and_setting_id (user_id, setting_id),
CONSTRAINT fk_user_setting_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
CONSTRAINT fk_user_setting_setting_id FOREIGN KEY (setting_id) REFERENCES setting (id) ON DELETE CASCADE
);

The query to insert a value into the above schema would look like this:

INSERT INTO user_setting (user_id, setting_id, val)
VALUES (?,?,?)
ON DUPLICATE KEY UPDATE val = VALUES (val)

To retrieve values ​​for a user, you can do the following:

SELECT setting. *, User_setting. *
FROM setting
INNER JOIN user_setting
ON user_setting.user_id =?
AND user_setting.setting_id = setting.id

Basically, there are internally created settings, and each user can set a value for a particular setting.

My problem now is that the value for each setting can only be VARCHARThis is not ideal because in some cases a setting is represented by a checkbox in the UI or something else that requires a more specific type. I do not want to use hard-coded values ​​like "true" and "false" in such situations, as this will make the code fragile. I would rather check a concrete Boolean type.

So that's my suggested solution.

CREATE TABLE IF NOT EXISTS setting (
id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
Label VARCHAR (191) NOT NULL,
Key VARCHAR (191) NOT NULL,

created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS datatype_bool (
id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
val TINYINT (1) NOT NULL,

created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (ID)
);

CREATE TABLE IF NOT EXISTS setting_datatype_bool (
id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
setting_id INT (10) UNSIGNED NOT NULL,
datatype_bool_id INT (10) UNSIGNED NOT NULL,

created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

Primary key (ID),
UNIQUE INDEX idx_steting_id_and_datatype_bool_id (user_id, datatype_bool_id),
CONSTRAINT fk_setting_datatype_bool_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
CONSTRAINT fk_setting_datatype_bool_datatype_id FOREIGN KEY (datatype_id) REFERENCES setting_datatype_bool (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_setting (
id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT (10) UNSIGNED NOT NULL,
setting_id INT (10) UNSIGNED NOT NULL,

created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

Primary key (ID),
UNIQUE INDEX idx_user_id_and_setting_id (user_id, setting_id),
CONSTRAINT fk_user_setting_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
CONSTRAINT fk_user_setting_setting_id FOREIGN KEY (setting_id) REFERENCES setting (id) ON DELETE CASCADE
);

Note the new one datatype_bool and einstellung_datentyp Table. The idea is that you can assign a data type to a specific setting (in this case, a Boolean data type-I could create other tables that represent other data types), and the value for that setting would be stored in the data type table.

I have not actually tested it yet, so I do not have queries for this variant (although it looks a bit like the first queries), but theoretically it seems to work.

I was just wondering if someone had to do that and / or if somebody had opinions on the design.

Many Thanks!