Users are users. They have one or more roles. In a simple design you would have a user table with a column for the user's role. This gives you the flexibility to add roles and move users between roles without changing the user.
create table users (
user_id number not null
...
, role_id number not null);
create table roles (
role_id number not null
... );
Your application queries the user table to get the role, then the role table to get the permissions associated with it (or better still a view to get it in a single query during initial authentication).
To more properly follow good database design convention you would move the role out of the users table entirely and have a table, user_roles, to marry users to multiple roles. It would contain the user_id and role_id and allow one-to-many relationships between a user and their roles and optionally define a Property (to clarify, this is a captial P Property for the user/role record here, not a flat or apartment) for the relationship.
users defines users roles defines roles user_roles says what users have what roles
Consider two buildings in the application. Mary lives in Building A and manages Building B. Her role under Building A is tenant; for Building B she's a caretaker. Assigning roles in the user_roles table gives the ability to refine her access based on a higher key in the hierarchy—property_id. Now she can query her own flat in Building A but not see others, while still managing Building B through rules like:
create table user_roles (
user
, role
, property);
insert into user_roles ('Mary', 'Tenant', 'BuildingA');
insert into user_roles ('Mary', 'Caretaker', 'BuildingB');
(Ideally you're using appropriate natural or surrogate keys for these values and not text!)
This may seem like an overly complicated way of doing it but it's exceptionally scalable, flexible, and fast. Abstracting things in the database avoids having to redesign the database later, or compromise performance and flexibility by adding complexity to work around all of the things that were "hard coded" into the initial database design.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Users are users. They have one or more roles. In a simple design you would have a user table with a column for the user's role. This gives you the flexibility to add roles and move users between roles without changing the user.
Your application queries the user table to get the role, then the role table to get the permissions associated with it (or better still a view to get it in a single query during initial authentication).
To more properly follow good database design convention you would move the role out of the
userstable entirely and have a table,user_roles, to marry users to multiple roles. It would contain theuser_idandrole_idand allow one-to-many relationships between a user and their roles and optionally define a Property (to clarify, this is a captial P Property for the user/role record here, not a flat or apartment) for the relationship.usersdefines usersrolesdefines rolesuser_rolessays what users have what rolesConsider two buildings in the application. Mary lives in Building A and manages Building B. Her role under Building A is tenant; for Building B she's a caretaker. Assigning roles in the
user_rolestable gives the ability to refine her access based on a higher key in the hierarchy—property_id. Now she can query her own flat in Building A but not see others, while still managing Building B through rules like:(Ideally you're using appropriate natural or surrogate keys for these values and not text!)
This may seem like an overly complicated way of doing it but it's exceptionally scalable, flexible, and fast. Abstracting things in the database avoids having to redesign the database later, or compromise performance and flexibility by adding complexity to work around all of the things that were "hard coded" into the initial database design.