Sign In/My Account | View Cart  

advertisement

AddThis Social Bookmark Button

Article:
  Using Tomcat 4 Security Realms
Subject:   suggestion for more robust database schema
Date:   2004-04-27 12:51:40
From:   pmocek
The article's database schema would allow the creation of a user_role for a nonexistent user_name or role_name. It also allows a user or role to be deleted when a corresponding user_role exists.


For a DBMS which supports foreign key constraints (such as PostgreSQL), I suggest the following schema:


CREATE TABLE users (
user_name VARCHAR(15) PRIMARY KEY,
user_pass VARCHAR(15) NOT NULL
);


CREATE TABLE roles (
role_name VARCHAR(15) PRIMARY KEY
);


CREATE TABLE user_roles (
user_name VARCHAR(15) REFERENCES users ON DELETE CASCADE,
role_name VARCHAR(15) REFERENCES roles ON DELETE CASCADE,
PRIMARY KEY(user_name, role_name)
);