Association Table - Create UserRoles Association Table for Users and Roles Table

An association (mapping) table, also called a link table or join table, is a type of table in a relational database that is used to associate(map) records from two or more other tables. Typically used in many-to-many relationships. In this case, any record in one table can be related to multiple records in another table and vice versa.

 An association (mapping) table typically contains a foreign key that references the primary key of the table to which it is mapped. 

Example of an association (mapping) table -

"usersRoles" table have two columns: userId and roleId. This table is used to associate "users" table with "roles" table in a many-to-many relationship. The primary key is a composite key made up of both columns (PRIMARY KEY(userId, roleId)), and there are foreign key constraints to ensure that the "userId" column references the "id" column in the "users" table and the "roleId" column references the "id" column in the roles table.

Depends on the role assigned to user user will be able access or perform the actions.

Please refer below SQL Statement,

 CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL
);

CREATE TABLE roles (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

CREATE TABLE usersRoles (
    userId INTEGER NOT NULL,
    roleId INTEGER NOT NULL,
    PRIMARY KEY(userId, roleId),
    FOREIGN KEY(userId) REFERENCES users(id),
    FOREIGN KEY(roleId) REFERENCES roles(id)
);


-- Inserting data into the users table
INSERT INTO users (name, email, password)
VALUES ('John Doe', 'johndoe@example.com', 'password123'),
       ('Jane Smith', 'janesmith@example.com', 'mypassword'),
       ('Bob Johnson', 'bobjohnson@example.com', '123456');

-- Inserting data into the roles table
INSERT INTO roles (name, description)
VALUES ('Admin', 'Has full access to the system.'),
       ('Editor', 'Can edit and create content.'),
       ('Viewer', 'Can view content but cannot make changes.');
      
-- Inserting data into the usersRoles table
INSERT INTO usersRoles (userId, roleId)
VALUES (1, 1), -- John Doe is an Admin
       (2, 2), -- Jane Smith is an Editor
       (3, 3), -- Bob Johnson is a Viewer
       (1, 2), -- John Doe is also an Editor
       (2, 3); -- Jane Smith is also a Viewer