Chapter 8: Master Data and Transaction Data — Adapting Your Design to the Nature of the Data
What you'll learn in this chapter
- Explain, in your own words, the difference between master data and transaction data
- Make appropriate design decisions (history management / partitioning / deletion strategy) based on each data type's nature
- Identify which category a table falls into in an e-commerce site or business system
You should understand Chapter 7: Basic Terminology and Naming Conventions.
Data Has Two Natures
Data stored in a database broadly splits into two kinds based on its nature.
This distinction affects every corner of design, not just table design — operations, backups, performance tuning, and archiving strategy too.
What Is Master Data? — The System's "Dictionary"
Master data is the reference information a system needs to run. Tables commonly called "masters" — a product master, customer master, department master, and so on — fall into this category.
Product Master Example
CREATE TABLE products (
id SERIAL PRIMARY KEY,
code VARCHAR(20) UNIQUE NOT NULL, -- the business-facing product code
name VARCHAR(200) NOT NULL,
category_id INTEGER NOT NULL REFERENCES categories(id),
unit_price NUMERIC(10, 2) NOT NULL,
tax_rate NUMERIC(4, 2) NOT NULL DEFAULT 10.0,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO products (code, name, category_id, unit_price) VALUES
('PROD-001', 'Laptop', 1, 89800),
('PROD-002', 'Mouse', 2, 2980),
('PROD-003', 'Keyboard', 2, 4980);
The product master is reference information showing "which product has which price set," and day-to-day order processing refers back to it.
What Is Transaction Data? — Day-to-Day "History"
Transaction data is data that's generated day by day through business activity. Orders, sales, receiving and shipping, access logs, and the like fall into this category.
Order Data Example
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
order_number VARCHAR(20) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL REFERENCES customers(id),
order_date TIMESTAMPTZ NOT NULL,
total_amount NUMERIC(12, 2) NOT NULL,
tax_amount NUMERIC(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL REFERENCES orders(id),
line_number INTEGER NOT NULL,
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL,
unit_price NUMERIC(10, 2) NOT NULL, -- a snapshot of the price at order time
amount NUMERIC(12, 2) NOT NULL,
PRIMARY KEY (order_id, line_number)
);
unit_price?It might seem like you could just look up unit_price from the product master, but the golden rule is to store the price at order time as a "snapshot" on the order line item. The reason is that the product master's price can change in the future, and you need to be able to correctly see "what the price was at the time" when you look back at a past order later.
The Relationship Between Master and Transaction Data
Transaction data always takes the form of referencing master data.
- Orders (
orders) reference the customer master (customers) - Order line items (
order_items) reference the product master (products) - Products (
products) reference the category master (categories)
You can think of a transaction as a factual record of "when, who, what, and how much," while a master is the reference information for "who" or "what."
Master vs. Transaction — A Comparison of Characteristics
| Aspect | Master data | Transaction data |
|---|---|---|
| Update frequency | Low (a few times a month to a year) | High (occurs by the second) |
| Data volume | Small (thousands to tens of thousands of rows) | Large (millions to hundreds of millions of rows) |
| Data lifespan | Long (years, sometimes semi-permanent) | Short to medium (archived after the legally required retention period) |
| Business importance | Extremely high (breaking it halts all business) | High (the accuracy of the history translates directly to monetary value) |
| Backup strategy | Keep everything, including history | Archive by time period |
A Concrete Example on an E-Commerce Site
Design Considerations
Master Data: When History Management Is Needed
There's a need like "when a product's price changes, I want to know 'the price at the time' in order to recalculate past orders retroactively." As covered above, the basic approach is to hold the price as a snapshot on the order line item, but if you want to see the product's price-change history itself, one option is to also set up a history table on the master side.
CREATE TABLE product_price_history (
id BIGSERIAL PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(id),
unit_price NUMERIC(10, 2) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE, -- NULL = still in effect
changed_by INTEGER NOT NULL REFERENCES users(id),
changed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_pph_product ON product_price_history(product_id);
CREATE INDEX idx_pph_valid_period ON product_price_history(valid_from, valid_to);
This is a design that uses valid_from / valid_to to express "from when to when this price was in effect." It's also called a "history table," "period model," or "temporal model."
Transaction Data: Coping With Ever-Growing Data
Transaction data keeps growing, so a table can balloon to hundreds of millions of rows within a few years if you don't do anything about it. Countermeasures include:
| Countermeasure | Description |
|---|---|
| Archiving | Move old data to a separate table / storage to keep the production table lean |
| Partitioning | Split a single table into logical partitions by day, month, or year, to speed up searches |
| Aggregation tables | Precompute daily/monthly aggregate results into a separate table, to reduce the load when generating reports |
| Appropriate indexes | Create indexes on columns used in searches; drop indexes that aren't used |
CREATE TABLE orders (
id BIGSERIAL,
order_date DATE NOT NULL,
customer_id INTEGER NOT NULL,
total_amount NUMERIC(12, 2) NOT NULL,
PRIMARY KEY (order_date, id)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_2026 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
Partitioning is something to consider "once a table has ballooned to hundreds of millions of rows or more"; introducing it from the start only adds complexity. The situations where a junior engineer should use it on a new project are limited. It's fine to take the stance of "add it once it becomes a problem."
The Lifecycle of Data
For master data, it's safer to "deactivate and keep it around once it's no longer needed" (is_active = false). The reason is that past transactions may still reference that master record, and physically deleting it would violate a foreign-key constraint.
Exercises
Which category does each of the following tables fall into — master data or transaction data?
- An employee table (
employees) - A clock-in/clock-out table (
attendance_logs) - A department table (
departments) - A payroll results table (
payroll_results) - A salary grades table (
salary_grades) - A login log table (
login_logs)
Sample answer
| # | Category | Reason |
|---|---|---|
| 1. employees | Master | Reference information about employees. Updated only when someone joins or leaves |
| 2. attendance_logs | Transaction | A history entry added every time someone clocks in or out |
| 3. departments | Master | Reference information about departments. Updated only during a reorganization |
| 4. payroll_results | Transaction | A history of results added monthly |
| 5. salary_grades | Master | Reference information about the salary structure. Updated once a year |
| 6. login_logs | Transaction | Added every time someone logs in |
A common mistake: judging "updated a lot = transaction." A customers (customer master) table with 100,000 members is updated daily as new members register, but it's still classified as master data, since it has the nature of "reference information" — one row per person. The real distinction is "is it reference information or history, not how often it's updated."
Alternative approach: something like "membership that changes over time" (e.g., a history of department assignments) has a nature somewhere between reference information and history. This is called a "period model," which this guide doesn't go into further, but it comes up often in business systems.
Which of the following should be stored on the order line-item table (order_items) — that is, captured as a value at that point in time, rather than looked up from the master?
- Product name
- Unit price
- Product code
- The customer's current address
- The shipping address
- The product's category
Sample answer
Should store a snapshot (= want to protect against future changes to the master):
- 2. Unit price: a product's price fluctuates. If a past order's total changes, that's a problem for accounting
- 5. Shipping address: a customer might move. "Where was the past order shipped to" should reflect the address at order time
- (In some cases) 1. Product name: rebranding or renaming a product could otherwise make past order information unclear
Fine to reference the master (= you always want to see the latest):
- 3. Product code: a business identifier that doesn't change (if it does change, the master would be rebuilt entirely)
- 6. Category: for aggregate reports, "the current category" is fine (consider a snapshot too if you want a detailed history)
- (In some cases) 4. The customer's current address: since the order-time address and the current address are distinct, the current address is looked up from the customer master, while the shipping address at order time is stored with the order
Design principle: ask "if the master's value changes, is there a situation where I'd want to see the old value?" If yes, snapshot it; if no, reference the master.
A common mistake: referencing the master for everything "to save storage," so that the moment the product master's price changes, past sales aggregates change along with it. Prioritizing consistency over storage is the important judgment call here.
Summary
Here's a recap of what this chapter covered.
- Data comes in two kinds: "master data (a dictionary of reference information)" and "transaction data (day-to-day history)"
- Transactions always take the form of referencing master data
- Storing a snapshot of the master's value on things like order line items protects the history from future changes to the master
- Master data follows a lifecycle of "deactivate and keep"; transaction data follows "archive → delete"
- Ever-growing transaction data is handled with archiving, partitioning, aggregation tables, and appropriate indexes
That wraps up Part 1 (Fundamentals) of this guide. Starting with the next chapter, we move into the design section and look at where database design fits within the flow of system development.