The REA Approach to Database Modeling:View Integration, Creating an Enterprise-Wide REA Model.

View Integration: Creating an Enterprise-Wide REA Model

The view modeling process described in the previous section produced an individual REA model of the revenue cycle for Apex Supply. This section explains how multiple REA diagrams, each created in its own view modeling process, are integrated into a single global or enterprise model. The section then explains how the enterprise model is implemented into a relational database and user views constructed. The view integration process involves three basic steps:

1. Consolidate the individual models.

2. Define the primary keys, foreign keys, and attributes.

3. Construct the physical database and produce user views.

STEP 1. CONSOLIDATE THE INDIVIDUAL MODELS

Because Apex is a wholesale supply company with no production facilities, model consolidation for them will include the previously developed revenue cycle model (Figure 10-8) and the expenditure cycle models for purchases/cash disbursements and payroll illustrated in Figures 10-10 and 10-11, respectively.

A brief explanation of the resources, events, agents, and cardinalities for two expenditure cycle models is provided next.

Purchases and Cash Disbursement Procedures

Figure 10-10 shows three event entities in Apex’s purchases and cash disbursement system. The first of these, the Order Product entity, is a support event that does not directly increase the Inventory (resource) entity. Upon recognizing the need for inventory, which sales to customers (revenue cycle) depleted, the purchasing clerk (internal agent) selects a supplier (external agent) and places the order. This act does not constitute an economic event, but is a commitment to buy. The link from the event entity to the Inventory entity indicates that the records will be adjusted to show that the items in question are on order. The quantity on hand, however, will not be increased at this time. The on-order information will prevent items from being accidentally reordered and will assist customer service clerks in advising customers as to the status of inventory and expected due dates for out-of-stock items. The 1:M association between Supplier and Order Product indicates that each order goes to only one supplier and that a particular supplier may have received zero or many orders during the period.

The second event entity is Receive Product, which is an economic event that causes a change in an economic resource. This is the receive half of the exchange and increases inventory. Goods are received from the supplier and the receiving clerk processes them. This involves counting, inspecting, transporting the products into the warehouse, transferring title to Apex, and updating the inventory records. The 0,1 cardinality in the association between the Order Product and Receive Product entities implies that at any point in time, an order may exist (occurrence of Order Product) that has not yet been received (no occurrence of Receive Product).

The third event represented in the diagram is Disburse Cash. This is the economic event that constitutes the give half of an economic exchange. In this case, it causes the Cash resource to be decreased. The 1:M association with the Supplier entity implies that each disbursement is made to only one supplier, but each supplier may receive zero or many disbursements during the period. The 1:M association between Dis- burse Cash and Receive Product implies that each product receipt is paid in full (no multiple partial pay- ments), but many receipts may be combined and paid with a single disbursement to reduce check writing.

Notice that M:M associations exist between the Order Product and Inventory entities and between the Receive Product and Inventory entities. These illustrate that orders placed with suppliers and received from them may contain one or many items. From the opposite side, these associations signify that each item of inventory may have been ordered and received zero or many times in the period. As previously discussed, each M:M association needs to be resolved by adding a linking entity. Tables for the linking entities will ultimately be created in the database, but the entities will not be included in the REA diagram. Keep in mind also that the internal agents are represented in the REA diagram as separate entities. This is done to illustrate more clearly their respective roles. In reality, these agents are instances of the Employee entity and will be collapsed into a single Employee table in the final database.

Payroll Procedures

The REA diagram in Figure 10-11 describes the data model for Apex Supply’s payroll procedures. The model consists of only two economic events: Get Time and Disburse Cash. The Get Time event is the receive half of the economic exchange. This involves a worker (internal agent) giving up his or her time, which is represented by the Employee Services resource. The supervisor (external agent) assumes control of the resource. Unlike the tangible economic resources of cash and inventory, time does not have a stock flow element and cannot be stored. The Get Time event increases time, and various task performance events simultaneously decrease time. Earlier in the chapter, the Employee Services resource was discussed as a possible resource to be modeled in the Apex Supply database. In situations in which employee services are directly tracked to products produced or services rendered to clients (that is, consulting, legal services, or public accounting), it makes sense to model this resource. Because Apex does not track employee time to such activities as individual customers served or orders taken, transforming this entity into a physical data- base table serves no purpose. To maintain consistency with the REA modeling convention that each event must be linked to a resource, however, Employee Services is included in Figure 10-11 as a shadow resource (dotted lines) only. It will not be not be modeled in the final enterprise-wide REA diagram.

