Rev 1 | Blame | Last modification | View Log | Download | RSS feed
connect camp2;
drop table if exists users;
create table users (
id int unsigned not null auto_increment,
username varchar(64) unique not null comment 'login user name',
password char(32) not null comment 'encrypted password',
group_id int unsigned references groups(id),
where_clause text comment 'appended to all queries used to limit the access the user has to records',
email varchar(64) comment 'email address so we can contact them',
notes text comment 'Any notes you want',
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'logins to system';
drop table if exists groups;
create table groups (
id int unsigned not null auto_increment,
name varchar(100) unique not null comment 'name of this group',
created datetime not null,
modified datetime default null,
primary key (id)
) comment 'holds users groups for the ACL code';
drop table if exists user_group;
create table user_group (
user_id int unsigned not null references users(id),
group_id int unsigned not null references groups(id),
primary key (user_id,group_id)
) comment 'maps users to groups';
drop table if exists owners;
create table owners (
id int unsigned not null auto_increment,
name varchar(64) not null unique comment 'Name of owner',
notes text comment 'Any notes you want',
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'holds owners information';
drop table if exists sites;
create table sites (
id int unsigned not null auto_increment,
name varchar(64) not null unique comment 'Name of site',
notes text comment 'Any notes you want',
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'holds site information';
drop table if exists device_types;
create table device_types (
id int unsigned not null auto_increment,
name varchar(64) not null unique comment 'short name for display',
system tinyint(1) comment 'If true (1) this is a system. Set to 0 for printers etc...',
notes text comment 'Any notes you want',
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'holds types of devices, such as computer, router, etc...';
drop table if exists attributes;
create table attributes (
id int unsigned not null auto_increment,
name varchar(64) not null unique comment 'short name for display',
notes text comment 'Any notes you want',
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'lookup table of possible attributes to devices';
drop table if exists devices;
create table devices (
id int unsigned not null auto_increment,
serial_number varchar(64) null comment 'serial number of device',
name varchar(255) not null comment 'name of device',
parent_id int unsigned null references devices(id),
device_type_id int unsigned not null references device_types(id),
site_id int unsigned null references sites(id),
owner_id int unsigned not null references owners(id),
notes text comment 'Any notes you want',
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'holds computers, cards that make them up, etc...';
drop table if exists device_aliases;
create table device_aliases (
id int unsigned not null auto_increment,
name varchar(64) not null unique comment 'short name for display',
device_id int unsigned not null references devices(id),
notes text comment 'Any notes you want',
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'aliases for devices';
drop table if exists attributes_devices;
create table attributes_devices (
id int unsigned not null auto_increment,
device_id int unsigned not null references devices(id),
attributes_id int unsigned not null references attributes(id),
attribute_value text comment 'the actual value of the attribute in question',
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'Each device can have multiple attributes and values';
drop table if exists files;
create table files (
id int unsigned not null auto_increment,
original_name varchar(64) not null comment 'original file name',
name_on_disk varchar(64) not null unique comment 'name of file on disk',
mime_type_id int unsigned default null references mime_types(id),
device_id int unsigned not null references devices(id),
site_id int unsigned null references sites(id),
owner_id int unsigned not null references owners(id),
created datetime not null,
removed datetime default null comment 'Date removed',
primary key (id)
) comment 'Files which can be associated with an owner site or device';
drop table if exists mime_types;
create table mime_types (
id int unsigned not null auto_increment,
name varchar(64) not null comment 'the web definition of the mime type',
embed tinyint(1) default 0 comment 'If true (1) can be viewed on web page',
extension varchar(5) comment 'standard file extension used for this MIME type',
primary key (id)
) comment 'just keeps a list of standard MIME types for use with filess';
drop table if exists menus;
create table menus (
id int(10) unsigned not null auto_increment,
parent_id int(10) unsigned default null comment 'If this is a submenu the id of the parent',
caption varchar(20) not null comment 'The actual caption displayed',
url varchar(120) default null comment 'the url of the page/script to call or null if this contains sub-options',
primary key (id)
) comment='We keep the entire menu structure here so modules can modify';
drop table if exists menu_group;
create table menu_group (
menu_id int unsigned not null references menus(id),
group_id int unsigned not null references groups(id),
primary key (menu_id,group_id)
) comment 'gives a group access to a menu';