DB Schema for ACL involving 3 subdomains
Posted
by
blacktie24
on Programmers
See other posts from Programmers
or by blacktie24
Published on 2011-03-10T22:33:18Z
Indexed on
2011/03/11
0:18 UTC
Read the original article
Hit count: 288
Hi, I am trying to design a database schema for a web app which has 3 subdomains: a) internal employees b) clients c) contractors. The users will be able to communicate with each other to some degree, and there may be some resources that overlap between them. Any thoughts about this schema? Really appreciate your time and thoughts on this. Cheers!
--
-- Table structure for table locations
CREATE TABLE IF NOT EXISTS locations
(
id
bigint(20) NOT NULL,
name
varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table privileges
CREATE TABLE IF NOT EXISTS privileges
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(255) NOT NULL,
resource_id
int(11) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
--
-- Table structure for table resources
CREATE TABLE IF NOT EXISTS resources
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(255) NOT NULL,
user_type
enum('internal','client','expert') NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Table structure for table roles
CREATE TABLE IF NOT EXISTS roles
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(255) NOT NULL,
type
enum('position','department') NOT NULL,
parent_id
int(11) DEFAULT NULL,
user_type
enum('internal','client','expert') NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Table structure for table role_perms
CREATE TABLE IF NOT EXISTS role_perms
(
id
int(11) NOT NULL AUTO_INCREMENT,
role_id
int(11) NOT NULL,
privilege_id
int(11) NOT NULL,
mode
varchar(250) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Table structure for table users
CREATE TABLE IF NOT EXISTS users
(
id
int(10) unsigned NOT NULL AUTO_INCREMENT,
email
varchar(255) NOT NULL,
password
varchar(255) NOT NULL,
salt
varchar(255) NOT NULL,
type
enum('internal','client','expert') NOT NULL,
first_name
varchar(255) NOT NULL,
last_name
varchar(255) NOT NULL,
location_id
int(11) NOT NULL,
phone
varchar(255) NOT NULL,
status
enum('active','inactive') NOT NULL DEFAULT 'active',
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Table structure for table user_perms
CREATE TABLE IF NOT EXISTS user_perms
(
id
int(11) NOT NULL AUTO_INCREMENT,
user_id
int(11) NOT NULL,
privilege_id
int(11) NOT NULL,
mode
varchar(250) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Table structure for table user_roles
CREATE TABLE IF NOT EXISTS user_roles
(
id
int(11) NOT NULL,
user_id
int(11) NOT NULL,
role_id
int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
© Programmers or respective owner