CODEWITHSUNDEEP
javapostgresqldatabase-designrelational-database
Infant Dev
Infant Dev

Reputation: 1749

Database Design For Self Reference

I need to design a Right table in such a way that it can refer to itself. I already have a roles and users table.

User can have multiple roles and a role can have multiple rights.

But the current problem is some rights should be implicitly selected on selection of one right like if a role has Create Right then it should automatically have View and Delete Rights.

The rights tables currently has id, name and description, how do I modify it to accommodate the above feature? I was thinking about two ways:

  1. Add rightIds column to rights table which will have a comma separated string of all the child right ids. For eg: CREATE right will have ids to VIEW and DELETE right. But the disadvantage is I cannot do a foreign key in this case, and fetching this would also be a pain.
  2. Add a new mapping table which will have two columns parentRightId and childRightId, and both will reference rights as foreign key.

Which one out of these two should be better, or is there a better way to design? Also how should the right class look like in this case. Currently it looks like this:

class Right{
  private Integer id;
  private String name;
  private String description;
}

Upvotes: 0

Views: 1872

Answers (2)

NickJ
NickJ

Reputation: 9559

I suggest a rights table with (among others) the following columns:

RightID int primary key
ParentID int foreign key references Right.RightID
Name varchar
Description varchar

Here, Right refers to itself (ParentID is a foreign key referring to RightID, but can be null)

So if a user with, for example, Right 1 also must have Rights 3 and 4, the table would be:

  RightID   |   ParentID
------------|-------------
        1   |   null
        2   |   null
        3   |      1
        4   |      1

For the next bit, I assume you have a Many-to-Many relationship between Roles and Rights, and therefore have a Join Table called RoleRight.

In RoleRight, if Role 9 has Right 1 (and of course 3 and 4), you would have:

  RoleID    |   RightID
------------|-------------
        9   |       1

Only this one entry is needed, because Rights 3 and 4 are child rights of Right 1 (Anyone with Right 1 automatically has Rights 3 and 4)

This SQL may be used to select all Rights for a given Role (X), including child rights:

select r.RightID from Right r 
join RoleRight rr on rr.RightID=r.RightID or rr.RightID=r.ParentID 
where RoleID=X

Upvotes: 1

Lexandro
Lexandro

Reputation: 785

So you have users and rights and you would like to assign some predefined set of rights to these users. I'm not sure is the self referencing is the best solution (mainly used for trees/hierarchies), but I have an idea that might makes sense:

I recommend to introduce a ROLES table to make it more customizable in the following way and resolve the connection between USERS, ROLES and RIGHTS (that is many-to-many connection) with helper tables:

USERS (id, name) - ROLES (id, name) - RIGHTS(id,name)

Assignments:

USER_ROLES (user_id, role_id)

ROLE_RIGHTS (role_id, right_id)

With this model you can assign more than one roles to a specific users and can create many sets of rights under roles.

Upvotes: 0

Related Questions