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. My primary question is if I should split up the roles into separate tables for each domain, as in internal_roles, client_roles, and expert_roles, or if I should just keep them in the one roles table, with a user_type field to differentiate the roles. Also, I'm wondering if having this type field will cause it to violate the 3rd normal form? 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;