ACCTING2503 · Accounting Systems And Analytics
Database Design & REA Data Modelling
This chapter is about designing a database before you build it: you draw a data model of the entities, relationships and rules the business cares about. You learn the general Entity-Relationship (E-R) diagram and the accounting-specialised REA model (Resources-Events-Agents) with its give-get economic duality, how to read cardinalities (minimum vs maximum), and how to turn the model into relational tables. It is a favourite of the closed-book final, where a case asks you to classify entities, justify cardinalities, build a small REA diagram, or convert a many-to-many relationship into tables.
What this chapter covers
- 011. Where data modelling sits — it happens early, during systems analysis and conceptual design, then the model is used to build the physical database
- 022. E-R diagrams — entities (rectangles/tables), attributes (columns) and relationships (associations)
- 033. Cardinalities — read (minimum, maximum) separately; minimum 0/1 = optional/mandatory, maximum 1/N
- 044. The relationship TYPE comes from the maximums only — 1:1, 1:N, M:N
- 055. The REA model — classify every entity as a Resource, Event or Agent
- 066. Give-get economic duality — every give (decrement) event is paired with a get (increment) event; the five cycles
- 077. The three REA rules — every event links to at least one resource, at least one other event, and at least two agents (one internal + one external)
- 088. Implementing REA in tables — one table per entity AND per M:N relationship (composite primary key); foreign keys for 1:1 and 1:N; merging cycles into an integrated diagram can change cardinalities
Build an REA model for a revenue cycle
- +1State the framework: an REA model has exactly three entity types, and every event must touch at least one resource, pair with at least one other event (give-get), and link to at least two agents. Cardinality states the minimum and maximum times one instance links to another.
- +2Resources — Inventory (the timber) and Cash. These are the two things of economic value the firm acquires and uses.
- +2Events — Sale (which gives inventory) and Cash Receipt (which gets cash). An optional Take Order commitment event may come first, but the two economic events are Sale and Cash Receipt.
- +2Agents — internal = Salesperson (and a Cashier for the receipt); external = Customer. Each event therefore has its required two agents, one internal and one external.
- +1Give-get duality — Torrens gives inventory via the Sale to get cash via the Cash Receipt. That give-get pairing is the relationship that links the two events and makes the model balance.
- +2Cardinalities — one transfer settles several invoices and one invoice can be paid in instalments, so a Sale links to many Cash Receipts and a Cash Receipt links to many Sales: Sale-Cash Receipt is many-to-many (M:N). Had the firm insisted one payment settles exactly one invoice in full, it would be 1:1. The minimum on the receipt side is typically 0, because a sale can exist before any cash is received.
Key terms
- Data modelling
- Defining an information system so it faithfully represents the organisation's requirements. It happens early — during systems analysis and conceptual design — and the finished model is then used to build the physical database.
- E-R (Entity-Relationship) diagram
- A general-purpose graphical model of the entities an organisation stores data about and the relationships between them. Entities become tables, attributes become columns, and relationships are refined by cardinalities.
- Cardinality (minimum / maximum)
- How many instances of one entity can link to one instance of another, written (minimum, maximum). Minimum 0 or 1 says optional vs mandatory participation; maximum 1 or N says at most how many. The relationship TYPE (1:1, 1:N, M:N) is fixed by the maximums only.
- REA model
- A data model purpose-built for accounting that constrains an E-R diagram to three entity types — Resources, Events and Agents — so it captures the economic things, the activities that change them, and the people involved.
- Resource / Event / Agent
- Resource = something with economic value (inventory, cash). Event = a business activity management wants to control (sale, receive cash). Agent = a person or organisation who participates — one internal (employee) and one external (customer, supplier) per event.
- Give-get economic duality
- REA's backbone: every give event (an outflow that decrements a resource) is paired with a get event (an inflow that increments a resource). For example, a Sale gives inventory to get cash via the Cash Receipt.
- M:N junction table
- A many-to-many relationship is implemented as its own separate table whose primary key is the combination (composite key) of the two entities' primary keys, plus any relationship attribute. Foreign keys — not extra tables — implement 1:1 and 1:N relationships.
- Integrated REA diagram
- The combined model of several cycles, formed by merging redundant resources and events (one Inventory table, one shared Disburse Cash event). Merging events can change cardinalities, so they must be re-checked after a merge.
Database Design & REA Data Modelling FAQ
How is the relationship type (1:1, 1:N, M:N) decided?
By the two maximum cardinalities only. If both maximums are 1 it is one-to-one; if one side is 1 and the other N it is one-to-many; if both are N it is many-to-many. The minimums (0 or 1) tell you whether participation is optional or mandatory — they never change the type.
What is the difference between an E-R diagram and an REA model?
An E-R diagram is the general notation for any database. An REA model is a constrained E-R diagram built for accounting, where every entity must be a Resource, Event or Agent. If a question says 'AIS' or 'accounting cycle', use REA; if it says a generic 'database', a plain E-R diagram is fine.
What are the three REA rules?
Every event links to at least one resource it affects; every event links to at least one other event (the give-get duality); and every event links to at least two agents — one internal and one external. Showing only one agent per event is a classic lost mark.
How do I convert a many-to-many relationship into tables?
Give it its own junction table with a composite primary key made of both entities' keys, plus any relationship attribute (such as quantity). You cannot implement M:N with a foreign key. By contrast, a 1:N link (one customer, many sales) is implemented by placing the customer's key into the Sale table as a foreign key — no extra table.
How do I classify entities without getting them wrong?
Write R, E or A against every entity before drawing anything. A sale and a cash receipt are events; cash and inventory are resources (not events); customers and employees are agents. Calling 'cash' an event or a 'sale' a resource unravels the whole diagram.
Why can merging cycles change the cardinalities?
When you merge a repeated event — such as Disburse Cash across the expenditure and payroll cycles — a single payment now goes to a supplier OR an employee, never both, so its link to each payee becomes optional (0,1) where it was mandatory before. Always re-read the cardinalities after a merge.
Exam move
Treat this chapter as a rules-and-classify topic, not a memorisation one. First, drill the three REA rules until you can recite them (every event: at least one resource, at least one other event, at least two agents) and always write R/E/A against each entity before you draw. Second, master cardinalities by reading minimum and maximum as two separate answers and taking the relationship type from the maximums only — this is the single biggest source of REA errors. Third, practise the give-get duality across all five cycles (revenue, expenditure, payroll, production, financing) so you can pair any outflow event with its inflow. Finally, be fluent in the REA-to-tables recipe: one table per entity AND per M:N relationship, a composite primary key for the junction, and foreign keys for 1:1 and 1:N. In the final, structure every answer as state the rules, apply them to the scenario, justify the cardinalities from the business rule, then conclude with the diagram or table set.
