Code: Select all
DROP TABLE IF EXISTS responsibility;
CREATE TABLE responsibility (
resp_id int(11) NOT NULL auto_increment,
title varchar(255) NOT NULL default '',
PRIMARY KEY (resp_id)
) TYPE=InnoDB COMMENT='Define a responsibility and associate it with a group';
DROP TABLE IF EXISTS user;
CREATE TABLE user (
user_id int(11) NOT NULL auto_increment,
username varchar(16) NOT NULL default '',
full_name varchar(255) NOT NULL default '',
join_date int(11) NOT NULL default '0',
PRIMARY KEY (user_id),
UNIQUE KEY username (username)
) TYPE=InnoDB COMMENT='Define organization staff here';
DROP TABLE IF EXISTS access_group;
CREATE TABLE access_group (
group_id int(11) NOT NULL auto_increment,
description varchar(25) NOT NULL default '',
PRIMARY KEY (group_id),
UNIQUE KEY description (description)
) TYPE=InnoDB COMMENT='Defines access level groupings';
DROP TABLE IF EXISTS access_level;
CREATE TABLE access_level (
acc_id int(11) NOT NULL auto_increment,
level varchar(255) NOT NULL default '',
description varchar(255) NOT NULL default '',
PRIMARY KEY (acc_id),
UNIQUE KEY level (level)
) TYPE=InnoDB COMMENT='All access levels are defined here';
drop table if exists access_level_in_group;
CREATE TABLE access_level_in_group (
acc_id int(11) not null,
group_id int(11) not null,
index (acc_id),
FOREIGN KEY (acc_id) REFERENCES access_level (acc_id) ON DELETE CASCADE,
index (group_id),
FOREIGN KEY (group_id) REFERENCES access_group (group_id) ON DELETE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS department;
CREATE TABLE department (
dept_id int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
org_id int(11) NOT NULL default '0',
PRIMARY KEY (dept_id),
KEY org_id (org_id)
) TYPE=InnoDB COMMENT='Define departments, divisions, etc. here';
DROP TABLE IF EXISTS organization;
CREATE TABLE organization (
org_id int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
PRIMARY KEY (org_id),
UNIQUE KEY name (name)
) TYPE=InnoDB COMMENT='Top-level company and business units are defined here';
DROP TABLE IF EXISTS department_member;
CREATE TABLE department_member (
dept_id int(11) NOT NULL default '0',
user_id int(11) NOT NULL default '0',
index (dept_id),
FOREIGN KEY (dept_id) references department (dept_id) on delete cascade,
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade
) TYPE=InnoDB COMMENT='Users who are in the group';
DROP TABLE IF EXISTS department_org_access;
CREATE TABLE department_org_access (
dept_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (dept_id),
foreign key (dept_id) references department (dept_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='Group-level organization access';
DROP TABLE IF EXISTS department_responsibility;
CREATE TABLE department_responsibility (
dept_id int(11) NOT NULL default '0',
resp_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (dept_id),
foreign key (dept_id) references department (dept_id) on delete cascade,
index (resp_id),
foreign key (resp_id) references responsibility (resp_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='Group responsibilities related to an organization';
-- DROP TABLE IF EXISTS department_structure;
-- CREATE TABLE department_structure (
-- dept_id int(11) NOT NULL default '0',
-- parent_dept_id int(11) NOT NULL default '0',
-- index (dept_id),
-- foreign key (dept_id) references department (dept_id) on delete cascade,
-- index (parent_dept_id),
-- foreign key (parent_dept_id) references department (dept_id) on delete cascade
--
-- ) TYPE=InnoDB COMMENT='Use to create multi-level groups (sub-structure)';
DROP TABLE IF EXISTS organization_business_unit;
CREATE TABLE organization_business_unit (
unit_id int(11) NOT NULL default '0',
company_id int(11) NOT NULL default '0',
index (unit_id),
foreign key (unit_id) references organization (org_id) on delete cascade,
index (company_id),
foreign key (company_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='For business unit definition only ... no sub-units allowed';
DROP TABLE IF EXISTS responsibility_access_level;
CREATE TABLE responsibility_access_level (
resp_id int(11) NOT NULL default '0',
acc_id int(11) NOT NULL default '0',
index (resp_id),
foreign key (resp_id) references responsibility (resp_id) on delete cascade,
index (acc_id),
foreign key (acc_id) references access_level (acc_id) on delete cascade
) TYPE=InnoDB COMMENT='Access levels associated with a responsibility';
CREATE TABLE `user_in_org` (
`user_id` INT NOT NULL ,
`org_id` INT NOT NULL ,
index ( `user_id` ),
foreign key (user_id) references user (user_id) on delete cascade,
index ( `org_id` ),
foreign key (org_id) references organization (org_id) on delete cascade
) type=innodb;
DROP TABLE IF EXISTS user_access_level;
CREATE TABLE user_access_level (
user_id int(11) NOT NULL default '0',
acc_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade,
index (acc_id),
foreign key (acc_id) references access_level (acc_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='User has access level associated with an organization';
DROP TABLE IF EXISTS user_org_access;
CREATE TABLE user_org_access (
user_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='User-level organization access';
DROP TABLE IF EXISTS user_responsibility;
CREATE TABLE user_responsibility (
user_id int(11) NOT NULL default '0',
resp_id int(11) NOT NULL default '0',
org_id int(11) NOT NULL default '0',
index (user_id),
foreign key (user_id) references user (user_id) on delete cascade,
index (resp_id),
foreign key (resp_id) references responsibility (resp_id) on delete cascade,
index (org_id),
foreign key (org_id) references organization (org_id) on delete cascade
) TYPE=InnoDB COMMENT='User has responsibility related to an organization';