alexa Reducing Complexity in an Accounting System by Using the REA Data Model to Design a Relational Database | Open Access Journals
ISSN: 2168-9601
Journal of Accounting & Marketing
Make the best use of Scientific Research and information from our 700+ peer reviewed, Open Access Journals that operates with the help of 50,000+ Editorial Board Members and esteemed reviewers and 1000+ Scientific associations in Medical, Clinical, Pharmaceutical, Engineering, Technology and Management Fields.
Meet Inspiring Speakers and Experts at our 3000+ Global Conferenceseries Events with over 600+ Conferences, 1200+ Symposiums and 1200+ Workshops on
Medical, Pharma, Engineering, Science, Technology and Business

Reducing Complexity in an Accounting System by Using the REA Data Model to Design a Relational Database

Ian Adamson*

Department of Accounting, Brock University, St. Catharine’s, Ontario, Canada

*Corresponding Author:
Ian Adamson
Associate Professor
Department of Accounting, Brock University
St. Catharine’s, Ontario, Canada
Tel: (905) 688- 5550
Fax: (905) 688-9779
E-mail: iadamson@brocku.ca

Received Date: May 12, 2017; Accepted Date: May 19, 2017; Published Date: May 26, 2017

Citation: Adamson I (2017) Reducing Complexity in an Accounting System by Using the REA Data Model to Design a Relational Database. J Account Mark 6: 237. doi: 10.4172/2168-9601.1000237

Copyright: © 2017 Adamson I. This is an open-access article distributed under the terms of the Creative Commons Attribution License, which permits unrestricted use, distribution, and reproduction in any medium, provided the original author and source are credited.

Visit for more related articles at Journal of Accounting & Marketing

Relational databases are used extensively in today’s business environment, from simple small enterprises using Microsoft Access to large corporations using Oracle or Microsoft SQL Server. A database must be efficiently designed in order to avoid errors and data redundancies. Poorly designed databases can be very complex, difficult to maintain and prone to anomalies. In order to avoid this situation, an effective design methodology should be used.

Designing a relational database involves developing a detailed data model that can then be used to create the database. Historically software engineers have used the Entity Relationship Model (ERM), a graphical tool that represents the logical relationships between entities. Entities are typically shown as rectangles with the connections between the entities portraying the relationships. In a logical sense, entities are the equivalent of grammatical nouns, such as employees, inventory, payables, or fixed assets, with the characteristics of the entity defined by means of its properties called attributes. Relationships are the equivalent of verbs or associations, such as the act of purchasing or selling, or an employee being a member of a department.

On implementation of the database, the entities become relations or tables while the relationships reveal the logical connection between the tables. Such connections can be one-to-one, one-to-many, or many-to-many. For example the figure below show a one-to-many relationship between Customer and Sale entities (Figure 1).

accounting-marketing-entity

Figure 1: Customer entity.

This shows that a Customer entity can have many sales but a sale belongs only to one customer. The diamond shape shows the relationship has been identified as R which could also be the verb ‘have’. For a number of relationships in an ERM they can be shown as R1 to Rn. On implementation in a relational database both Customer and Sale become tables.

How are the relationships established? Both Customer and Sale have to have a unique identifier in order to identify each instance of the entity class. Such identifiers are called primary keys and usually are numeric. The primary key for Customer would be Customer Number and for Sale, the Invoice Number. The one-to-may relational connection would be implemented with the Sale entity having Customer Number as the foreign key. This example can be shown by the following (Figure 2). It can be seen that Customer one has two invoices while customer two has only one invoice. Primary Keys are shown as PK while the Foreign Key is shown as FK.

accounting-marketing-system

Figure 2: ERM of a system.

In order to construct and ERM of a system the entities or nouns are first identified and then the relationship constructed. Consider simple sales and corresponding cash receipts transactions. Possible entities, but not all, are shown in the following (Table 1).

Customer  
Employee Sales Clerk
Sales Order  
Sales Invoice  
Inventory  
Accounts Receivable  
Employee
Clerk
Accounts Receivable
Cash  
Cash Receipts  
Employee Cash Receipts Clerk
Cash Receipts Listing  
General Ledger  
Employee Accounting Clerk

