The REA Approach to Database Modeling:Developing an REA Model

Developing an REA Model

In the previous chapter we described the process of view modeling, in which the database designer identifies and models the set of data that individual users need to make a decision or perform a task. The result of this process is an ER diagram depicting the user’s data model. The REA approach uses semantic modeling to construct an REA diagram, which in some ways is similar to an ER diagram, but in other respects is quite different. Before we describe REA view modeling, we need to examine these differences.

DIFFERENCES BETWEEN ER AND REA DIAGRAMS

ER and REA diagrams differ visually in a significant way. Entities in ER diagrams are of one class, and their proximity to other entities is determined by their cardinality and by what is visually pleasing to keep the diagrams readable. Entities in an REA diagram, however, are divided into three classes (resources, events, and agents) and organized into constellations by class on the diagram. This arrangement is illustrated in Figure 10-4. Note that during view integration (discussed later), when several individual REA diagrams are merged to form a single global model, the constellations of entities may necessarily be altered. As a design tool during the view modeling phase, however, the constellation convention is typically followed.

A second difference between ER and REA diagrams involves the sequencing of events. ER diagrams present a static picture of the underlying business phenomena. Relationships between data are shown through cardinality and associations, but the sequence of activities that determine the cardinality and associations is not clearly represented. REA diagrams, however, are typically organized from top to bottom within the constellations to focus on the sequence of events. An advantage of this is that during systems development, management and nontechnical users better understand REA diagrams.

The third difference between ER and REA diagrams pertains to naming conventions for entities. In ER diagrams, entity names are always represented in the singular noun form. REA modeling applies this rule when assigning names to resource and agent entities. Event entities, however, are given verb (action) names such as Sell Inventory, Take Order, or Receive Cash. The reader should, therefore, be careful to not confuse an event entity with a process. Event entities on an REA diagram represent and describe data- base tables that will store data about processes, but they are not representing or describing the processes themselves.

VIEW MODELING: CREATING AN INDIVIDUAL REA DIAGRAM

This section describes the view modeling process as applied to creating an REA diagram. The process involves the following steps:

1. Identify the event entities.

2. Identify the resource entities.

3. Identify the agent entities.

4. Determine associations and cardinalities between entities.

These procedures are performed for each organizational function being modeled. The result is several individual REA diagrams. The modeling process is completed during the view integrating phase (described later) where the individual models are consolidated into a single global model. To illustrate REA view modeling, we will use a simplified description of a revenue cycle process. Following are its key features:

Apex Supply Company is a downtown Philadelphia wholesaler of electrical products that sells to electri- cal retailers. It carries an inventory of approximately 10,000 items. Customers place orders by phone and buy on credit through a line-of-credit arrangement with Apex. A typical transaction involves the customer first contacting the customer services department to verify availability and check the price of the item or items being sought. If the customer decides to buy, he or she is transferred to a sales agent, who takes the order. The shipping clerk sends the products to the customer by a common carrier. The billing clerk records the sale in the sales journal, prepares an invoice, and sends it to the customer, who is given 30 days to make payment. The AR clerk also receives a copy of the invoice and records it in the AR ledger. Subsequently (within 30 days) the customer sends a check and the remittance advice to Apex. The cash receipts clerk receives the check, records it in the cash receipts journal, and updates the cash account. The remittance advice goes to the AR clerk, who updates (reduces) the customer’s account receivable.

Step 1. Identify the Event Entities

The first step in developing an REA model is to identify the event entities in the function being modeled. The events in this revenue cycle example can be identified as the value-added actions that Apex employ- ees take. These entities include Verify Availability, Take Order, Ship Product, and Receive Cash. An REA model must, at a minimum, include the two economic events that constitute the give and receive activities that reduce and increase economic resources in the exchange. In addition, it may include sup- port events, which do not change resources directly. We will next examine each identified event above to determine whether it should be classified an economic event or a support event.

VERIFY AVAILABILITY. The Verify Availability event is a support event because it does not directly increase or decrease a resource. The decision to add this entity to the model will depend on management’s need for information regarding customer inquiries. Such information could help them determine which in- ventory items customers most frequently demand. This may be different from what Apex is actually selling to customers. For example, an analysis of inquiries that do not lead to placed orders may indicate that customers are shopping around for, and getting, better deals from Apex competitors. We will assume, there- fore, that Verify Availability is a value-added activity that should be modeled in the REA diagram.