The Get Time event captures the daily time-giving instances of employees through a timekeeping mechanism such as an electronic time clock. For salaried employees, the time-capturing process may sim- ply involve the passage of time. The zero cardinality in the associations between the Get Time and the Worker and Supervisor entities reflects the possibility that some employees may not have contributed time during the period. This would include, for example, new employees or employees on sick leave.

The Disburse Cash event is the give half of the economic exchange. This involves distributing cash to an employee (now the external agent) for services rendered. The payroll clerk (internal agent) participates in this event, which reduces the cash resource. The association between the Disburse Cash and Get Time events reflects the timing differences between employees giving up their time and receiving payment for it, as they are usually not paid daily. Typically, employees work for a week, two weeks, or even a month before getting paid. Therefore, the 1,M cardinality on the Get Time side of the association implies that at least one and possibly many instances of Get Time will exist for each instance of Disburse Cash. The 0,1 cardinality on the Disburse Cash side of the association implies that at a point in time (midweek), a Get Time instance will exist that has not yet been paid. Each Get Time instance, however, is paid only once.

Merge Individual REA Diagrams

With the individual REA diagrams created and explained, we are now ready to consolidate them into a single enterprise-wide diagram (see Figure 10-12). By flipping the expenditure cycle diagrams to create a mirror image effect, the common resources of Inventory and Cash are centered in the diagram. These are flanked by two constellations of events, which increase and decrease them. The agents form periphery constellations in the diagram.

To simplify the diagram, redundant event, agent, and resource entities have been collapsed into a single entity where possible. For example, the Disburse Cash event, which is an element of both the Payroll and Purchase/Cash Disbursement procedures for Apex Supply is represented only once in the consolidated model. Also, the Cash and Inventory entities are present only once on the consolidated diagram. To maintain perspective as to the roles played by internal agents, they are depicted as individual entities rather than collectively as Employee. Finally, to avoid crossing association lines between entities, the Supplier and Customer agents appear more than once in the diagram.

STEP 2. DEFINE PRIMARY KEYS, FOREIGN KEYS, AND ATTRIBUTES

With the data model constructed, we are now ready to design the relational database tables. This involves determining primary keys, assigning foreign keys, and defining the attributes of the tables. A separate table will be constructed for each valid entity in the REA integrated diagram in Figure 10-12. This will require 18 tables, which are explained in the following paragraphs:

• The ten internal agents represented in Figure 10-12 will be collapsed into a single Employee table.

• The two external agents (Customer and Supplier) will each require a separate table.

• The two resources Inventory and Cash will each be a table.

• The eight events will require a table each.

• The five M:M relations represented in the diagram will each require a linking table.

RULES FOR PRIMARY KEYS AND ATTRIBUTES. Table 10-1 presents the 18 tables in Apex’s database along with their primary keys, foreign keys, and attributes. The determination of primary keys and attributes comes from an understanding of each table’s purpose, which results from a detailed analysis of user needs. The database designer should select a primary key that logically and uniquely defines the nonkey attributes in the table. In some cases, this is accomplished with a simple sequential code such as an Invoice Number, Check Number, or Purchase Order number. In other situations, block codes, group codes, alphabetic codes, and mnemonic codes are better choices. We discussed the advantages and disadvantages of various coding techniques in detail in Chapter 2.

Some table attributes are common to all organizations and can be derived from common sense and adherence to best practices. Other attributes are unique to specific applications and can be derived only from thorough and detailed analyses of individual user views. Each attribute assigned to a table should, how- ever, appear directly or indirectly (a calculated value) in one or more user views. If data stored in a table are not used in a document, report, or a calculation that is reported in some way, then it serves no purpose and should not be part of the database.

RULES FOR FOREIGN KEYS. The degree of association between tables (that is, 1:1, 1:M, or M:M) determines how foreign keys are assigned. We discussed the key-assignment rules for linking tables in Chapter 9, and they are briefly reviewed in this section.