Table 1: Possible entities.

Possible relationships are shown in the following ERM as identified by R1 to R18. Each of the relationships would have to be implemented by primary keys and foreign keys. It can also be seen that each of the clerical personnel are Employees, requiring only on entity class of Employee of which each member is an instance (Figure 3).

accounting-marketing-relationships

Figure 3: Possible relationships.

All together there are ten entity classes and eighteen relationships for this simple scenario. In a full accounting database system the number of tables and relationships would be very large resulting in a degree of complexity that would be significant. This complexity could result in multiple data redundancies and duplication of tables. By looking at the Resource Event Agent Data Model it is possible to significantly reduce such complexity.

The Resources, Events, Agents (REA) Model

Hruby shows that REA is a business process modeling methodologythat iscloser to business reality than any other known alternative. An increasing number of business analysts have found that the models they develop become better when they have REA in mind. REA systems are usually modeled as relational databases, though this is not necessarily a requirement, typically using entity-relationship diagrams.

The REA model derives its name from the three distinct categories of entities resources, events, and agents [1,2].

• Resources are acquired and used by the company and have economic value to the organization.

• Events are typically business activities in which the company engages and about which management wishes to gather data for planning or control purposes; and

• Agents include people and organizations who participate in events. Management also needs gather data on these entities for planning, control, and evaluation purposes

As a rule, central to an REA model is as a pair of events associated with an exchange relationship. The structure of this relationship is based upon the duality principle. The REA duality principle: An event generating an outflow of resources must be paired in duality relationship with an event generating an inflow of resources, and vice-versa. These corresponding events are also called give and take events. Therefore an exchange transaction is basically a pair of dual events that links the act of giving, the decrement event, with the act of taking, the increment event. If there is no duality then the transaction is meaningless, with the exception of a transaction such as a charitable donation where there would be an inflow of a resource such as cash, but no outflow, thus no duality.

One of these events usually corresponds to a resource being given away, while the other corresponds to a resource being received. For example, an event resulting in the receipt or inflow of inventory must be related to an event of equal monetary value loss or outflow. In the terms of a sales transaction, one event would be the sale transaction where goods are given up and the other would be cash receipt, where cash is received or gained.

The Sales Transaction using the REA Data Model

In sales/cash receipts transactions the resources are typically the inventory item that is being sold and the cash received from the customer. There are two events, the sale and the cash receipt, that have a duality relationship. The agents in this scenario are the Sales Clerk, the Customer, and the Accounting Clerk (Table 2). The REA diagram can be seen in the following (Figure 4).

Resource Inventory
Resource Cash
Event Sales
Event Cash Receipt
Agent Customer
Agent Sales Clerk
Agent Cash Receipts Clerk

Table 2: The Sales Transaction using the REA Data Model.

accounting-marketing-diagram

Figure 4: REA diagram.

Notice that there are no journals or ledgers. Such entities are an artifact of the traditional accounting system and cannot be classified as resources, events, or agents. The REA Model eliminates accounting structures that are not necessary in the electronic era. These entities disappear since they can be generated in real time using record detail in an REA system. Consider an example of producing an Aged Accounts Receivable Listing using the following (Figure 5).

accounting-marketing-listing

Figure 5: Aged Accounts Receivable Listing.

From this data it is conceivable to have an application program, written in a language such as PLSQL, C++ or even COBOL, to construct the listing. Simple partial pseudo code for a program is shown below (Figure 6).

accounting-marketing-pseudo

Figure 6: Simple partial pseudo code.

Any reports or listings can be generated in a similar manner whenever they are needed, not just at month or year end.

REA Model Benefits

The REA Model uses an events-based approach looking at all the possible events that can occur. These events can then be processed to give the user their required output, for example, an Accounts Receivable Aged Trial Balance, which has not been pre-defined. The report is only produced when the user accesses the specific events defined for that report [3].

An Entity-Relationship diagramming approach, a more traditional methodology, is a view-based approach requiring that all potential entities are included in the relational model, which would include the Accounts Receivable Aged Trial Balance and other similar reports. This can result in an incomprehensible diagram for complex real-world relational database applications.

