Role-based DB design. Role as user attribute or role-specific tables

user_1477299 0
Viewed 568

I'm developing a web application with several roles for its users. The DB in use is Postgres 10. The roles are very different and have much non-overlapping data so that oftentimes a specific table should belong only to one role and not to another.

In the examples below, I'll use two roles: seller and buyer. The first role is assigned to a user which has some company and sells goods. The second role is assigned to a user who buys goods and can receive gifts. Seller cannot receive gifts and buyer cannot have a company. Both seller and buyer can have an avatar.

I'm considering the following four designs with their cons and pros.

Case 1

case 1

Here we have a role reference table which enumerates all the roles in the system (seller or buyer), user table with password and email of the user which references the role table, avatar, company and gifts_recieved tables reference user table.

PROs

  • No tables have NULL-able attributes

CONs

  • Trigger should be used to check referential integrity. For example, if a user with id=1 is a buyer, we should prevent insertion of a row to the company table with user_id=1. Thus, we should create a trigger on every role-specific table which references user table to guard referential integrity.

Case 2

enter image description here

Here, user table contains nullable role attributes: seller_id and buyer_id (or other roles which are not in the example). Only one of them is not NULL and it defines user's role. All role-specific tables reference either seller or buyer table. Avatar table which is common for both roles continues to reference the user table.

PROs

  • No need for triggers to guard referential integrity of role-specific tables: they all reference either seller or buyer table.

CONs

  • NULL-able role attributes.
  • A single trigger (not triggers for all tables as in case 1) is required to ensure only one of role attributes (seller_id or buyer_id) is not NULL and all other are NULL.

Case 3

enter image description here

To avoid NULL-able role attributes, we'll use Postgres' inheritance for tables (this feature is not supported by MySQL). Here, user table is 'swallowed' into seller and buyer tables. Avatar table has got duplicated and prefixed with role name.

PROs

  • No NULL-able role attributes.
  • No triggers at all.

CONs

  • Tables which are common for different roles are duplicated - a pure waste of resources.
  • Inheritance is Postgres-specific feature.

Case 4

enter image description here

To solve the duplication problem from Case 3, avatar_id was moved to user table from which seller and buyer inherit. Thus, for every table which is common to seller and buyer we'll add an attribute to their parent table (user).

PROs

  • No triggers for referential integrity
  • No table duplication
  • No NULL-able atrrributes

CONs

Question

What solution of the above fit the best DB design practices (if any)? Should user role be an attribute of user table or be a specific table(s)?

Clarifications

  1. A seller can have only one company.
  2. A user identified by email/password pair can be either a seller or a buyer.
user-roles database-design postgresql
1 Answers

I am partial to Case 2.

I would not consider Cases 3 and 4 because of the use of inheritance. I have not visited this topic in many years, but my biggest concern about it was whether developers using a database implementing inheritance would be able to wrap their minds around how to use it.

The trigger is the sticking point for Case 1 in my mind.

Are you aware that you do not need a trigger in Case 2?

create table exclusion_constraint (
  user_id int,
  seller_id int,
  buyer_id int,
  check (case
          when coalesce(seller_id, buyer_id) is null then false
          when seller_id is not null and buyer_id is not null then false
          else true
         end)
);
CREATE TABLE

insert into exclusion_constraint values (1, 100, 200);

ERROR:  new row for relation "exclusion_constraint" violates check constraint "exclusion_constraint_check"
DETAIL:  Failing row contains (1, 100, 200).

insert into exclusion_constraint values (2, null, 200);
INSERT 0 1

insert into exclusion_constraint values (3, 100, null);
INSERT 0 1

insert into exclusion_constraint values (4, null, null);

ERROR:  new row for relation "exclusion_constraint" violates check constraint "exclusion_constraint_check"
DETAIL:  Failing row contains (4, null, null).
user_138083190
Related