RDBMS - Difference between Master and Transaction Table

Introduction 

There are two types of tables in a database system, master, and transactions. Identifying these tables during the database development is important to understand, how the system interacts with the database.

Master Table 

During database design, the master tables are designed first. Since the purpose of master tables is to capture the system. The design of the master tables i.e. its columns and constraints describe the entities in the system. For example user, account, customer etc. Generally, entities of the system are mapped to master tables.

What is a Transaction Table? 

To understand the difference between the two types we need to understand what exactly we mean by a transaction. A transaction is an activity performed by entities(master tables) within the system. These activities are captured in transaction tables and usually, these transaction entries have foreign keys to master records.

Transaction tables are designed to store events in the system.

These events are associated with master records to ensure normalization. Because the transactions can quickly grow in large numbers. The analytics tools, OLTP, partitioning are applied on transaction tables. Most of the querying is done on transaction tables.

 The Pie charts, line charts, and graphs are drawn using transaction tables. The design of transaction tables.

Identification Criteria 

The following criterias helps us identify whether a table should be classified as master or transaction.

Criteria	Master	Transaction
Changes	Data which is less likely to change.	Data which frequently changes.
Information	Stores master data. e.g. inventory, suppliers, users, customers.	Stores the transactions in the system. e.g. orders, attendance, sales.
Timestamp	Master tables may not require a timestamp associated with each entry.	Every transaction usually is associated with a timestamp. Since it's considered as an event in the system at a particular point in time.
Records	Records are less compared with Transaction table.	Records are more as compared with Master table.
Partitions	Vertical Partitioning for normalization	Horizontal Partitioning based on timestamp/date for easier querying.
Use	Store system information	Capture System events
Indexed	Usually No	Yes