Often we see database schemas as large collections of database tables without recognizing discrete and recognizable database table patterns for employees, companies, orders, invoices and customers.
Each well designed database table represents an encapsulated data-based policy instance such as an address, an employee’s biographic information, an invoice header or detail, or a customer’s key contact information. Each such table is characterized by a name, a set of columns and, if well designed, a primary key that is based on column-based business facts that, when valued, selects one and only one row.
A database table is, however, often part of a larger and more comprehensive set of business-based data known as a database object class. A database object class is almost always comprised of multiple database tables for something like employees, companies, orders, invoices, and customers. Within customer, for example, there would be a customer’s basic information, customer contacts, customer addresses, customer locations and the like. Each of these represents a table within the context of the customer.
These collections of functionally related database tables are called database object classes. A set of data from a database object class that represents a policy-based well-defined instance is called a database object. Examples of database objects might, for employee might be Employee Requisition, Employee Candidate, Employee Interviewed, Employee Hired, Employee Assigned, Employee Reviewed, Employee Promoted and Employee Separated. Each named database object would consist of a subset of rows of valued columns across the database object’s tables.
Database object classes almost always have a root database table for a database object class and a set of subordinate database tables related to the root table through primary and foreign keys. An instance of a database object class is called a database object. Database object classes can have multiple levels to their hierarchies.
Database object classes are often derived through use case analysis. Use cases are seen as collections of related functions within discrete, recognized business processes that are focused on employees, companies, orders, invoices, and customers. Use cases often end up as software module collections that collect, store, update and report business information.
Clearly, there is a parallel structure between database objects and identifiable business information systems functions. As the business functions are executed, database objects are created, stored and updated.
Missing from this two-part (business functions and database object classes) paradigm are business events. Here, a business event is the formal identification of the execution of the software that captures or updates database objects. Business functions are fundamentally human-based processes while business events are recordings of database object class action effects in discrete, identifiable, recognizable, trackable, inter-relatable, time-sequenced and reportable forms. That an employee requisition is being considered and formulated is the business function. The creation of a specific employee requisition within a database is not only the instantiation of the database object, but it is also the instantiation of the “who, what, when, where, why and how” of the employee requisition business event.
Each business event execution represents a business event transaction. Once developed into executing software, business event transaction content data is captured through the software layer and is stored in the database as database objects.
The business event transaction’s data is the “who, what, when, where, why, and how” about each time-sequenced, business event-driven execution. This business event context data is created with each business event execution.
Database designs, however, seldom support the capture of data about 1) the business event transactions themselves, 2) the history of business event transactions, or 3) the interrelationships among business event transactions. Rather, the database objects are merely stored in the database.
Seldom also defined and stored are which business events are “possible” versus which business events “actually” occur.
If business events are not explicitly defined and captured, lost is the context of the business event transactions within which the database content data is originally captured. Even more importantly, what also is lost is the context and interrelationship of the myriad of the business’ operations themselves.
Business events can exist in two forms: hierarchies and networks. Business event hierarchies exist in common traditional forms such as Invoice Header and Detail, Order Header and Detail, and the like. Hierarchical business events often mirror the database object data structures for their corresponding database object classes.
Whenever a business event can be invoked by multiple parent events, the business event conforms to a network. For example, the business event, Order Entry, might invoke the child business event, Make a Payment. A different business event, Invoice Processing, accomplished online by a customer might also invoke the same contained business event, Make a Payment.
Other examples include Organization Management. Organizations morph into others and are subsumed and/or divided across time. One organization that purchases three others is a hierarchy, while a new organization that is the result of the merger of two others is clearly a network. Another common example is reference data. A value set that takes one value and divides it into two others is a hierarchy, while the merger of three values into one is a network.
Because business event transactions can have multiple parents as well as multiple children, the business event transaction’s data structure has to be a network. Without a network structure, that is, with only a hierarchical data structure, data redundancy and subsequently conflict will occur. That ultimately leads to update errors as multiple records require update when there should have only been one update.
Solution ApproachFigure 1 illustrates an approach for 1) the business event transactions themselves, 2) the history of business event transactions, or 3) the interrelationships among business event transactions. This approach also enables distinguishing what business events are possible versus those that are actual. Figure 1 shows three major blocks of tables.
The top block contains two classes of tables: “Type Tables” and “Instance” rows. In the top block, there are three tables: Business Event Class, Business Event Class Structure, and Business Event Class Structure Type. These, when instantiated represent all possible business events.
The Business Event Class table holds rows that name each different business event that can occur.
The middle table, Business Event Class Structure, enables the capture of the interrelationships among business event transaction classes. This represents the contextualized history of all possible business events. A stylized set of names was created to illustrate the type of information captured in the business event class structure table. The table, Business Event Class Structure Type, provides a name to the interrelationship. In the first instance, it is “Requires” or “Is Required By.”
The middle table, Business Event Class Structure, has two child-to-parent relationships with Business Event Class. This enables a network. The top relationship line is an “active” relationship. For example, the record, “#1003, 104, 101, 2″ essentially means “Organization contains Charter Document.” The “passive” relationship “bottom line” means “Charter Document is contained in Organization.”
Read in the opposite (passive) way, Key Contract is required by Organization. Note also that #103 Create Key Contact is also required by #102 Create Business Unit. Because the key contact is a “child” of both Organization and Business Unit, the data structure is called a network. That’s because a network uniquely enables a child to be “owned” by multiple parents.
The second block also contains both “type” and “instance” tables. In this second block, the “type” tables are at the bottom of the block and are: Business Event Transaction, Business Event Transaction Structure, and Business Event Transaction Structure Type. This second set of network structures supports the storage and interrelationships among actual business event transactions.
Figure 1: Double Network Structure to Capture both Business Events and their Context
The instance example here is for the Marketing Division. The method of reading the rows is the same as in the top block. Here, once the capture of a Charter is started, an initial first step is the capturing of the Charter’s organization, which in this case is the Marketing Division. Each organization (e.g., the Marketing Division) is represented in the Business Event Transaction table. The “real” data for the Marketing Divisions data is not contained in the Business Event Transaction table. Rather, what is contained is the business event metadata for the Marketing Division: that is, the “when, how, where, why, who, and what” of that “Create Organization” business event transaction. This is shown in the “box” that is below and to the right of the Charter contains Marketing Division business event transaction structure row.
The “real” data for the Marketing Division is represented by the #8943 row at the bottom of Figure 1. This row has the foreign key value, #1003, back to the Marketing Division business event transaction structure row that, in turn, has a foreign key value #5 back to the Business Event Transaction row, Marketing Division. That row, in turn, has a foreign key #1003 back to the Business Event Class Structure row, Charter contains Organization, which, in turn, maps to the business events Create Charter Document and Create Organization.
All in all, this provides non-redundant, discrete, identifiable, recognizable, trackable, inter-relatable, time-sequenced, and reportable business events across all business functions that invoke business events.
While this approach may appear indirect and somewhat complex, it represents industry standard best practice for capturing “bills of materials.” A bill of materials is the strategy for capturing parts, assemblies, and types of assemblies. Here, the parts are the business events, the assemblies are the contextual relationships among business events, and the type of assemblies are the various types of collections of business events.
Two interrelated bills of materials are required because the first represents all possible business events and interrelationships, and the second represents all actual business events and relationships. Married together, reports can be produced that not only indicate what is possible and even required, but also what has actually occurred. That provides the ability to create critical reports of what actually exists and what remains missing.
Through this approach what can be captured are 1) the business event transactions themselves, 2) the history of business event transactions, and 3) the interrelationships among business event transactions.
