Posted by: ahmedashfaque | March 6, 2016

Database design – master data and transaction data

While doing database design for creating a relational database, people often make mistake. These mistake include emphasizing too much consideration for entities and their relationships. People forget that the type of data which needs to be kept in tables is equally important. The other mistake they make is in the area of normalization. Let us discuss these issues.

Suppose you need to keep information about customer orders. But before you can think about orders, you need to think about how these orders are important from the point of view of the marketing department, the sales department and the finance department. Each department wants to have meaningful information about orders. The marketing department will like to get information related to customer segments, most profitable customers etc. The sales department may be interested in getting information about sales per region, sales for the year etc. The finance department may be interested about cost of sales, cost of marketing etc. for each product.

To satisfy needs of various departments, you need to create a good database design. From the description given above, it is clear that entities like sales regions, costs, products, customer types etc. need to be created. Once you have defined good entity relationships then you will get data about orders belonging to these entities.

So far we have discussed entities and their relationships. When you create data about these entities then you will essentially create master data. But when the software product is used by users then they start creating data when they execute orders for customers. This data is the transaction data. This data is very different from the master data which are created when you describe the entities in terms of data. For example when you have data about different sales regions in tables then these pieces of data are master data. But when users take orders and execute them then an order may belong to a sales region. Now this order data is a transaction data. A piece of transaction data will contain information such as time stamp data, quantity of goods sold, amount of order, taxes etc.

When you design your database, master data always belong to database tables which have primary keys. The transaction tables always have foreign keys. This relationship is important to understand. Only then you will be able to create good database design.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: