Subversion Repositories computer_asset_manager_v2

/* 

   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';