CREATE TABLE users (
id INT NOT NULL IDENTITY,
username VARCHAR(50) NOT NULL,
CONSTRAINT pk_users PRIMARY KEY (id)
);
CREATE TABLE roles (
name VARCHAR(50) NOT NULL,
CONSTRAINT pk_roles PRIMARY KEY (name)
);
CREATE TABLE users_roles_mapping (
user_id INT NOT NULL,
role_name VARCHAR(50) NOT NULL,
CONSTRAINT pk_users_roles_mapping PRIMARY KEY (user_id, role_name),
CONSTRAINT fk_users_roles_mapping_user FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT fk_users_roles_mapping_role FOREIGN KEY (role_name) REFERENCES roles (name)
);
INSERT INTO users (username) VALUES ('user1');
INSERT INTO roles (name) VALUES ('role1'), ('role2');
INSERT INTO users_roles_mapping (user_id, role_name) VALUES (1, 'role1'), (1, 'role2');