TAKE ORDER. Depending on the circumstances, Take Order could be either an economic or support event. Taking an order typically involves only a commitment on the part of the seller to sell goods to the customer. It may even involve adjusting (decreasing) the inventory available for sale to prevent it from being sold or promised to other customers. This commitment, however, does not cause a real decrease in inventory and is not an economic exchange. Furthermore, if the client subsequently cancels the order before it is shipped, no economic exchange will occur. On the other hand, if taking an order causes the buyer to expend resources to obtain or manufacture the product on behalf of the customer, then an economic event will have occurred. For the purposes of this example, we will assume that no economic con- sequences derive directly from the Take Order event and it is, therefore, a support event.

SHIP PRODUCT. Ship Product is an economic event. This is the give half of an economic exchange and reduces the inventory resource directly.

RECEIVE CASH. Similarly, the Receive Cash event is an economic event. This is the receive half of the exchange that increases the cash resource.

INVALID ENTITY TYPES. REA modeling focuses on value chain events. These are the activities that use cash to obtain resources including equipment, materials, and labor and then employ those resources to earn new revenues. Bookkeeping tasks such as recording a sale in the journal and setting up an account receivable are not value chain activities. These are invalid entity types and should not be included in an REA diagram.

A fundamental precept of REA is the rejection of accounting artifacts, including journals, ledgers, and double-entry bookkeeping. Capturing transaction data in sufficient detail adequately serves traditional accounting requirements. For example, an account receivable is the difference between a sale to a customer and cash received in payment of the sale. Therefore, analyzing data pertaining to the Ship Product (sales) and Receive Cash events can satisfy the information needs related to the accounts receivable and billing functions described in the Apex case previously presented.

Once the valid event entities are identified and classified as either economic or support events, they are placed on the REA diagram. REA convention is to arrange these entities in their sequence of occurrence from top to bottom on the diagram. Figure 10-5 presents the four events previously described in sequence of occurrence.

Step 2. Identify the Resource Entities

The next step in creating the REA diagram is to identify the resources that are impacted by the events selected to be modeled. Each economic event in an REA model must be linked to at least one resource

entity whose economic value will be either reduced or increased by the event. Support events are also related to resources but do not effect a change in the resource value.

One could make the theoretical argument that all employee actions, including support events such as Verify Availability or Take Order, consume a resource called Employee Service. In fact, this resource is increased as employees render their services to the organization and is simultaneously decreased as those services are employed in the performance of a task. In situations in which employee services are tracked to specific projects or products, this entity would provide meaningful data and should be included in the REA model. Because we can presume that this is not the case for Apex Supply, Employee Services will not be modeled.

In the Apex revenue cycle, economic events change only two resources. The Ship Product event reduces the inventory resource and the Receive Cash event increases the cash resource. The Verify Avail- ability and the Take Order support events are also associated with inventory, but they do not change it. The resource and associated event entities are presented in Figure 10-6.

Step 3. Identify the Agent Entities

Each economic event entity in an REA diagram is associated with at least two agent entities. One of these is an internal agent and the other is an external agent. The external agent associated with all four events in the Apex case is Customer. In addition, four internal agents are associated with the four events:

1. The customer services clerk, who participates in the Verify Availability event.

2. The sales representative, who participates in the Take Order event.

3. The shipping clerk, who participates in the Ship Product event.

4. The cash receipts clerk, who participates in the Receive Cash event.

Note that each of these internal agents is in fact an instance of the Employee entity type. For illustration purposes on the REA diagram, we identify each instance of Employee (for example, Sales Representative or Shipping Clerk) as a separate entity. The database that ultimately emerges from this model, however, will employ a single Employee table, and each instance shown in the model will be a row in that table. Figure 10-6 illustrates the relationship between the events and associated external and internal agents in the Apex case.

Step 4. Determine Associations and Cardinalities between Entities

We discussed in detail in Chapter 9 the topics of entity association and cardinality. This section assumes familiarity with these topics, which are only briefly reviewed.

Association is the nature of the relationship between two entities, as the labeled line connecting them represents. Cardinality (the degree of association between the entities) describes the number of possible occurrences in one entity that are associated with a single occurrence in a related entity. Four basic forms of cardinality are possible: zero or one (0,1), one and only one (1,1), zero or many (0,M), and one or many (1,M).

Figure 10-7 presents three alternative methods for presenting cardinalities in an REA diagram. Alter- native 1 presents the crow’s foot notation method discussed in Chapter 9. The example illustrates that a single occurrence (record) in Entity A is associated with zero or many occurrences in Entity B. Thus, the lowest possible cardinality is zero and the highest is many. Looking in the other direction, the notation states that a single occurrence in Entity B is associated with one and only one occurrence in Entity A. Sometimes lower and upper cardinality are explicitly written on the association line between the entities, as shown in Alternative 2 in the figure. A shorthand version of this is presented as Alternative 3, which shows only the upper cardinality and presumes the lower cardinality to be zero. The upper cardinalities for each entity define the overall association. For example, the entities in Figure 10-7 are said to have a 1:M association. Other possible associations are 1:1 and M:M.

