Database design information is relevant to those interested in accessing the FootPrints database from a source outside of FootPrints. This section provides 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 an external source outside of may cause errors. Because FootPrints uses many of the values within its tables to manage data internally, we have indicated the fields, which can cause errors if edited improperly.
FootPrints flexible database design works with the most popular relational databases for fast and efficient performance. All 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.
Workspaces
Different functions or groups can be separated into Workspaces. Each Workspace is a sub-database of theFootPrintsdatabase, with its own fields, forms, users and settings. For example, you may have one Workspace to track service desk activity, and a second Workspace to track development activity. Workspaces 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 Workspaces that can be created. Incidents are stored in Workspaces as individual records, called “Issues”.
Address Book
User contact information is stored in the FootPrints Address Book. Each workspace can have it’s own Address Book, or multiple workspaces 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, Oracle, etc., via Dynamic SQL Link.
Database Connection
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.
Table Design
All FootPrints versions use the same table design. FootPrints data is kept in a single database. Each workspace within the 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, workspace settings, and user information are kept in the FootPrints application directory on the web server.
A workspace 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 workspace can have its own Address Book or multiple workspaces can share one Address Book. Each Address Book is maintained with a single ABMASTERy table and, optionally, the ABMASTERy_MASTER table:
For detailed information, refer to Table Design and Indexes.