This document describes the MySQL tables used in inventory.
Tables are presented in an alphabetical order:
General Note: Why are some of the referenced fields (from one table to another) defined under foreign key constrains, while others are not? This is a design
decision we made: As a rule, 'arch types' like the ones in the Types Tables are NOT linked (to) since cascading deletions from these types may cause a 'snowball' effect and erase large amounts of data. These types are also not allowed to be deleted through the web interface (for this reason). Other references are linked as usuall, since the cascade action is more predictable.
assocHW Table - This table is used to store hardware items associations to other objects
like computers, users and locations.
| Column Name |
Column Type |
Description |
| id |
unsigned int not null auto increment |
Unique id for items in this table (primary key) |
| tbl |
unsigned tinyint not null |
This field indicates which item type the current record is associated with. Item
types are numeric values defined in include/define.php TABLE_XXX section. Currenty the
computer, user and location tables are supported. Zero value indicates that current item
is assigned to the clipboard (usually coupled with 'itemid' field also zero). This column
is indexed. |
| itemid |
unsigned smallint not null |
Reference to id field of item the current hardware item is assigned to. This id number can
only be understood together with the tbl number indicating in which table this id is in. tbl + itemid
fields declare to which item (and of what type) the current hardware item is assigned to. See the meaning
of zero value in this field in the desription of tbl column above. |
| hwid |
unsigned int not null |
Reference to id column in hardware table. This refers to the hardware item associated.
This column is indexed and has a foreign key constraint which will cascade delete. |
| serial |
tinytext |
Stores serial number for current hardware item |
| version |
tinytext |
Information about hardware version |
| origin |
unsigned smallint not null |
References id in vendors table. This is the vendor the hardware was bought from. Column
is indexed and has a foreign key constraint of ON DELETE SET NULL |
| warrenty |
smallint not null |
Warrenty length in days from bought date (see next column). |
| bought |
date |
Date this hardware item was purchased |
computer Table - This table stores computer records.
| Column Name |
Column Type |
Description |
| id |
unsigned int not null auto increment |
Unique id for items in this table (primary key) |
| tag |
tinytext not null |
Computer tag: name/serial-num which identifies computer |
| mfg |
tinytext |
Computer's Manufacturer name |
| model |
tinytext |
Computer's model name |
| serial |
tinytext |
Computer's serial number |
| owner |
unsigned smallint |
References users table, indicates owner of current computer. Index + foreign key
with ON DELETE SET NULL |
| location |
unsigned smallint |
References location table, indicates location of current computer. Index + foreign key
with ON DELETE SET NULL |
| origin |
unsigned smallint |
References vendor table, indicates vendor from which current computer was bought. Index + foreign key
with ON DELETE SET NULL |
| warrenty |
smallint |
number specifying warrenty for this computer in days |
| bought |
date |
Date computer was bought on |
| updated |
timestap(14) |
Auto updating timestap |
contact Table - This table stores contact information for contacts working
for vendors.
| Column Name |
Column Type |
Description |
| id |
unsigned mediumint not null auto increment |
Unique id for items in this table (primary key) |
| name |
tinytext not null |
Contact's full name |
| company |
smallint unsigned not null |
References the id column in vendor table. This is the vendor this contact belongs to.
Column is indexed and foreign key with ON DELETE CASCADE |
| position |
tinytext |
Contact's position in the company (he works for) |
| email |
tinytext |
Contact's email address |
| phone |
tinytext |
Contact's phone number |
| position |
tinytext |
Contact's cell-phone number |
hardware Table - stores hardware items.
| Column Name |
Column Type |
Description |
| id |
int unsigned not null auto increment |
Unique id for items in this table (primary key) |
| mfg |
tinytext |
Name of manufacturer who makes this hardware item |
| id |
smallint unsigned |
References the typeHW table (id field), indicating which type of hardware this tuple is |
| model |
tinytext |
Hardware model |
type Tables - This describes the following tables: langs, netTypes, typeHW,
typeLic, typeSW, typeOS, typeReport. All contain type data for the respective subject; e.g. typeOS
contains types of OS: Windows, Linux, Mac ...
| Column Name |
Column Type |
Description |
| id |
smallint unsigned not null auto increment |
Unique id for items in this table (primary key) |
| name |
tinytext not null |
Type's name |
| icon |
tinytext |
[Optional - this field does not appear in all type tables] file name of icon for this type.
Path to icon's location is stored in the application. (not here) |
| sequence |
smallint unsigned |
Indicates order of types (smallest is first) |
| active |
tinyint unsigned |
Flag 0/1 indicating if this type is active |
license Table - contains licenses for software and OS
| Column Name |
Column Type |
Description |
| id |
int unsigned not null auto increment |
Unique id for items in this table (primary key) |
| company |
tinytext |
(Software) Company for this license |
| software |
tinytext |
Software Name (for current license) |
| version |
tinytext |
Software version for this license |
| total |
smallint unsigned not null |
Number of licenses in this package |
| used |
smallint unsigned not null |
Number of licenses used |
| forOS |
tinyint |
Flag 0/1 indicating if current license is an OS license (1) or software license (0) |
| bought |
date |
Date of purchase for this license |
| vendor |
smallint unsigned |
Reference to vendor table id column. Defines the vendor the license was bought from.
Column is an index and foreign key with ON DELETE SET NULL |
| expire |
smallint unsigned |
Number of days from purchase date the license will expire. |
| serial |
tinytext |
Serial number(s) for this license |
location Table - this table stores locations.
| Column Name |
Column Type |
Description |
| id |
smallint unsigned not null auto increment |
Unique id for items in this table (primary key) |
| name |
tinytext not null |
Location name |
| note |
tinytext |
Free text for location |
| parent |
smallint unsigned |
Location's parent. Used to generate tree-like hirarchy between locations. Also index and
foreign key to id column in this table with ON DELETE CASCADE. |
log Table - holds all log messages generated by the system.
| Column Name |
Column Type |
Description |
| id |
int unsigned not null auto increment |
Unique id for items in this table (primary key) |
| user |
smallint unsigned not null |
Refers to users table id column. Reference to user who generated the log line. Column is indexed. |
| tbl |
tinyint unsigned not null |
Holds reference to table the log item refers to. See include/define.php for TABLE_X defines |
| tuple |
int unsigned not null |
Refers to id field in the table denoted by tbl, thus referring to the object this log line
belongs to. |
| text |
tinytext |
Log line content |
| err |
tinyint |
Flag 0/1 indication error type: 0 = OK, 1 = ERROR |
| stamp |
timestamp(14) |
Automatically updating timestamp to indicate when log was generated. |
network Table - Stores network data for operating systems.
| Column Name |
Column Type |
Description |
| id |
int unsigned not null auto increment |
Unique id for items in this table (primary key) |
| os |
int unsigned not null |
References the OS table id column to indicate which OS the network data belongs to.
Column in indexed and foreign key with ON DELETE CASCADE. |
| type |
smallint unsigned |
References the netType table id column to indicate which network type is represented. |
| data |
tinytext |
Network data itself |
OS Table - This table stores main data for Operating
Systems
| Column Name |
Column Type |
Description |
| id |
int unsigned not null auto increment |
Unique id for items in this table (primary key) |
| name |
tinytext not null |
Name of the OS (can be hostname) |
| type |
smallint unsigned not null |
References the typeOS table id column to indicate which type this OS is (usually: windows, linux etc.) |
| version |
tinytext not null |
String indicating the version of the OS. e.g. 'Fedora Core 2' or 'XP Home'. Together with OS type column gives full info about the OS kind |
| lang |
smallint unsigned |
References the langs table id column. Indicates which (main) language
this OS supports. |
| compref |
int unsigned |
References the computer table id column. Indicates which computer this OS is associated with. This column in an index and foreign key with ON DELETE CASCASE |
| license |
int unsigned |
Refereces the license table id column. Indicates a license assigned to this OS. Enforcement that referenced license type is defined 'forOS' (see
license table) is done through external software and is not enforced directly throught the DB. Column in indexed, and foreign key with ON DELETE SET NULL |
| installed |
date |
Keeps the date the OS was installed |
| installby |
smallint unsigned |
References users table column id. Indicates which user installed this OS.
Column is an index and foreign key with ON DELETE SET NULL |
| updated |
timestamp(14) |
Automatically updated timestamp to indicate when the record was last changed |
patch Table - keeps information about patches available in the system.
| Column Name |
Column Type |
Description |
| id |
int unsigned not null auto increment |
Unique id for items in this table (primary key) |
| name |
tinytext not null |
Name of the patch |
| target |
tinytext |
What OS/software is this patch intended for? |
| level |
tinyint |
Number indicating patch level. These are defined in include/defines.php and include/inv.inc.php |
| notes |
tinytext |
Free text for this patch |
patchOS Table - stores references of patches that are installed in Operating systems.
| Column Name |
Column Type |
Description |
| id |
mediumint unsigned not null auto increment |
Unique id for items in this table (primary key) |
| pid |
int unsigned not null |
References patch table id column. Indicates which patch is installed on the OS. Index + foreign key with ON DELETE CASCADE |
| osid |
int unsigned not null |
References OS table id column. Indicates which OS the patch is installed on. Index + foreign key with ON DELETE CASCADE |
| installed |
date |
Indicates patch installation date |
| notes |
tinytext |
Free text |
report Table - holds information about reports that can be generated by the system.
| Column Name |
Column Type |
Description |
| id |
smallint unsigned not null auto increment |
Unique id for items in this table (primary key) |
| name |
tinytext not null |
Report's Name |
| hash |
varchar(32) not null |
Storing a 32bit MD5 hash value, unique way to identify the report without using the id field.
The hash is made of the string which is the concatination of fileName and funcName fields
|
| display |
tinyint(1) not null |
Boolean value, display this report in the report list or not (some reports are only accessed indirectly) |
| typeId |
smallint unsigned not null |
Refering to the typeReport table, id field. denotes report type. ON DELETE CASCADE |
| fileName |
tinytext not null |
Report filename (where the report function is found). The string stored here is replaced
in this pattern: report.x.php and then the file is included. The report is then executed by
calling the function from the field funcName (see below) |
| funcName |
tinytext not null |
Report function (see fileName above), function to run to execute the report. |
| printFlag |
tinyint(1) |
This is boolean value which enables printing/export of this report. Some reports are functions for
search (or other actions), and should not allow printing or export (not relevant). Other reports actually
display results, so then should be printed/exported. This flag marks which are which. |
software Table - holds information about software defined in the system
| Column Name |
Column Type |
Description |
| id |
int unsigned not null auto increment |
Unique id for items in this table (primary key) |
| company |
tinytext not null |
Name of company publishing the software |
| name |
tinytext |
Software name |
| version |
tinytext |
Software version |
| license |
smallint unsigned |
References typeLic table id column. Indicates which license type
this software is published under. (This is NOT the actually license for the software, just the type). |
| url |
tinytext |
URL for software/publisher's website |
| category |
smallint unsigned |
References the typeSW table id column. Indicates which type/category this software belongs to. |
softwareOS Table - stores information about software
installed on Operating Systems.
| Column Name |
Column Type |
Description |
| id |
mediumint unsigned not null auto increment |
Unique id for items in this table (primary key) |
| osid |
int unsigned not null |
References the OS table id column. Indicates the OS under which the software item is installed. Indexed + foreign key with ON DELETE CASCADE |
| swid |
int unsigned not null |
References the software table id column. Indicates which software item is installed under the OS. Indexed + foreign key with ON DELETE CASCADE |
| license |
int unsigned |
References the license table id column. Indicates which license is associated with the software item installed under the OS. Indexed + foreign key with ON DELETE SET NULL |
users Table - Stores information about Inventory (and other) users.
Note that users are divided into two parts: Hardware Owners and Inventory users. There' more information
in the User Types and Permissions section.
| Column Name |
Column Type |
Description |
| id |
smallint unsigned not null auto increment |
Unique id for items in this table (primary key) |
| login |
tinytext |
User's account username. This is indexed and a unique key. Note that for users that are not allowed
to login to Inventory, (depends on type column), this value may be NULL |
| pass |
tinytext |
User's login password, stored using MySQL's PASSWORD() function. |
| name |
tinytext |
User's full name. |
| type |
tinyint unsigned not null default 0 |
Represents user type by a number starting from zero. See User types
for more info. Types are defined under include/defines.php |
| active |
tinyint unsigned not null default 1 |
Flag representing if the user account is active or not. 1 = active. |
| token |
tinytext |
Login token. This field is used by the login system for user validation. The token is generated
by the Inventory system, and user credentials are checked against the token during the session
(instead of transmitting the password over and over again). Tokens are issued on each login, and act
as a one-time pass key that lasts through the session. |
| duration |
smallint unsigned |
User's session duration in minutes. User's login time is recorded in the 'last' column,
and access is granted to inventory only if session duration did not expire yet. This implements
a forced-logout feature -which limits user's sessions. Setting this field to zero will cancel the
session limitation (user will not be force-logout). |
| location |
smallint unsigned |
Reference to location table id column. Indicates user's location. Indexed + foreign key with
ON DELETE SET NULL |
| span |
smallint unsigned |
Reference to location table id column. Indicates user's permission span root. See
User Types and Permissions section. Column is indexed + foreign key with
ON DELETE SET NULL |
| last |
int unsigned |
Unix style timestamp indicating user's last login time. Used for information and to enforce
session duration timeout. |
| info |
tinytext |
Free Text. |
| display |
tinyint default 10 |
Row display value: When displaying lists for this user, determines the amount of rows that
should be presented in one page. |
| email |
tinytext |
User's email address. |
vendor Table - stores vendor information.
| Column Name |
Column Type |
Description |
| id |
smallint unsigned not null auto increment |
Unique id for items in this table (primary key) |
| name |
tinytext not null |
Vendor's (company) name. |
| address |
tinytext |
Vendor's mailing address. |
| url |
tinytext |
Vendor's website URL. |
| phone |
tinytext |
Vendor's phone number. |
| fax |
tinytext |
Vendor's fax number. |
| email |
tinytext |
Vendor's email address. |
