15 lines
634 B
SQL
15 lines
634 B
SQL
-- Create user_branches table
|
|
CREATE TABLE IF NOT EXISTS user_branches (
|
|
user_id INT NOT NULL,
|
|
branch_id INT NOT NULL,
|
|
PRIMARY KEY (user_id, branch_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Migrate existing data from users table
|
|
INSERT IGNORE INTO user_branches (user_id, branch_id)
|
|
SELECT id, branch_id FROM users WHERE branch_id IS NOT NULL;
|
|
|
|
-- Keep branch_id in users for now as fallback or default, but we should eventually rely on user_branches
|