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.
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.
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.
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 |
