[NirajPatel]

Hi, I am new to database design

I have requirement of 'Role based authorization' (Not authentication) for user

I am putting the tables what I have designed...Please let me know if any problems you see in the design..
[I know as I am new, There are 100% chances for poor design... but from you guys [Database Experts] I will learn designing of database ]

---User table //User will enter username

1. USER

USERID NUMBER(10) not null,
USERNAME VARCHAR2(7) unique not null,
FNAME VARCHAR2(25),
LNAME VARCHAR2(25),
CONSTRAINT USER_PK PRIMARY KEY (USERID)

CREATE SEQUENCE USER_S01 MINVALUE 1 MAXVALUE 55555 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;



---There are maximum 8 roles at a time, still taken ROLEID as NUMBER(5) as generating this id using sequence and to give facility to add, delete role

2.ROLE

ROLEID NUMBER(5) not null,
ROLENAME varchar2(20) not null,
CONSTRAINT ROLE_PK PRIMARY KEY (ROLEID)

CREATE SEQUENCE ROLE_S01 MINVALUE 1 MAXVALUE 55555 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;



---Table for authorized user, & I am getting role for user

3. AUTHUSER

AUTHUSERID NUMBER(10) not null,
USERID NUMBER(5) not null,
ROLEID NUMBER(5) not null,
CONSTRAINT AUTHUSER_PK PRIMARY KEY (AUTHUSERID)

ALTER TABLE AUTHUSER ADD CONSTRAINT AUTHUSER_FK1 FOREIGN KEY (USERID) REFERENCES USER (USERID) ON DELETE CASCADE;

ALTER TABLE AUTHUSER ADD CONSTRAINT AUTHUSER_FK2 FOREIGN KEY (ROLEID) REFERENCES ROLE (ROLEID) ON DELETE CASCADE;

CREATE SEQUENCE AUTHUSER_S01 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;




1'st problem is how to restrict entry in table authuser , with unique USERID,ROLEID
I have created unique constraint for this,
ALTER TABLE AUTHUSER ADD CONSTRAINT AUTHUSER_UQ1 UNIQUE(USERID,ROLEID);
Is it ok or any better way !!!?????

Your suggestions will be greatly appretiated, If possible please provide example showing how to improve this design for role based authorization


Many Thanks for looking at this post, please comment.....please provide suggestions to improve this design

[anacedent]

>AUTHUSERID NUMBER(10) not null,
>CONSTRAINT AUTHUSER_PK PRIMARY KEY (AUTHUSERID)
Of what benefit does having AUTHUSERID included in this application?

Why not make the PRIMARY KEY on (USERID, ROLEID)?

Just asking.

[NirajPatel]

Mainly PRIMARY KEY used to ensure uniqueness of row data,

Let's take composite primary key (USERID, ROLEID) than !!! wt's a problem... Nothing but it will degrade performance of database because primary key used for searching and indexing

So if PRIMARY KEY is NUMBER data type than database efficiency increases than having PRIMARY KEY of VARCHAR2 type

So I took AUTHUSERID as primary key &
took USERID as primary key even though I have USERNAME as UNIQUE field with VARCHAR2 data type



If anybody not agree with me...Please comment....