Rev 20 | Blame | Last modification | View Log | Download | RSS feed
/*
mySQL Database for Computer Asset Management Program
No records are deleted by default. If record is to be deleted
it is marked as removed (removed_date not null)
When joining tables (ie client_device) is modified, the old record is
marked removed and a new record added, to give history of device
*/
/* used as a configuration file for the app as a whole */
drop table if exists _system;
create table _system (
_system_id int unsigned not null auto_increment,
group_name varchar(64) NOT NULL COMMENT 'used to group keys together',
key_name varchar(64) NOT NULL COMMENT 'key into this value',
theValue text null COMMENT 'the actual value of this entry',
primary key (_system_id )
) COMMENT='Basically a configuration file equivilent to a windows INI ';
/* used by the auth class */
drop table if exists _user;
create table _user (
_user_id int unsigned not null auto_increment,
username varchar(32) not null comment 'user name for logging in',
name varchar(64) comment 'common name of user',
email varchar(64) comment 'email address of user',
notes text comment 'any notes about user',
passwd varchar(256) comment 'encrypted password of user',
access text comment 'sql to determine what records user can view',
added_date date not null comment 'Date record added to database',
removed_date date default null comment 'Date record marked as removed',
primary key ( _user_id )
) comment 'user access to program';
/* used by the menu class */
drop table if exists _menu;
create table _menu (
_menu_id int unsigned not null auto_increment,
parent_id int unsigned default null comment 'If this is a submenu the id of the parent' REFERENCES _menu(_menu_id),
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 (_menu_id)
) comment 'We keep the entire menu structure here so modules can modify it';
/* beginning of the actual tables used by the app, client, site and device */
/*
A device is owned by a client (entity). This is a list of available
clients who can own things.
*/
drop table if exists client;
create table client (
client_id int unsigned not null auto_increment,
name varchar(64) comment 'Name of the client',
notes text comment 'Any notes you want to enter',
internal_notes text comment 'private notes accessible only to technicians',
added_date date not null comment 'Date record added to database',
removed_date date default null comment 'Date record marked as removed',
primary key (client_id)
) comment 'Stores information about a particlar client/owner';
/*
A device can be on a site. This is a list of sites which can house
devices
*/
drop table if exists site;
create table site (
site_id int unsigned not null auto_increment,
name varchar(64) comment 'Name of the site',
notes text comment 'Any notes you want to enter',
added_date date not null comment 'Date record added to database',
removed_date date default null comment 'Date record marked as removed',
primary key (site_id)
) comment 'Stores information about a particlar physical site';
/*
The basis for the program. In our context, an asset is a computer or
other device.
*/
drop table if exists device;
create table device (
device_id int unsigned not null auto_increment,
name varchar(64) not null comment 'name of the device or device',
notes text default null comment 'any notes we want to store',
device_type_id int unsigned not null references device_type( device_type_id ),
added_date date not null comment 'Date record added to database',
removed_date date default null comment 'Date record marked as removed',
primary key (device_id)
) comment 'stores information about an individual device or other device';
/*
allows devices to be related to each other, ie a "part of" scheme, where
for example, a virtualized server may be 'part of' a physical machine.
By using a child table, we can track movement across this relationship
*/
drop table if exists device_device;
create table device_device (
device_device_id int unsigned not null auto_increment,
device_id int unsigned not null references device( device_id ),
parent_id int unsigned not null references device( device_id ),
added_date date not null comment 'Date record added to database',
removed_date date default null comment 'Date record marked as removed',
primary key (device_device_id)
) comment 'joins device to another device';
/*
device_type is a child table of device, determining what type of device it is]
such as computer, printer, router, whatever.
flags currently used as 0 is non system and 1 is system, though this could
be expanded in the future
*/
drop table if exists device_type;
create table device_type (
device_type_id int(10) unsigned NOT NULL auto_increment,
name varchar(64) not null COMMENT 'the visible displayed name',
flags int unsigned default 1 comment 'flags for this device type',
added_date date not null COMMENT 'date record was added',
removed_date date default NULL COMMENT 'date record was deleted/supserceded',
primary key (device_type_id)
) comment='simply a list of device types ie computer printer whatever';
/*
this is actually a join with multiple tables, depending on what the
name is associated with, client, site or device
for example, if 'id' is client_id from the client table, then 'source'
would be 'client' (name of the table);
*/
drop table if exists alias;
create table alias (
alias_id int unsigned not null auto_increment,
source varchar(64) comment 'the table this alias comes from',
id int unsigned not null comment 'the client, site or device id',
name varchar(64) comment 'the alias for the device',
primary key (alias_id)
) comment 'Allows client, site and device to have multiple names';
/*
links a site to a client. A site is "owned" by a client
*/
drop table if exists client_site;
create table client_site (
client_site_id int unsigned not null auto_increment,
client_id int unsigned not null references client( client_id ),
site_id int unsigned not null references site( site_id ),
added_date date not null comment 'Date record added to database',
removed_date date default null comment 'Date record marked as removed',
primary key (client_site_id)
) comment 'A client owns a site';
/*
A device is owned by a client. Ownership can be tracked by removing
a device from one client and adding it to another.
*/
drop table if exists client_device;
create table client_device (
client_device_id int unsigned not null auto_increment,
client_id int unsigned not null references client( client_id ),
device_id int unsigned not null references device( device_id ),
added_date date not null comment 'Date record added to database',
removed_date date default null comment 'Date record marked as removed',
primary key (client_device_id)
) comment 'Links client and device tables';
/*
A device is at a location. Location history can be tracked by removing
and adding a device to a new location
*/
drop table if exists site_device;
create table site_device (
site_device_id int unsigned not null auto_increment,
site_id int unsigned not null references site( site_id ),
device_id int unsigned not null references device( device_id ),
added_date date not null comment 'Date record added to database',
removed_date date default null comment 'Date record marked as removed',
primary key ( site_device_id )
) comment 'Links site and device tables';
/* add some indexes */
alter table device add index (added_date,removed_date);
alter table device add index (removed_date);
alter table device add index (name);
alter table device add index (device_type_id);
alter table client_site add index ( client_id,site_id );
alter table site add index (removed_date);
alter table client add index (removed_date);
/* preload some data */
/* basic menu; home and logout */
insert into _menu values ( null, null, 'Home Page', 'index.html' );
insert into _menu values ( null, null, 'Log Out', 'logout.html' );
/* one user, admin, with no password for initial access */
insert into _user( username,added_date) values ( 'admin', now() );
/* insert into device_type (name,flags, added_date ) values ( 'Computer',1,now() ); */