User-Role-Permission based database design

Question

I want to design database for a Java web app in which a user can have a particular role and that role can have permission to access specific tabs based on PERMISSION_CODE

So, I have created three tables like:

 CREATE TABLE "PAWAN"."USERS_TABLE" 
    (   
    "ID" NUMBER(4,0) NOT NULL ENABLE, 
    "USER_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "PASSWORD" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
    "FIRST_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "LAST_NAME" VARCHAR2(20 BYTE), 
    "CREATED_DATE" DATE DEFAULT sysdate, 
    "ROLE_ID_FK" NUMBER(4,0) NOT NULL ENABLE, 
    PRIMARY KEY ("ID") ENABLE,
    CONSTRAINT "FK_ROLE_ID" FOREIGN KEY ("ROLE_ID_FK")
    REFERENCES "PAWAN"."USER_ROLES" ("ID") ENABLE
    );     

  CREATE TABLE "PAWAN"."USER_ROLES" 
    (   
    "ID" NUMBER(4,0) NOT NULL ENABLE, 
    "ROLE_TYPE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "ROLE_DESCRIPTION" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
    "CREATED_DATE" DATE DEFAULT sysdate, 
    "PERMISSION_ID_FK" NUMBER(4,0) NOT NULL ENABLE, 
    PRIMARY KEY ("ID") ENABLE, 
    CONSTRAINT "PERMISSION_ID" FOREIGN KEY ("PERMISSION_ID_FK")
    REFERENCES "PAWAN"."PERMISSIONS_TABLES" ("ID") ENABLE
    );   


CREATE TABLE "PAWAN"."PERMISSIONS_TABLE" 
   (    
    "ID" NUMBER(4,0) NOT NULL ENABLE, 
    "PERMISSION_CODE" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    "PERMISSION_DESC" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
    "CREATED_DATE" DATE DEFAULT sysdate, 
     PRIMARY KEY ("ID") ENABLE
   );

Now, I need a fourth table which contains list of tabs that belongs to particular PERMISSION_CODE of PERMISSIONS_TABLE

What's is coming in my mind is to have a table having columns like

• PERMISSION_CODE
• TAB1 (Yes/No)
• TAB2 (Yes/No) and so on..

But this is not a good design, because every time a new tab is created, I have to add one more column. One alternative is to have structure like:

• PERMISSION_CODE
• TAB_NAME

But this way no. of rows will be more.

Is there any way to store tabs in a array like structure? Can any one suggest me an better approach?

Answer 1

But this way no. of rows will be more.

Databases are good in managing a huge number of rows, million of rows is not a problem.

Is there any way to store tabs in a array like structure?

No no no - don't go that way.
This breaks the Database normalization rules, esspecially the first normal form thast says:

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.

This aproach (storing many values in one colum) seems attractive at first glance, but some day you will fall into trobles when someone will ask you for a query "give me all users who have access to tab TAB-233".
Some other disadvantages of this approach:

1. each query must parse the array
2. RDBMS can't use indexes on multivalue columns, each query like SELECT ... WHERE tab='TAB-333' must always use a full table scan and is not scaleable.
3. how to update or delete ? Instead of simple UPDATE ... WHERE or DELETE .. WHERE ... each update command must unpack the array, change/delete one value and pack and store changed array back to the column.
4. You can't use any referential integrity on this column.

Please see an aswer to this question for more details.

---

In your model there are the following relationships:

1. The user has roles
2. The role has permission codes
3. The permission code has tabs

---

But there are rather n:n relationships, not 1:n, because I can see that:

1. The role has many users (because several users can have the same role)
2. The permission code has many roles (because several roles can have the same permission code)
3. The tab has many permission codes (because several permission codes can have the same tab)

---

So in this model there would be the following tables:

• USERS(id, user_name, password, etc)
• ROLES(id, role_name, etc )
• PERMISSION_CODES( id, code_name, etc )
• TABS( id, tab_name, etc )
• USER_ROLES( user_id, role_id )
• ROLE_PERMISSIONS( user_id, perrmission_id )
• PERMISSION_TABS( permission_id, tb_id )