What Tables to use for a Database that stores User Roles?

Question

What Tables to use for a Database that stores User Roles?

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have to design a database that is capable of storing the data of a user and its Role in a web page, these roles are: Worker, Employer and Administrator.

What confuses me is how to make those relationships. Mainly I made some relationships between:

--------------------------------------------------

| Trabajador | 0,1  ------  0,n --->>  | Rol  |


--------------------------------------------------

Likewise with the employer entity

--------------------------------------------------

|Empleador  | 0,1 ------- 0,n --->> |Rol|

--------------------------------------------------

The other way to do it would be Asi:

| Usuario |-- 0,1--------- 0,n --->> |Rol|

By creating a User Entity, users' data can be saved along with a column called user_roller where the ID of the table or Role entity is.

My question is, if done in the second way, how can users have 2 roles? Should they have two different accounts for each role?

Ó Is there a better way to make that kind of relationship?

I hope I have expired. I would appreciate any help, push or scolding. Thanks.

asked by Odannys De La Cruz 22.02.2018 в 23:16

source

2 answers

1

This kind of questions, always ( but always ) depends on the rules of your business.

There is no single way to do this, and the rules are what defines the format of the tables.

In your case, decide that there is a user table, a single user table.

And a role table, with a RolId, and I guess a list of permissions.

And here is where the rules of your business matter.

If several roles are accepted per user, you will have a table User_Roles, which will contain ID_user and ID_rol and will be related to the other two n a n.

If only one role is accepted per user, simply put the ID_rol in the Users table.

However, and I insist again, this scheme is not fixed, nor is it always so. You could have a table of characteristics, where you keep the user and the characteristics of it, which includes the role (or roles).

answered by 22.02.2018 в 23:25

Creating default object from empty value in Joomla Why does not the transition work in this example?

score 0 · Accepted Answer

I tell you the DBMS is indistinct for the task you want to accomplish, for example, if I wanted to do it in MySQL, it would be as follows:

I create a table of roles that will save me the information of each of the user types that will exist in my system

I create a user table to which by means of a FOREIGN KEY I link with the id of the table of roles to be able to identify them in a unique way.

When you sign up, fill in the table of roles first, so that later in a combo box from a form when a user registers you can choose your type of user, for example.

I enclose the brief but effective SQL of my previous explanation

CREATE TABLE roles(
    id INT PRIMARY KEY AUTO_INCREMENT,
    rol_name VARCHAR(30) NOT NULL UNIQUE,
    rol_status TINYINT(1) NOT NULL
)ENGINE=INNODB;

CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(20) NOT NULL UNIQUE,
    status_user TINYINT(1) NOT NULL,
    rol_id INT NOT NULL,
    CONSTRAINT fk_users_roles FOREIGN KEY(rol_id) REFERENCES roles(id)
);