Data, databases and the REA model

Every accounting report is built from stored data, so the way data is organized decides what questions an organization can answer and how reliably. A ledger answers the question "what is the balance of accounts receivable?" A well-designed database also answers "which retailers in the north region took more than 45 days to pay for tents last winter, and which salesperson approved their credit limits?" This lesson introduces the vocabulary of data and databases from an accountant's point of view, explains why relational databases replaced flat files, and presents the resources-events-agents (REA) model, a way of designing accounting databases around business events rather than around debits and credits.

CMPUT-291 Introduction to Databases covers the relational model, SQL and normalization in depth for computing students. Here we stay at the level an accountant needs: enough to read a data model, understand why an ERP stores data the way it does, recognize data quality problems, and write the basic queries in lesson 4. If you have taken CMPUT-291, treat this lesson as a review with an accounting lens.

From files to databases

Data is organized in a hierarchy. A field (or attribute) holds one fact, such as a customer's credit limit. A record (or row) is a set of related fields about one thing, such as one customer. A file or table is a set of records of the same kind, such as all customers. A database is a set of related tables managed together. Early accounting systems kept each application's data in its own flat files. The billing program had a customer file; the shipping program had another; the credit department kept a spreadsheet. When a customer moved, someone had to update three files, and usually one was missed. The result was data redundancy (the same fact stored in several places), inconsistency (the copies disagree) and program-data dependence (changing a file layout meant rewriting every program that used it).

A database management system (DBMS) solves these problems by storing data once and letting many applications share it through a common interface. Users and programs ask for data by describing what they want, not where it is physically stored, which gives data independence. The DBMS enforces rules such as "every invoice must refer to an existing customer", controls who may read or change each table, logs changes and supports backup and recovery. Almost every modern accounting system and ERP sits on a relational DBMS such as Microsoft SQL Server, Oracle Database, SAP HANA, PostgreSQL or MySQL, often hidden behind a cloud service.

Relational database concepts for accountants

A relational database stores data in tables of rows and columns. Each table has a primary key, a field or combination of fields whose value uniquely identifies each row, such as a customer number. A foreign key is a field in one table that refers to the primary key of another, which is how tables are linked: each invoice row holds the customer number of the customer billed. Referential integrity is the rule that every foreign key value must match an existing primary key, so an invoice cannot point to a customer who does not exist. Entity integrity is the rule that a primary key can never be empty. These rules are controls built into the database itself, and they prevent whole classes of errors that plagued flat-file systems.

Well-designed relational databases are normalized: each fact is stored in exactly one place, and tables are split so that data about different things is not mixed. A table that repeats a customer's address on every invoice line is not normalized; if the address changes, old lines disagree with new ones. Normalization reduces anomalies when data is inserted, updated or deleted. ERP databases are heavily normalized, which is why a single invoice may draw on a dozen tables: header, lines, customer, product, tax codes, payment terms, currency and more. Reports and data warehouses sometimes deliberately denormalize data for speed, but the transaction database underneath should be normalized.

The REA model

Traditional accounting systems were designed around the chart of accounts and journal entries. William McCarthy proposed in 1982 that accounting databases should instead model the underlying economic reality: the resources an organization controls, the economic events that change those resources, and the agents who participate in those events. This resources-events-agents (REA) model has become the standard way of teaching AIS data design, and its ideas sit underneath many ERP data structures and the ISO/IEC 15944-4 standard for business transactions.

Resources are things of economic value the organization wants to plan, monitor and control: cash, inventory, equipment, employee time. Events are business activities that management wants to plan and control: sales, cash receipts, purchases, cash disbursements, payroll. Agents are the people and organizations who participate in events: customers, suppliers, employees. Each economic event is linked to the resource it affects and to two agents, one internal (the salesperson or clerk) and one external (the customer or supplier). The most distinctive REA idea is duality: every economic exchange has a give event and a take event. A sale gives up inventory; a cash receipt takes in cash. A purchase takes in inventory; a cash disbursement gives up cash. The duality relationship between a sale and its cash receipt replaces the accounts receivable balance: receivables are simply sales not yet matched by receipts, and the database can compute them at any moment.

REA diagrams are usually drawn with resources on the left, events in the middle and agents on the right. Relationships carry cardinalities that state business rules: one sale can be paid by many cash receipts (installments) and one cash receipt can pay many sales (a retailer paying several invoices at once), so the sale-to-receipt relationship is many-to-many and is implemented as a separate linking table. Getting cardinalities right matters. If a designer assumes one receipt per sale, the system cannot record partial payments, and clerks invent workarounds that break the data.

# A small REA-style revenue database for Ridgeback in SQLite (illustrative)
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE customer (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, credit_limit REAL);
CREATE TABLE sale (sale_id INTEGER PRIMARY KEY, sale_date TEXT, customer_id INTEGER NOT NULL
                   REFERENCES customer(customer_id), amount REAL NOT NULL);
CREATE TABLE cash_receipt (receipt_id INTEGER PRIMARY KEY, receipt_date TEXT, customer_id INTEGER
                   NOT NULL REFERENCES customer(customer_id), amount REAL NOT NULL);
CREATE TABLE sale_receipt (sale_id INTEGER REFERENCES sale(sale_id), receipt_id INTEGER
                   REFERENCES cash_receipt(receipt_id), applied REAL NOT NULL,
                   PRIMARY KEY (sale_id, receipt_id));
INSERT INTO customer VALUES (1043, 'Summit Trail Outfitters', 20000), (1077, 'Lakehead Paddle', 8000);
INSERT INTO sale VALUES (1, '2026-09-02', 1043, 6200), (2, '2026-09-15', 1043, 4100),
                        (3, '2026-09-20', 1077, 2900);
INSERT INTO cash_receipt VALUES (501, '2026-10-01', 1043, 8000);
INSERT INTO sale_receipt VALUES (1, 501, 6200), (2, 501, 1800);
""")
rows = db.execute("""
SELECT c.name, ROUND(SUM(s.amount) - COALESCE(SUM(sr.applied_total), 0), 2) AS receivable
FROM sale s JOIN customer c USING (customer_id)
LEFT JOIN (SELECT sale_id, SUM(applied) AS applied_total FROM sale_receipt GROUP BY sale_id) sr
  USING (sale_id)
GROUP BY c.name ORDER BY c.name""").fetchall()
for name, bal in rows: print(f"{name:26s} receivable {bal:>9,.2f}")
try:
    db.execute("INSERT INTO sale VALUES (4, '2026-10-02', 9999, 500)")
except sqlite3.IntegrityError as e:
    print("Rejected:", e)

Lakehead Paddle            receivable  2,900.00
Summit Trail Outfitters    receivable  2,300.00
Rejected: FOREIGN KEY constraint failed

Two controls appear in the output. First, the receivables balance is computed from sales and their linked receipts, so it cannot drift away from the transactions behind it; there is no separately keyed balance to fall out of agreement. Second, the attempt to record a sale to customer 9999 is rejected because of referential integrity. In a flat-file system that sale might have been recorded against a customer that did not exist, which is one way fictitious sales hide.

Data quality and master data

Data quality has several dimensions: accuracy (the value is correct), completeness (nothing required is missing), consistency (the same fact agrees everywhere), timeliness (it is current enough for its use), validity (it follows the defined format and rules) and uniqueness (each real-world thing appears once). Poor data quality is expensive. Duplicate supplier records lead to duplicate payments. Missing tax registration numbers cause tax errors. Customers with two account numbers have two credit limits. Accountants should treat master data, the relatively permanent records of customers, suppliers, products, employees and accounts, as an asset that needs an owner, rules and regular review.

Master data governance assigns each master data domain an owner, defines who may create and change records, requires approval for sensitive fields such as bank details and credit limits, and logs every change with the user, date and old and new values. Many organizations run a periodic clean-up: merging duplicate suppliers, blocking inactive ones, and checking that every active supplier has a valid tax number and verified bank details. These steps reduce both errors and fraud risk, as lessons 6 and 11 show.

Operational databases, data warehouses and data lakes

The database behind an ERP is an operational (transaction) database, optimized for recording many small transactions quickly and safely. Heavy reporting and analysis can slow it down, so many organizations copy data each night into a data warehouse, a separate database organized for queries across years of history and many sources. Data warehouses are often designed as star schemas: a central fact table of events, such as sales lines, surrounded by dimension tables for customers, products, dates and regions. A data lake stores raw data of many types, including files, logs and documents, before it is structured. For accountants the key point is lineage: every number in a dashboard should be traceable back through the warehouse to the transaction that produced it, and the copying process (often called extract, transform and load, or ETL) needs its own controls, such as record counts and control totals compared at each step. ACCT-341 works with warehouse data in depth.