title: User-Role-Permission based database design
body: <p>I want to design database for a Java web app in which a <strong>user</strong> can have a particular <strong>role</strong> and that <strong>role</strong> can have permission to access specific tabs based on <strong>PERMISSION_CODE</strong></p>
  
  <p>So, I have created three tables like:  </p>
  
  <pre><code> 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
     );
  </code></pre>
  
  <p>Now, I need a fourth table which contains list of tabs that belongs to particular <strong>PERMISSION_CODE</strong> of <strong>PERMISSIONS_TABLE</strong></p>
  
  <p>What's is coming in my mind is to have a table having columns like</p>
  
  <ul>
  <li>PERMISSION_CODE</li>
  <li>TAB1 (Yes/No)</li>
  <li>TAB2 (Yes/No) and so on..</li>
  </ul>
  
  <p>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:</p>
  
  <ul>
  <li>PERMISSION_CODE</li>
  <li>TAB_NAME</li>
  </ul>
  
  <p>But this way no. of rows will be more. </p>
  
  <p>Is there any way to store tabs in a array like structure? Can any one suggest me an better approach?</p>