Database Design - table creation & connecting records

Ive been trying to understand the thoughts of the person who designed this database. I want to know what this procedure is called and why its beneficial. The person who designed this is gone and I want to learn why.

Say I have two tables, Users and Roles

Users

create table my_users (
    userid int,
    primary key (userid),
    username varchar(255)
);
insert into my_users (userid, username) values (1, 'Stack User 1');

Roles

create table my_roles (
    roleid int,
    primary key (roleid),
    rolename varchar(255)
);
insert into my_roles (roleid, rolename) values (1, 'Admin'), (2, 'Local User'), (3, 'DB Owner');

These tables are mapped together called UserRoles

create table UserRoles (
    userroleid int,
    primary key (userroleid),
    userid int,
    roleid int
);

Is the reason for this so that there can be many roles to one user?

something like this:

insert into UserRoles (userid, roleid) values (1,1), (1,2), (1,3);

What would be the reason for a column in my_users for just one roleid?

create table my_users_2 (
    userid int,
    primary key (userid),
    username varchar(255),
    roleid int
);
insert into my_users_2 (userid, username, roleid) values (1, 'Stack Exchange', 1);

Is the reason for this so that there can be many roles to one user?

Yes, this is normal. Common many-to-many (M:N) relation. But your structure does not set any relation because there is no any FOREIGN KEY in the table. And in most cases the values pair (userid, roleid) in such table must be unique.

There is another thing which makes no sence - and this thing is synthetic primary key. In current structure it is excess. The next structure with composite primary key is more reasonable:
create table UserRoles (
userid int,
roleid int,
PRIMARY KEY (userid, roleid),
FOREIGN KEY (userid) REFERENCES my_users (userid),
FOREIGN KEY (roleid) REFERENCES my_roles (roleid)
);


When the relation is an entity itself (and it have some additional properties and/or it is used in another M:N relation), then the synthetic key is reasonable. In this case you'd provide (userid, roleid) uniqueness by creating according unique index:
create table UserRoles (
userroleid int,
primary key (userroleid),
userid int,
roleid int,
UNIQUE (userid, roleid),
FOREIGN KEY (userid) REFERENCES my_users (userid),
FOREIGN KEY (roleid) REFERENCES my_roles (roleid)
);


What would be the reason for a column in my_users for just one roleid?

If the user can have only one role then the role is an attribute of the user, the relation will be 1:N, and according relational column will be a part of my_users table with according FOREIGN KEY relation to my_roles. The table UserRoles not needed.

StackExchange Database Administrators Q#319252, answer score: 6