KEYS IN 1:1 ASSOCIATIONS. In 1:1 associations, one side of the association will typically have a minimum cardinality of zero. When this is the case, the primary key of the table with the 1,1 cardinality should be embedded as a foreign key in the table with the 0,1 cardinality. Reversing this rule will create a table structure in which the 1,1 cardinality table contains instances of foreign keys with a null (blank) value. Although such a link will work, it is a poor table design that can lead to inefficiencies and possible processing errors. By following the rule, however, all foreign key values in the table on the 0,1 cardinality side of the association will be non-null. For example, we can see from Table 10-1 that the primary key for the Verify Availability table (Inquiry Number) is assigned as a foreign key to the Take Order table.

KEYS IN 1:M ASSOCIATIONS. Where a 1:M association exists between tables, the primary key of the 1 side is embedded in the table of the M side. For example, the primary key of the Employee table (Employee Number) has been assigned as a foreign key to the Verify Availability, Take Order, and Ship Product tables.

KEYS IN M:M ASSOCIATIONS. Tables in an M:M association cannot accept an embedded foreign key from the related table. Instead, the database designer must create a separate link table to contain the foreign keys. By inserting a link table between the original tables, the M:M association is transformed into two 1:M associations (see Figure 10-9). The link table can now accept the primary keys of the tables on the 1 side of the two new associations. This process results in a combined (composite) key that serves as a primary key for defining the attributes (if any) in the link table. Each component of the composite key also serves as a foreign key for locating related records in the associated tables. Five link tables are presented in Table 10-1: Inventory- Verify Link, Inventory-Order Link, Inventory-Ship Link, Ord-Prod-Inven Link, and Rec-Prod-Inven Link.

NORMALIZED TABLES. The assignment of primary keys and attributes to relational tables must always comply with the rules of normalization discussed in Chapter 9. Recall that improperly normalized tables exhibit negative operational symptoms called anomalies, including the update anomaly, the insertion anomaly, and the deletion anomaly. One or more of these anomalies will exist in tables that are not normalized to the third normal form (3NF) level. These anomalies are caused by structural problems within tables known as repeating groups, partial dependencies, and transitive dependencies. Normalization involves systematically identifying and removing these dependencies from the table(s) under review. Tables in 3NF will be free of anomalies and will meet two conditions:

1. All nonkey attributes will be wholly and uniquely dependent on (defined by) the primary key.

2. None of the nonkey attributes will be dependent on (defined by) other nonkey attributes.

The database tables in Table 10-1 are in 3NF, but contain only minimal attributes. Keep in mind that data- bases are not static. As user needs change and additional user views are developed, additional attributes and perhaps additional tables may need to be included in the database. These additions must comply with normalization rules to preserve the structural integrity of tables and avoid anomalies. For a complete discussion of anomalies, dependencies, and normalization techniques, please review the appropriate sections of Chapter 9 and its appendix.

STEP 3. CONSTRUCT PHYSICAL DATABASE AND PRODUCE USER VIEWS

At this point, the database designer is ready to create physical relational tables based on the descriptions in Table 10-1. Once the tables have been constructed, some of them must be populated with data. Resource and agent tables must be initialized with data values such as inventory quantities on hand, customer names and addresses, and employee data. The new system will start operations with beginning values for the attributes of these tables. In contrast, event tables start out empty and will be populated through actual transaction processing activities.

The resulting database should be rich enough to support the information needs of all users of the sys- tem being modeled. This includes the needs of accountants, operations personnel, and management. The reports, computer screens, and documents that constitute these views are derived from structured query language commands and computer programs that access the normalized tables in the database and navigate between them using the foreign keys as links. In this section, we present some examples of the reports that can be produced from rich event tables.

PRODUCING FINANCIAL STATEMENTS AND OTHER ACCOUNTING REPORTS. In a traditional system, financial statements are typically prepared from general ledger accounts, whose values are derived from journal voucher postings. Accounting artifacts, including journals, ledgers, and double- entry accounting, however, are not entities in an REA model. Instead, these traditional accounting mechanisms are reproduced from the event tables. To illustrate, Figure 10-13 presents the structures for several relational tables in the Apex database. These table structures correspond to the tables listed in Table 10-1, but some of the attributes have been omitted to simplify the figure. The figure demonstrates how financial statement accounting figures can be constructed from underlying event data. The calculations are:

