Skip to content Skip to main navigation Skip to footer

Modeling Price Changes

Objectives

This tutorial covers the following objectives:

  • Solve the business requirement of tracking changes in price or values by constructing a model that uses a historical entity
  • Describe the meaning of journaling/logging
  • Identify the business need for journaling/logging and construct a model that addresses this requirement

Purpose of Price Changes

  • Price changes: Historical data of prices are important when looking for trends, determining the appreciation or depreciation value of items, or getting a refund for an item purchased in the past at a previous price.
  • Many businesses track history of change — who changed it, when it was changed, and so on.
  • Example: if a student’s grade is changed, it is useful to record when it was changed, the old grade, the new grade, and who changed it.

Appreciation: A rise in value or price, especially over time.

Depreciation: A decrease or loss in value because of age, wear, or market conditions.

The Importance of Price Changes

  • Changes in price are often an important consideration when modeling business requirements.
  • Some examples would be:
    • The stock market: Prices are changing by the second and you are watching the reader board, wondering when to buy and when to sell. What factors would you consider?
    • The fuel industry: Why would you want to track the price changes in fuel if you are thinking of buying a car or heating your home during the winter?
    • Construction businesses: Why are price changes important to a contractor of a five-year bridge-construction project?

US Stock prices usually take a sharp dive or rise during times of crises or just before an announcement by the US Federal Reserve. If a stock price has been rising rapidly for a long time, you may wonder how much longer the rise will continue. You would need to know the history of the stock price. These are some of the things to take into consideration when deciding when to buy or sell.

What’s the Price Today?

  • The prices of products change over time.
  • Some go up, some go down, and others fluctuate up and down.
  • Food, clothing and school fees are more expensive now than they were twenty years ago.
  • Technology often gets cheaper over time.
  • You can buy a standard specification laptop computer today for around half the price of ten years ago.
  • Gold, silver and currency are examples of commodities whose prices fluctuate.

Model Price Changes

  • It is often useful to have information on past prices.
  • The model shown here tracks the historical price of a product.
Model Historical Price

Do you really need an end date? It depends. If the various periods of a product price are contiguous (i.e. no gaps), then you don’t need an end date.

You can derive the end date from the start date of the next price associated with the item. However, if the products are not always available, as in the seasonal fruit and vegetable market, then you need an end date.

Note: in this case, it is possible to have products that don’t yet have a price, hence the relationship is optional at the PRODUCT end.

Historical Price Changes

  • Consider what happens when you return an item to a store.
  • You purchased the item at a certain price, but it has gone on sale since then.

Tracking Price Changes

  • Businesses often need to keep a record of price changes.
  • In this model, we assume that each PURCHASE is of only one product.
  • The price that was paid can be found by matching the purchase date between the start date and the end date of PRICE.
Tracking Price Changes

Note that you cannot model the derived “between relationship”. It needs to be documented and implemented with additional code in the system.

The PURCHASE entity may also contain other attributes or relationships to the CUSTOMER and the EMPLOYEE who processes the transaction. For simplicity’s sake, we don’t include them here.

Other Data Changing Over Time

  • We’ve seen that prices change over time.
  • Other types of information can also change, for different business reasons.
Price Changes

Journaling

  • Whenever a system allows a user to modify or remove particular information, the question should be asked, “Do the old values need to be kept on record?”
  • This is called “logging” or “journaling.”
  • This is often an issue when the information is financial or of a sensitive nature, such as a student grade change.
Journaling

The PAYMENT entity could have additional attributes and relationships to PAYOR and PAYEE, but we have left them out of the diagram to keep things simple.

Apart from the consequences for the conceptual data model, the system needs special journaling functionality: any business function that allows an update of PAYMENT amount should result in the requested update, plus the creation of a new instance of AMOUNT MODIFICATION with the proper values. This functionality has to be implemented by programming.

Journal Content

  • A journal usually consists of both the modified value and the information about who did the modification and when it was done.
  • This extra information can, of course, be expanded if you wish.
Journal Content

Again, this system needs special journaling functionality: any business function that allows an update of EMPLOYEE salary amount should result in the requested update, plus the creation of an entity instance SALARY CHANGE with the proper values.

An alternative way to model salary changes (and many other examples of tracking changes over time) would be to include both old and new salary amounts as attributes of SALARY CHANGE, and remove the salary amount attribute from EMPLOYEE. The 1:M relationship would then be mandatory at both ends. How would we find an employee’s current salary? It’s the new salary amount of the SALARY CHANGE instance with the most recent date modified.

Terminology

Key terms used in this tutorial included:

  • Appreciation
  • Depreciation
  • Journaling and/or logging
  • Price changes

Summary

In this tutorial, you should have learned how to:

  • Solve the business requirement of tracking changes in price or value by constructing a model that uses a historical entity
  • Describe the meaning of journaling/logging
  • Identify the business need for journaling/logging and construct a model that addresses this requirement

Was This Article Helpful?

1
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *