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.

3

3 Answers

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.

2

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.

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.