The WaypointHR Database

The information on this page is intended as detailed technical information for developers and technical administrators. For more general information, please refer to the main WaypointHR.com site, or post a comment in our forum.

The database schema is entirely defined within PHP, allowing efficient 'runtime' creation pages based on the database setup without having run queries. (For example, automatically setting the maxmimum input length of text boxes for data capture.)

A further advantage is that if a software update requires a structural change to the database, the update can be run automatically from within WaypointHR, without requiring separate execution of SQL by an administrator.

You should always ensure you have a current backup before upgrading WaypointHR or verifying the database.

The Specification File

The db_spec.php file within the components directory contains the specification for the various tables required by WaypointHR. The specification is implemented as a series of associative arrays.

If you need to make changes to the database, it is recommended that this is done by editing the db_spec.php file, then running the 'verify database' option from within the Options module.

(Please note that if you make changes to this file, they will be lost if you update your version of WaypointHR. In a future version, we will provide support for making user changes in a separate inherited file. Also, some changes to a column specification are not currently reflected automatically - for example, character length. In such cases, you should make the change to the specification file and directly to the database.)

Domain and Referential integrity

Where possible, the database creation routines automatically enforce database integrity by creating primary key indexes, specifying enforceable inter-tabe relationships and creating indexes on foreign key columns. In most cases, NULL values are not permitted. To support MySQL's referential integrity rules, where a relationship is optional, the column value can be NULL (denoting no child relationship).

Cascade deletes are implemented at the application layer (and prohibited at the database layer). This is to allow automatic archiving of rows and associated rows in child-related tables.

Please note that your database must support InnoDB for referential integrity rules to be enforced.

Where possible, atomic transactions are used for the archive process. A transaction is created, relevant rows are locked, copied to the archive table, removed from the primary table and the lock committed. If the whole transaction cannot complete, it will be rolled back to its original state.

Please note that your database must support InnoDB and your PHP environment must support the MySQLi (improved) API extension. If either of these requirements are not met, the archive process is implemented with a series of regular MySQL queries that may succeed or fail independently.

Column types

Each column has a 'type' array element, closely representing a MySQL data type (but at a slightly higher level conceptually). For example, a type might be int, varchar, or boolean.

The list of types is also maintained in an associative array in the db_types class (within the components/db_engine.php file). Each type has a creation script and various other parameters. The creation script can contain placeholders (denoted by ) that can be replaced by a corresponding array element in a specific table's column specification. (Where placeholders are used, a default value for the placeholder is included within the type specification in case it is not set in a particular column.)

If adding new columns to a table, it is important to use a type from the list of those specified.

The purpose of SYSID values

Each table and column is given its own sysid value within the database specification file. The purpose of this information is for efficient storage and retrieval of archive information. (The value is currently validated to ensure its presence but is not currently used. It will be implemented in a future version.)

It is important to avoid using a SYSID that could be used by the standard installation in the future. This may lead to misleading information being held in the archive tables. You should therefore use SYSID values of more than 150 (but less than 200).

Any comments or suggestions?

If you have any ideas for improving this page, please post your feedback in our community support forums.

Need additional support?

You can post your question in our forums, or please contact us for professional support and assistance.