In examining the REA Model of Sales/Cash Receipts it is evident that there fewer entities and relationships (Table 3).

Traditional Model REA Model
10 Entities 6 Entities
18 Relationships 7 Relationships

Table 3: An Entity-Relationship diagramming approach.

The result would be reduced data redundancy, a condition created within a database where the same data item is held in two separate tables. The traditional model has four tables, Sales Order, Accounts Receivable, Cash Receipts Listing, and the General Ledger which contain data that are already present in other tables such as sales invoice and Cash Receipts. This is an increase in data redundancy. Any changes to Sales Invoice and Cash Receipts requires corresponding changes to theses four tables. This can result in insert, update, and deletion anomalies and data inconsistencies within the database, a situation prone to error.

In the traditional model adjustments usually have to made to subledgers in order to balance to the general ledger, or vice-versa. In the REA Data Model this is not required. All transactions are entered events and the sub-journals and general ledger are created from these events. Thus the sub-ledgers will balance to the general ledger and not adjusting entries for balancing would be required.

Thus the REA Model results in a more simplified relational data base which significantly reduces the chance of data duplication, data redundancy, and as a result, a data base that requires less maintenance and is less prone to error.

References

Select your language of interest to view the total content in your interested language
Post your comment

Share This Article

Relevant Topics

Article Usage

  • Total views: 132
  • [From(publication date):
    June-2017 - Jul 26, 2017]
  • Breakdown by view type
  • HTML page views : 108
  • PDF downloads :24
 
 

Post your comment

captcha   Reload  Can't read the image? click here to refresh

Peer Reviewed Journals
 
Make the best use of Scientific Research and information from our 700 + peer reviewed, Open Access Journals
International Conferences 2017-18
 
Meet Inspiring Speakers and Experts at our 3000+ Global Annual Meetings

Contact Us

Agri, Food, Aqua and Veterinary Science Journals

Dr. Krish

agrifoodaquavet@omicsonline.com

1-702-714-7001 Extn: 9040

Clinical and Biochemistry Journals

Datta A

clinical_biochem@omicsonline.com

1-702-714-7001Extn: 9037

Business & Management Journals

Ronald

business@omicsonline.com

1-702-714-7001Extn: 9042

Chemical Engineering and Chemistry Journals

Gabriel Shaw

chemicaleng_chemistry@omicsonline.com

1-702-714-7001 Extn: 9040

Earth & Environmental Sciences

Katie Wilson

environmentalsci@omicsonline.com

1-702-714-7001Extn: 9042

Engineering Journals

James Franklin

engineering@omicsonline.com

1-702-714-7001Extn: 9042

General Science and Health care Journals

Andrea Jason

generalsci_healthcare@omicsonline.com

1-702-714-7001Extn: 9043

Genetics and Molecular Biology Journals

Anna Melissa

genetics_molbio@omicsonline.com

1-702-714-7001 Extn: 9006

Immunology & Microbiology Journals

David Gorantl

immuno_microbio@omicsonline.com

1-702-714-7001Extn: 9014

Informatics Journals

Stephanie Skinner

omics@omicsonline.com

1-702-714-7001Extn: 9039

Material Sciences Journals

Rachle Green

materialsci@omicsonline.com

1-702-714-7001Extn: 9039

Mathematics and Physics Journals

Jim Willison

mathematics_physics@omicsonline.com

1-702-714-7001 Extn: 9042

Medical Journals

Nimmi Anna

medical@omicsonline.com

1-702-714-7001 Extn: 9038

Neuroscience & Psychology Journals

Nathan T

neuro_psychology@omicsonline.com

1-702-714-7001Extn: 9041

Pharmaceutical Sciences Journals

John Behannon

pharma@omicsonline.com

1-702-714-7001Extn: 9007

Social & Political Science Journals

Steve Harry

social_politicalsci@omicsonline.com

1-702-714-7001 Extn: 9042

 
© 2008-2017 OMICS International - Open Access Publisher. Best viewed in Mozilla Firefox | Google Chrome | Above IE 7.0 version