ACCT 332 Week 3 Discussion Board Forum 2 Example

Discussion Board Forum 2: Data Modeling and Relational Database Design for a Business Process

ACCT 332 Accounting and Control, Discussion Board Forum 2

Thread by [Student Name]

The supply yard and the three classmates are composites; the textbook, Scripture and the peer-reviewed sources cited are real.

A composite stone and mulch yard sells mulch, stone and pavers to about 300 contractors on 30-day terms. Today it writes sales tickets by hand and tracks who owes what in a spreadsheet, so the owner cannot easily see which contractors buy which products or how long each takes to pay. Designing a database for the sales and collection process would fix that.

Richardson et al. (2018) present the REA approach, which models a business process in terms of the economic events that change resources and the agents who take part. McCarthy (1982) offered REA as a general design for accounting databases that many users share, storing the business events themselves instead of debits and credits. For the yard, the events are Sale and Cash Receipt, the resources are Inventory and Cash, and the agents are Customer, Salesperson and Cashier. A sale decreases inventory, a cash receipt increases cash and each receipt pays for one or more sales.

The cardinalities matter. A contractor who pays in installments makes many receipts for one sale, and a single check may cover many sales, so Sale and Cash Receipt have a many-to-many relationship. Sale and Inventory are also many-to-many, since a sale includes several products and a product appears on many sales. The design therefore needs five base tables, Customer, Salesperson, Inventory, Sale and Cash Receipt, plus two linking tables: Sale-Line (sale number and item number, with quantity and price) and Sale-Receipt (sale number and receipt number, with the amount applied). Every many-to-many relationship in the model becomes a table in the database; skipping one is how double counting starts. The Sale table carries the customer and salesperson numbers as foreign keys.

Dunn and McCarthy (1997) review the intellectual heritage of REA and argue that its event-based design lets one database serve accounting and non-accounting users alike, which is the point for the yard: the same tables produce receivables aging for the bookkeeper and product sales by contractor for the owner.

Paul closed his instructions to the Corinthian church by asking that everything be done properly and in an orderly way (English Standard Version Bible, 2001/2016, 1 Corinthians 14:40). A well-designed database is order applied to records, and it serves everyone who depends on them.

Reply to Abigail

Abigail, your design for the bookstore stored the customer's address on every sale record. Richardson et al. (2018) explain that normalized tables store each fact once, so the address belongs in the Customer table, linked to Sale by the customer number. Otherwise, when a customer moves, some sales will show the new address and some the old one, and reports will disagree. Moving the address also makes the Sale table smaller and easier to query. If the bookstore later needs a shipping address that differs from the billing address, that can become its own field on the Sale table, because it describes the sale rather than the customer, and the distinction keeps both facts accurate.

Reply to Devon

Devon, you wondered whether REA matters if most companies buy an ERP package rather than designing their own database. I think it does, because the logic is underneath the packages. McCarthy (1982) argued that storing economic events instead of journal entries lets a single database work for everyone who needs the data, and that is essentially what an ERP does when a sales order updates inventory, receivables and the ledger at once. Knowing REA helps an accountant read an ERP's data model and judge where information comes from. It also helps during implementation, when consultants ask the accounting team to map old reports to new tables; an accountant who thinks in events, resources and agents can see quickly which report draws on which event and catch gaps before go-live.

Reply to Sarah

Sarah, your point about primary keys being unique made me think about controls. If anyone can edit the Cash Receipt table, a receipt could be reassigned from one customer to another to hide a theft, the classic lapping scheme. Richardson et al. (2018) stress that access controls and segregation of duties apply inside the database too, so the cashier who records receipts should not also be able to change sale or customer records. The key design and the control design really work together. A further safeguard would be a change log on the Sale-Receipt linking table, recording who applied each payment and when, so that any reassignment of a receipt leaves a trail an auditor can follow back to the person who made it.

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. https://doi.org/10.2308/jis-697364

English Standard Version Bible. (2016). Crossway. (Original work published 2001)

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. https://doi.org/10.2308/tar-4487748

Richardson, V. J., Chang, C. J., & Smith, R. (2018). Accounting information systems (2nd ed.). McGraw-Hill Education.