Figure 10-8 presents a revised data model for Apex. Notice that it has been simplified for better read- ability by eliminating redundant instances of the Customer and Inventory entities that were depicted in Figure 10-6. In addition, Figure 10-8 shows the association and cardinality between entities using the crow’s foot notation method. Cardinality reflects the business rules that are in play for a particular organization. Sometimes the rules are obvious and are the same for all organizations. For example, the nor- mal cardinality between the Customer and Take Order entities are 1,1 and 0,M. This signifies that a particular customer may have placed zero or many orders during the sales period and that each order is for only one customer. The association between these entities is 1:M and would never be 1:1 as this would mean that the organization restricts each customer to an upper limit of only one order, which is illogical. The association between internal agents and event entities follows this same pattern. An orga- nization would expect its employees to participate in many events over time, not just one. Most of the cardinalities in Figure 10-8 reflect rules that are self-explanatory. A few points that need further explanation are presented next.

CARDINALITY BETWEEN THE VERIFY AVAILABILITY AND TAKE ORDER ENTITIES.

Each occurrence of the Verify Availability entity is the result of a customer inquiry. We know from the case description, however, that not all inquiries result in a customer order. On the other hand, we will make the simplifying assumption that each Take Order occurrence is the result of an inquiry. The cardinality on the Take Order side of the relation, therefore, is 0,1. On the Verify Availability side, it is 1,1.

CARDINALITY BETWEEN THE TAKE ORDER AND SHIP PRODUCT ENTITIES. The 0,1 cardinality on the Ship Product side of the relation reflects the timing difference between orders taken and shipped. Because sales are not processed instantly, we can assume that an order will exist (occurrence of Take Order) that has not yet been shipped (no occurrence of Ship Product). Furthermore, an order that is canceled before being shipped would also result in no Ship Product record being created.

CARDINALITY BETWEEN THE SHIP PRODUCT AND RECEIVE CASH ENTITIES. Business terms of trade and payment policies vary greatly. Companies that make credit sales to consumers often accept partial payments over time. This would result in many cash receipts occurrences for a single shipment occurrence. On the other hand, companies whose customers are other businesses typically expect payment in full when due. Business customers, however, may consolidate several invoices on a single cash payment to reduce check writing. Because the Apex company is a wholesaler that serves business customers, we will assume that debts are paid in full (no multiple partial payments) and that Apex customers may pay for multiple shipments with a single cash receipt. The cardinality in Figure 10-8 reflects this business rule.

CARDINALITY BETWEEN THE CASH AND RECEIVE CASH ENTITIES. The cash resource of an organization is composed of several different accounts, such as the general operating account, payroll imprest account, petty cash, and so on. These are consolidated for financial reporting into a single account, but are used and tracked separately. The cardinality depicted in this relationship implies that cash is received from many customers and is deposited into one account.

MANY-TO-MANY ASSOCIATIONS. The model in Figure 10-8 depicts three examples of M:M associations. The first of these is between the Verify Availability and Inventory entities. A 1,M cardinality exists at the Inventory end of the association and a 0,M cardinality lies at the Verify Availability end. This suggests that a particular customer query could involve one or many items of inventory, and each item may have been queried zero or many times in the period. The second M:M association exists between the Take Order and Inventory entities. A 1,M cardinality exists at the Inventory end of the association and a 0,M cardinality is at the Take Order end. This means that a particular order may con- tain one or many different items of inventory and that a particular item may have never been ordered (perhaps a new product) or may have been ordered many times during the period. A similar situation exists between the Ship Goods and Inventory entities. In each of these cases the upper cardinalities of M creates an M:M association, which we know from Chapter 9 must be reconciled. These situations are the result of repeating group data that need to be normalized before implementing the model in a relational database.2 The solution is to create three link tables that contain the primary keys of the associated tables. The link tables will also contain details pertaining to items queried, the orders taken, and the products shipped.

When modeling traditional ER diagrams, it is often convenient to include the link tables in the model (as in Chapter 9) so that it reflects closely the actual database. The inclusion of link tables in an REA dia- gram, however, creates a conflict with the rule that an event entity should be connected to at least one resource and at least two agent entities. Figure 10-9 shows how a portion of the Apex REA diagram would appear when link tables are inserted. Although link tables are a technical requirement for implementing an M:M association in a relational database, they are not a technical requirement for modeling the database. Including the link table in an REA diagram disrupts its visual integrity and adds little to one’s understanding of the conceptual model. Ultimately, during implementation, the database designer will create the link tables. Indeed, the database cannot function without them. For REA diagramming purposes, however, the link tables need only be implied via the M:M associations.