The REA pattern: resources, events and agents
Most business systems record transactions with one pattern, called REA. Because it repeats, you rarely design a schema from nothing.
After you enlist, the payment page of the university's online enlistment system lists your enlisted courses and the amount due for each. You pay, and the page shows a payment reference number. The slots you hold and the money you paid are two separate records.
Each tab shows one business drawn with the same six boxes: a resource, an event and the agents on each side. The dashed line between the sides is duality. Open each tab and compare.
More detail
The three parts of REA
Resource: the thing of value, such as a slot, a book, an item of stock or cash. The event moves it.
Event: a record of something that happened at a moment and changed who holds the resource. Events are usually the tables with the most rows.
Agent: a person or organization on one side of an event. Usually one is inside your organization and one is outside it, and you need to record both.
Where the foreign keys sit
One resource appears in many events, and one agent takes part in many events. So the event table holds both foreign keys, following the rule from Relationships that the foreign key goes on the many side.
Why there are two events
A slot is given and money is taken. A book goes out and comes back. If you model only the half you were asked about, the system cannot answer where the money went.
The hardest business questions are gaps between two events that should have paired: unpaid enlistments, overdue books, orders delivered but not billed. Payments and gateways shows the same problem on the network side.
The dashed line joins two events that pair up, such as an enlistment and its payment. One payment can cover several enlistments, and one enlistment can be paid in installments. So the pairing is many-to-many and needs a table of its own. That table records how much of each payment applies to each enlistment.
Read the allocations table row by row.
| allocations | enlistment | payment | amount |
|---|---|---|---|
| one payment, three enlistments | MGT 45 | OR-8841 | ₱7,500 |
| CSCI 21 | OR-8841 | ₱7,500 | |
| one enlistment, two payments | ITMGT 25 | OR-8841 | ₱3,000 |
| ITMGT 25 | OR-9102 | ₱4,500 |
More detail
The table has four rows, three enlistments and two payments. Neither side divides evenly into the other, so the amount column cannot sit in the enlistment table or in the payment table. Relationships explains why a many-to-many link needs a table of its own.
Events without a matching event
Some events legitimately have no partner: a scholarship waiver, a damaged shipment, a library book written off after a flood. A resource decreases and nothing comes back. Reconciliation is the check that matches each event with its partner. If it reports every unpaired event as an exception, it produces false alarms, and people stop reading it.
Decide once which events are expected to pair, and record that decision in the model. Only a missing pair that should exist needs investigating.
Because the same shape repeats, the industry publishes ready-made templates for common domains, called universal data models. Django's user and login tables are one example. A template shows which entities other people found necessary. The one you would not have thought of is usually the event in the middle.
You can now describe a sale, a payment or an enlistment with the REA pattern.
More detail
Other examples are a Supabase starter schema and a ready-made set of tables for an online store. Each is somebody else's solution to a model you were about to draw yourself. Use them, and read them.
Where to put the boxes (optional)
A diagram of tables can be laid out in many ways, and most people place the boxes wherever there is room. One convention costs nothing: draw every crow's foot pointing up and to the left. A crow's foot is the three-pronged symbol on the many side of a relationship. The many side is almost always the entity that records what happens, so the tables that grow fastest end up in the top left, and every foreign key points down and to the right.
On a narrow screen, scroll this diagram sideways to see all of it.