MTZi9wAR

1CREATE TABLE IF NOT EXISTS setting (
2    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3    label VARCHAR(191) NOT NULL,
4    key VARCHAR(191) NOT NULL,
5
6    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
7    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
8
9    PRIMARY KEY (id)
10);
11
12CREATE TABLE IF NOT EXISTS user_setting (
13    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
14    user_id INT(10) UNSIGNED NOT NULL,
15    setting_id INT(10) UNSIGNED NOT NULL,
16    val VARCHAR(191) NOT NULL,
17
18    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
19    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
20
21    PRIMARY KEY (id),
22    UNIQUE INDEX idx_user_id_and_setting_id (user_id, setting_id),
23    CONSTRAINT fk_user_setting_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
24    CONSTRAINT fk_user_setting_setting_id FOREIGN KEY (setting_id) REFERENCES setting (id) ON DELETE CASCADE
25);
26	
27INSERT INTO user_setting (user_id, setting_id, val)
28VALUES (?, ?, ?)
29ON DUPLICATE KEY UPDATE val = VALUES(val)
30	
31SELECT setting.*, user_setting.*
32FROM setting
33INNER JOIN user_setting
34    ON user_setting.user_id = ?
35    AND user_setting.setting_id = setting.id
36	
37CREATE TABLE IF NOT EXISTS setting (
38    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
39    label VARCHAR(191) NOT NULL,
40    key VARCHAR(191) NOT NULL,
41
42    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
43    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
44
45    PRIMARY KEY (id)
46);
47
48CREATE TABLE IF NOT EXISTS datatype_bool (
49    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
50    val TINYINT(1) NOT NULL,
51
52    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
53    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
54
55    PRIMARY KEY (id)
56);
57
58CREATE TABLE IF NOT EXISTS setting_datatype_bool (
59    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
60    setting_id INT(10) UNSIGNED NOT NULL,
61    datatype_bool_id INT(10) UNSIGNED NOT NULL,
62
63    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
64    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
65
66    PRIMARY KEY (id),
67    UNIQUE INDEX idx_steting_id_and_datatype_bool_id (user_id, datatype_bool_id),
68    CONSTRAINT fk_setting_datatype_bool_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
69    CONSTRAINT fk_setting_datatype_bool_datatype_id FOREIGN KEY (datatype_id) REFERENCES setting_datatype_bool (id) ON DELETE CASCADE
70);
71
72CREATE TABLE IF NOT EXISTS user_setting (
73    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
74    user_id INT(10) UNSIGNED NOT NULL,
75    setting_id INT(10) UNSIGNED NOT NULL,
76
77    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
78    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
79
80    PRIMARY KEY (id),
81    UNIQUE INDEX idx_user_id_and_setting_id (user_id, setting_id),
82    CONSTRAINT fk_user_setting_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,
83    CONSTRAINT fk_user_setting_setting_id FOREIGN KEY (setting_id) REFERENCES setting (id) ON DELETE CASCADE
84);