Open topic with navigation

About Database Design

Database design information is relevant to those interested in accessing the BMC FootPrints Service Core database from a source outside of BMC FootPrints Service Core.  This section provides a general understanding of the BMC FootPrints Service Core database structure.  If all data manipulation and management is left solely to BMC FootPrints Service Core, you do not need to read any further.

With respect to editing table elements outside of BMC FootPrints Service Core, changing the values of certain table elements from an external source outside of may cause errors. Because BMC FootPrints Service Core uses many of the values within its tables to manage data internally, we have indicated the fields, which can cause errors if edited improperly.

BMC FootPrints Service Core 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.

Different functions or groups can be separated into Workspaces. Each Workspace is a sub-database of theBMC FootPrints Service Coredatabase, 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 BMC FootPrints Service Core 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”.

User contact information is stored in the BMC FootPrints Service Core 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.

The BMC FootPrints Service Core 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.  BMC FootPrints Service Core 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 BMC FootPrints Service Core application directory on the web server.

A workspace consists of five tables:

• MASTERx (where x is the workspace ID)—Holds the core information about an Issue, including built-in and custom fields.
• MASTERx_HISTORY—Contains a history log of actions performed on each issue.
• MASTERx_DESCRIPTIONS—Contains past descriptions and notes (current Description is kept in the MASTERx table).
• MASTERx_ABDATA—Contains the contact information for the Issue.
• MASTERx_TIMETRACKING—Contains time tracking data for Issues.

The change management approval process involves four additional database tables:

• MASTERx_APPROVALSTATES—Used to maintain and query information about the current state of an approval phase. The current state of the process is determined by the state of its final phase.
• MASTERx_APPROVALVOTES/MASTERx_APPROVALVOTESHIST—Used to maintain and query individual user votes on approval phases.  These two tables are the same in design. However, MASTERx_APPROVALVOTES only contains the current (mrPROCID,mrPHASEID,mrID) set, while MASTERx_APPROVALVOTESHIST contains them all. There are duplicate (mrPROCID,mrPHASEID,mrID) sets only if a phase has run multiple times on an mrID.
• MASTERx_APPROVALPHASES—Contains the configuration of a phase.
• MASTERx_APPROVALPROCS—Contains the configuration of a process.

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:  

• ABMASTERy (where y is the Address Book ID)—Contains contact information for users (fields are customizable).
• ABMASTERy_MASTER (where y is the Address Book ID)—Contains contact information for the organizational unit selected in the Address Book to be used as the Master Contact field (such as Department, Company, etc.)  This table was added in FootPrints 7.0, and is automatically created when making a new address book in 7.0+, or accessing an older address book for the first time in 7.0+.  When using the Dynamic LDAP or SQL Address Book, data is not stored locally in the FootPrints Address Book.

For detailed information, refer to Table Design and Indexes.