Subversion Repositories computer_asset_manager_v2

CAMP is designed to be run with a relational database which is supported by the PHP PDO class. It has been developed and tested using MariaDB.

Version 2 is a complete rewrite, with emphasis on getting the basic system right, then adding functionality through modules. Thus, the decision was made to only include the minimum functionality required to track an asset (called a device in the app) through sites and clients. Since modules are not allowed to modify the structure of any tables, this basic database structure will be static, requiring a major revision to modify.

This document describes the structure of the basic database, without modules.

CAMP has two types of tables; system and data. System tables are preceded by an underscore, and are used for program flow.

System tables can have rows edited, updated, added and deleted as necessary. For example, adding a new module often requires adding rows to _system and _menu, and removing a module will remove those entries.

_system - configuration of system, similar to an INI file structure

_menu - describes the menuing system used in the program

_user - defines access to the program, but see below

_user entries should never be removed as the can be linked to various modules. Thus, it has two date fields; added_date and removed_date to allow removal from access while maintaining structual integrity.

The remaining tables should never have rows deleted under normal circumstances, in or to track history and/or maintain data integrity. They have two additional date fields, added_date and removed_date. An edit of any of these tables proceeds as follows:

mark "deleted" row with removed_date = a non-null value

copy the newly removed row's data to new row, modifying the fields necessary (added_date always)

The basis of the whole system is the device (aka asset). An asset has an owner (client) and a location (site). Since we don't want to lose historical data, the linkage between devices, clients and sites is done through join tables, named by the tables they join, thus, client_device links the client and device tables. Additionally, a site is owned by a client, so there is a client_site table.

device <- client_device -> client

device <- client_site -> site

site <- client_site -> client

A device can also be a part of another device in the table. An example is a virtual machine which is hosted by a physical machine. In this case, we use a separate table to mark the "part of" relationship.

device <- device_device -> device

The current (2017) plan is that linkage tables will only be one-to-one.

A device also has one child table, device_type. device_type allows us to categorize devices by function; router, server, workstation, virtual. This relationship does not involve link tables; one of the columns in device is device_type_id, so a device can only have one type.

Finally, there is one weird table, alias. I found computer names change over a period of time, and there can actually be multiple names for a device at the same time, especially when automated reporting is done.

alias is not a true relational table. Instead, it tracks aliases for devices, sites or clients, depending on the source column; a 64 varchar which is designed to store the table name it is aliasing. This is a kludge to solve a problem that exists in real life, but has no elegant solution in a DBMS.

See database.png for a diagram of the basic database.

IMPORTANT: This document describes the actual database layout. However, CAMP does not read the layout from the database. Instead, it uses a hash to describe the database and some PHP classes that automate a lot of the screens. After the database is defined, the hash must be created. See DatabaseDefinition.php in the include directory.