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.