CODEWITHSUNDEEP
sqlsql-serverdatabase
Vidhya
Vidhya

Reputation: 371

difference between master and transaction table

Why do we need to have two tables (master and transaction table) of any topic like sales,purchase,etc.. What should be the relationship between the two tables and what should be the difference between them. Why do we really need them.

Upvotes: 18

Views: 52147

Answers (3)

Sorter
Sorter

Reputation: 10240

Master Table

Master data is for capturing the system. Designing master table is the first step of database design. Master tables hold basic information of the system. To understand master tables, you need to understand the concept of a transaction.

Transaction Table

A transaction is an activity performed by entities within the system. These activities are captured to represent events like item purchase, ticket booking. Understanding transactions will give you clearer distinction of master tables from transaction tables.

Example

For example, to describe a class of students all the information needed will be part of master tables like student info, class info, teacher info, subject info. But to know what daily attendance of the students will be captured in the transaction table.

Reference

Here is an article to help distinguish the two. https://metamug.com/article/difference-between-master-and-transaction-table.html

Upvotes: 3

FallAndLearn
FallAndLearn

Reputation: 4135

Master and Transaction tables are needed in the database schema specially in the verticals of sales.

Master Data: Data which seldom changes. For example, if a company has a list of 5 customer then they will maintain a customer master table having the name and address of the customers alongwith other data which will remain permanent and is less likely to change.

Transaction Data: Data which frequently changes. For example, the company is selling some materials to one of the customer.So they will prepare a sales order for the customer. When they will generate a sales order means they are doing some sales transactions.Those transactional data will be stored in Transactional table.

This is really required to maintain database normalization.

Upvotes: 30

clasikbel
clasikbel

Reputation: 39

In the end, it really depends on the type of data you are working with. If you have a specific example, that might give us a better indication on what you are trying to do. However, in general, a master table would theoretically be constant in relationship to habitual changes seen in your transaction table.

Upvotes: 3

Related Questions