Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Post Unanswered: How to design table for authorization...!!!

    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
    Last edited by NirajPatel; 04-03-08 at 23:08.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2008
    Posts
    8
    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....
    Last edited by NirajPatel; 04-04-08 at 12:49.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •