The ProVantage Database

Designed from experience for:

Database Objects Count* Quality of Data Integrated Business Rules Accessibility High Level Availability
User Defined Data Types 15 * * * *
Data Columns 3,078 * * *
Tables 599 * * *
Foreign Keys 230 * * *
Check Constraints 188 * * *
Views 559 * * *
Stored Procedures 1,169 * * * *
Triggers 204 * * *
* October 2011

User Defined Data Types - Examples: The "css_currency" type is a numeric field capable of storing a 17-digit number with 2 decimals. The default value is zero and "null" values are not allowed. The "css_bool" type is a single character field that can only contain the values "Y" or "N" to represent "yes" or "no".

Data Columns - Consistent names such as "dt_work", "dt_bill", "dt_open" for dates and "id_work", "id_enter", "id_auth" for timekeepers are used across the tables.

Tables - Useful table names such as "client", "vendor", "fee", "disb", "bill", etc. simplify data access and understanding.

Foreign Keys - Requiring that values in one table exist as a key in another table insures data accuracy. Example: A "fee" transaction can only be recorded if it contains a client number that exists in the "client" table.

Check Constraints - Examples: The balance in a billed fee or disbursement transaction must equal the billed amount minus the amount of payments and write offs. The balance in an unbilled fee must be zero.

Views - Logical groups of columns from related tables that appear as simplified tables for use by report writers or third party query products.

Stored Procedures - Reusable processes that are called by an application to consistently perform tasks based on business rules. The General Ledger transactions associated with transactional processing are created by server based stored procedures rather than by the client application. Other examples include Bill Creation, Bill Reversal, Payment Application, Payment Reversal, Late Fee Calculations, Write Up or Write Down Proration.

Triggers - Processes that occur automatically, regardless of source of action, when a table's rows are inserted, updated or deleted. Triggers protect data integrity by enforcing business rules and starting Stored Procedures to perform tasks such as updating summary figures or logging events.

The ProVantage Database (October 2011)

The ProVantage database is implemented using the latest versions of Microsoft SQL Server. The design includes objects, derived from years of experience, which maintain quality data using self contained business rules. This information rich database offers easy access and reliability.

The design meets four key objectives: Quality Data, Integrated Business Rules, Accessibility and a High Level of Availability.

The basic SQL Server data types have been augmented by (15) ProVantage specific User Defined Data Types. This method of standardization helps meet all four design objectives. For example, the css_currency type is used to store all numeric values that require 2 decimal places. This data type has a default value of zero and does not accept 'NULL' values.

There are 599 tables of information that hold 3,078 distinct columns or fields of data. The tables have useful names such as "client", "vendor", "bill", "fee", etc. and columns are logically named to increase accessibility via third party products such as Excel, Crystal Reports, Access and others. Naming conventions such as starting all date columns with a prefix of "dt" and all entity identifiers with a prefix of "id" decrease development time and reduce support efforts.

Data quality and consistency is assured through the use of 230 Foreign Key constraints on tables that are related to one another. For example, a fee or cost transaction will not be accepted if their client number does not exist in the client table. Conversely, a client master row cannot be deleted if fees or costs exist for that client.

Information quality is further protected through the use of 188 Check Constraints. For instance, regardless of the application issuing the command, the balance of a billed transaction must always equal the amount billed minus the payments and write-offs, or it will be rejected. Similarly, the balance due on any unbilled transaction can only have a value of zero.

It has always been an objective to provide our customers with easy access to their own data. This is accomplished by common sense naming conventions, as well as, through 559 Views that simplify information derived from multiple related tables.

In order to reduce network communications and to incorporate business-processing rules, ProVantage utilizes 590 Stored Procedures. These procedures encapsulate the knowledge of how to perform the steps required for common transactions such as Creating a Bill, Applying a Payment, Creating General Ledger Debits and Credits, Prorating a Write Down, Computing a Late Fee, etc. These procedures are called by the application program and are performed on the server instead of being performed on the user's system.

Finally, the database includes 204 Triggers, which are stored processes that execute automatically whenever a table row is inserted, updated or deleted. Again, regardless of which application is issuing the command, certain processing steps are guaranteed to happen as tables are maintained. Most summary values are maintained via triggers as transaction rows are processed. Logging of critical events is also accomplished through the use of triggers.

The ProVantage database maintains and delivers quality data through the use of integrated business rules. The information is readily accessible and can be provided at a high level of availability.

Mike Mullen is the Director of Research and Development for ProVantage Software, Inc.

Copyright ProVantage Software, Inc. 2011