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;
