Database design information is relevant to those interested in accessing the FootPrints database from a source outside of FootPrints. The purpose of this section of the document is to provide a general understanding of the FootPrints database structure. If all data manipulation and management is left solely to FootPrints, you do not need to read any further.
With respect to editing table elements outside of FootPrints, changing the values of certain table elements from a source outside of FootPrints may cause errors while using FootPrints. Because FootPrints uses many of the values within its tables to manage data internally, we've indicated the fields, which, if edited improperly, cause errors within FootPrints.
FootPrints’ flexible database design works with the most popular relational databases for fast and efficient performance. All FootPrints data is kept in a single database within the database server (SQL Server, Oracle, etc.). The database can reside on a different server from the application web server or on the same server.
Different functions or groups can be separated into Projects. Each Project is a sub-database of the FootPrints database, with it’s own fields, forms, users and settings. For example, you may have one Project to track service desk activity, and a second Project to track development activity. Projects can be made on-the-fly through the FootPrints web administration interface with no database administration required. There is no limit to the number of Projects that can be created. Incidents are stored in Projects as individual records, called “Issues”.
User contact information is stored in the FootPrints Address Book. Each Project can have it’s own Address Book, or multiple Projects can share a single Address Book. The Address Book is made up of records called “Contacts”. Alternatively, contact data can be dynamically accessed from an outside source, such as Active Directory, Lotus Notes, or Sun ONE Directory Server, via LDAP or to SQLServer, MySQL, Oracel, etc., via Dynamic SQL Link.
The FootPrints application connects to the database via ODBC, for Windows, or via a Perl module (DBD/DBI), for UNIX and Linux.
UNIX/Linux Relational Database versions, including, Oracle, MySQL, Postgres, use DBI/DBD, a popular database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface and makes database calls directly from Perl without having to do a system call to execute an external binary program.
Client programs communicate with the daemon through the use of a named pipe that has read and write access. Client programs send a structure containing a SQL statement to the daemon, and a structure containing a result code, possible data, and the number of rows (in the case of a query) is returned. This persistent-connection approach provides a more optimized performance than directly connecting to and disconnecting from the ODBC data source in each client because it benefits from the caching mechanisms built into most database management systems.
All FootPrints versions use the same table design. FootPrints data is kept in a single database. Each Project within the FootPrints database consists of five tables that hold the information about each record. Address Book data is kept in a separate, single table for each Address Book. Additional data, including schemas, Project settings, and user information are kept in the FootPrints application directory on the web server.
A Project consists of five tables:
The change management approval process involves four additional database tables:
In addition, user contact information is stored in the Address Book. Each Project can have its own Address Book or multiple Projects can share one Address Book. Each Address Book is maintained with a single ABMASTERy table and, optionally, the ABMASTERy_MASTER table:
Table Design and Indexes contains detailed information on the tables and the FootPrints Indexes
FootPrints Database Design
Change Management Database Design