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_asset) 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 */
create or replace 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',
added_date datetime not null COMMENT 'date record was added',
removed_date datetime default NULL COMMENT 'date record was closed',
PRIMARY KEY (_system_id )
) COMMENT='Basically a configuration file equivilent to a windows INI ';
/* used by the auth class */
create or replace table user (
user_id int unsigned not null auto_increment,
name varchar(64) comment 'common name of user',
email varchar(64) comment 'email address of user',
notes text comment 'any notes about user',
pass varchar(64) comment 'encrypted password of user',
access text comment 'sql to determine what records user can view',
added_date date not null default now() comment 'Date record added to database',
removed_date date null default null comment 'Date record marked as removed',
primary key (user_id)
) comment 'user access to program';
/* used by the menu class */
create or replace table menu (
menu_id int unsigned not null auto_increment,
parent_id int unsigned 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) 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';
/* used by report class */
create or replace table report (
report_id int unsigned not null auto_increment,
name varchar(64) not null comment 'Display Name of Report',
query text not null comment 'Query to be executed',
parameters text comment 'All parameters used in above',
screen_report int unsigned default null comment 'Each screen can be assigned a bit and this will show up on a screen',
primary key (report_id)
) comment 'holds definition for report';
/* beginning of the actual tables used by the app, client, site and asset */
create or replace 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',
added_date date not null default now() comment 'Date record added to database',
removed_date date null default null comment 'Date record marked as removed',
primary key (client_id)
) comment 'Stores information about a particlar client/owner';
create or replace 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 default now() comment 'Date record added to database',
removed_date date null default null comment 'Date record marked as removed',
primary key (site_id)
) comment 'Stores information about a particlar physical site';
create or replace table asset (
asset_id int unsigned not null auto_increment,
name varchar(64) comment 'name of the device or asset',
notes text comment 'any notes we want to store',
part_of int unsigned null references asset( asset_id),
asset_type_id int unsigned not null references asset_type( asset_type_id ),
added_date date not null default now() comment 'Date record added to database',
removed_date date null default null comment 'Date record marked as removed',
primary key (asset_id)
) comment 'stores information about an individual device or other asset';
create or replace table asset_relationship (
asset_relationship_id int unsigned not null auto_increment,
source int unsigned not null references asset( asset_id ),
target int unsigned not null references asset( asset_id ),
primary key (asset_relationship_id)
) comment 'joins asset to another asset';
/*
asset_type is a child table of asset, determining what type of asset 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
*/
create or replace table asset_type (
asset_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 asset type',
added_date date not null COMMENT 'date record was added',
removed_date date default NULL COMMENT 'date record was deleted/supserceded',
primary key (asset_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 asset
for example, if 'id' is client_id from the client table, then 'source'
would be 'client' (name of the table);
*/
create or replace 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 asset id',
name varchar(64) comment 'the alias for the asset',
primary key (asset_alias_id)
) comment 'Allows client, site and asset to have multiple names';
create or replace 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 default now() comment 'Date record added to database',
removed_date date null default null comment 'Date record marked as removed',
primary key (client_id,asset_id)
) comment 'A client owns a site';
create or replace table client_asset (
client_asset_id int unsigned not null auto_increment,
client_id int unsigned not null references client( client_id ),
asset_id int unsigned not null references asset( asset_id ),
added_date date not null default now() comment 'Date record added to database',
removed_date date null default null comment 'Date record marked as removed',
primary key (client_asset_id)
) comment 'Links client and asset tables';
create or replace table site_asset (
site_asset_id int unsigned not null auto_increment,
site_id int unsigned not null references site( site_id ),
asset_id int unsigned not null references asset( asset_id ),
added_date date not null default now() comment 'Date record added to database',
removed_date date null default null comment 'Date record marked as removed',
primary key ( site_asset_id )
) comment 'Links site and asset tables';
/* add some indexes */
alter table asset add index (added_date,removed_date);
alter table asset add index (part_of);
alter table asset add index (removed_date);
alter table asset add index (site_id);
alter table asset add index (name);
alter table asset add index (asset_type_id);
alter table site add index (removed_date);
alter table client add index (removed_date);
/* some convenience views */
/* this will combine asset, client and site */
create or replace view view_current_asset_full as
select
asset.name 'asset',
client.name 'client',
site.name 'site'
from
asset
join client using( client_id )
join site using ( site_id )
where
client.removed_date is null
and site.removed_date is null
and asset.removed_date is null;
create or replace view view_asset_name as
select
asset_id 'asset_id',
null 'alias_id',
name 'name'
from asset
union
select
asset_id 'asset_id',
alias_id 'alias_id',
name 'name'
from
asset_alias
where
source = 'asset';
create or replace view view_client_name as
select
client_id 'client_id',
null 'alias_id',
name 'name'
from client
union
select
id 'client_id',
alias_id 'alias_id',
name 'name'
from
alias
where
source = 'client';
create or replace view view_site_name as
select
site_id 'site_id',
null 'alias_id',
name 'name'
from site
union
select
id 'site_id',
alias_id 'alias_id',
name 'name'
from
alias
where
source = 'site';