Total Sales = The sum of the Invoice Amount attribute in the Ship Product table for all items shipped on or before the year-end closing date.

Accounts Receivable = Total Sales minus the sum of the Receive Cash table’s Amount attribute for all remittances received on or before the year-end closing date.

Cost of Goods Sold = Sum of Quantity sold in the Inventory-Ship Link table multiplied by the Unit Cost attribute in the Inventory table.

Inventory = Quantity On Hand attribute multiplied by Unit Cost attribute in the Inventory table.

Accounting figures extracted in this way from REA tables can be used to prepare income statements, balance sheets, and even journal entries as illustrated with the journal voucher report in Figure 10-14.

PRODUCING MANAGEMENT REPORTS FROM REA TABLES. A key objective of REA is to create a database capable of supporting multiple user views. This section illustrates examples of nonaccounting user reports that Apex Supply could produce from their database. Figure 10-15 depicts an inventory status report that their purchasing agent would use. This report identifies items to be ordered and the

respective suppliers. The data for this report come from the Inventory and Supplier tables in Table 10-1. Figure 10-16 is a sales activity report organized by customer and product, which the sales manager would use. The report is constructed from data in the Customer, Ship Product, Inventory-Ship link, and Inventory tables. Figure 10-17 is a customer inquiry report. The purpose of this report is to track customer inquires against actual sales. The report indicates how much time is spent on each customer inquiry and whether it led to a sale. The Customer, Verify Availability, Inventory-Verify Link, Inventory, and Take Order tables provide the data for the report.

REA AND VALUE CHAIN ANALYSIS

The competitive advantages to organizations that adopt the REA approach are most clearly seen from the perspective of the value chain. These are the activities that add value or usefulness to an organization’s products and services. To remain competitive, organizations must differentiate between their various business activities, prioritizing them on the basis of their value in achieving organizational objectives. This means being adaptable and responsive to changes in the environment, including their relationships with suppliers, customers, and other external entities that influence performance. Modern decision makers need information systems that help them look beyond their internal operations to those of their trading partners.

One approach adopted for this purpose is known as value chain analysis. This analysis distinguishes between primary activities and support activities: primary activities create value; support activities assist in the achievement of the primary activities. By applying value chain analysis, an organization can look beyond itself and maximize its ability to create value, for example, by incorporating the needs of its customers in its products or accommodating the flexibility of its suppliers in scheduling its production.

Traditional information systems are not well suited to supporting value chain activities. Organizations that have applied value chain analysis have generally done so outside the traditional accounting information system by providing such information separately to the decision makers. Frequently, this involved the creation of separate information systems, which results in data redundancy, data concur- rency, and system integration problems. As a single information system framework capable of capturing generic and fine-grained data, REA can overcome these problems and is capable of providing the following advantages.

1. The REA approach to modeling business processes helps managers focus on the key elements of economic events and identify the nonvalue-added activities that can be eliminated from operations.

Improving the operational efficiency of individual departments thus generates excess capacity that can be redirected to increase the overall productivity of the firm.

2. The storage of both financial and nonfinancial data in a common database reduces the need for multiple data collection, storage, and maintenance procedures.

3. Storing financial and nonfinancial data about business events in detailed form permits a wider range of management decisions by supporting multiple user views.

4. The REA model provides managers with more relevant, timely, and accurate information. This will translate into better customer service, higher-quality products, and flexible production processes.

REA COMPROMISES IN PRACTICE

The advantages to operational efficiency, systems integration, and value chain analysis have drawn great attention to REA as a theoretical model for system and database design. As a practical matter, however, larger organizations often compromise the REA model for financial statement reporting purposes. Although it is possible to extract traditional financial information from event data (as illustrated in Figure 10-13), doing so from millions of individual event records can be problematic. Instead, most companies implementing an REA model create an event database for operations, planning, and control purposes and maintain a traditional general ledger system separately in the background for financial reporting. Enterprise resource planning systems, which are discussed in the next chapter, exemplify the successful integration of event-based and traditional database designs within a single system.