ACC 315 Module 5 Database Design Assignment Example

Modeling Deliveries and Payments: An REA Database Design for a Composite Propane Dealer's Revenue Cycle

[Student Name]

Southern New Hampshire University

ACC 315: Accounting Information Systems

Module Five Assignment

[Instructor Name]

[Date]

The organization, setting and figures below are a composite written as a model document. No real employer, client, colleague or patient is described.

Modeling Deliveries and Payments: An REA Database Design for a Composite Propane Dealer's Revenue Cycle

Introduction

At the Vermont dealer used throughout these samples, delivery data lives in route software and billing data in a desktop accounting package, so no report can connect a tank, a delivery, an invoice and a payment. Project One set the requirement that one database serve both dispatch and billing. This assignment designs that database for the revenue cycle using the resources, events and agents model introduced by McCarthy (1982), which records economic events directly rather than the debits and credits that summarize them. The design covers propane deliveries and customer payments; purchasing and payroll would be added later using the same approach.

Resources, Events and Agents

In the REA model, resources are things of economic value the business controls, events change those resources, and agents are the people and organizations who participate in events (Romney et al., 2021).

Table 1. REA Elements in the Revenue Cycle

A tank is not an agent or event but an important entity in this business: one customer may have several tanks, such as a house tank and a barn tank, and automatic deliveries are scheduled by tank. The design therefore adds a tank table linked to the customer.

Relationships and Cardinalities

Dunn and McCarthy (1997) emphasize that the REA pattern is valuable because the same relationships recur across businesses, so a designer can reason from the pattern rather than from scratch. Table 2 states each relationship in plain words and gives its cardinality.

Table 2. Relationships and Cardinalities

The duality relationship is the one that matters most for this business. Budget plans are common here, covering roughly 1,900 accounts, so one payment often applies to several deliveries, and one delivery is often paid over several months. That makes the relationship many-to-many.

Relational Tables

Each entity becomes a table, each many-to-one relationship is carried by a foreign key on the many side, and the many-to-many duality relationship becomes a linking table.

Table 3. Tables, Keys and Main Attributes

Three design choices deserve explanation. First, the delivery table stores meter start and end readings, not only gallons, so that each delivery can be checked against the truck meter, which addresses the gallons reconciliation requirement from Project One. Second, the unit price is stored on each delivery because prices change during the season; the price in effect on the day of delivery must be preserved. Third, no customer balance is stored. The balance is calculated as the sum of deliveries billed less the sum of amounts applied in the linking table, so it cannot drift from the events behind it.

Queries the Design Supports

Three questions the owner cannot answer today become simple queries. Gallons by town and month joins Delivery, Tank and Customer and groups by the tank's site town and the delivery month, giving the owner the usage reports he now waits a day for. Unpaid deliveries by age compares each delivery's amount with the amounts applied to it in DeliveryReceipt and groups the unpaid remainder by days since delivery, an aging report built from events rather than balances. Gallons pumped versus billed by truck sums meter end minus meter start for each truck and day and compares the total with the truck's morning fill, the daily reconciliation recommended in Module Three.

Controls Built Into the Design

Referential integrity rules prevent a delivery from being recorded for a tank that does not exist, or a payment for an unknown customer. The clerk's ID on each receipt creates an audit trail. Because the price comes from a price plan table rather than typed entry, a clerk cannot bill a residential customer at a commercial rate without changing the customer's plan, an action that can be restricted to the controller.

Conclusion

An REA design for this dealer's revenue cycle uses seven entities and one linking table to replace two disconnected systems. It records what happened, a delivery to a tank by a driver and the cash that paid for it, and derives balances and reports from those events. It meets four of the core requirements from Project One and gives the dealer the reconciliation data it has never had. Purchasing, with its terminal loads and bills of lading, would be modeled next in the same pattern.

References

Dunn, C. L., & McCarthy, W. E. (1997). The REA accounting model: Intellectual heritage and prospects for progress. Journal of Information Systems, 11(1), 31-51.

McCarthy, W. E. (1982). The REA accounting model: A generalized framework for accounting systems in a shared data environment. The Accounting Review, 57(3), 554-578.

Romney, M. B., Steinbart, P. J., Summers, S. L., & Wood, D. A. (2021). Accounting information systems (15th ed.). Pearson.