CODEWITHSUNDEEP
databaseooprelationshiprelation
BambinoUA
BambinoUA

Reputation: 7110

Correct relations between Product and its Price

I need to implement more correct relation between Product and its Price. I know that sometimes they use this declaration

class Product {
  int id;
  String name;
  int price;
}

But as for me it is not good approach especially if product price can be changed and it is good to have a history of this change. For example user wants to increase the price of the product on some date in the future. Using the above class he lost the history. Yes he can change the price on the date but the product can be purcahges later so the real date of price increase will be incorrect (if take it from first Order). For now I have two ideas of how to design classes.

1 - This one is my favourite idea. All prices are in the separate table. The current price can be taken from it using productId. The SQL view can be built basis on ProductPrice and Product tables to always have a current price. Maybe this way required more GUI, i.e. I need to

class Product {
  int id;
  String name;
}

class ProductPrice {
  int id;
  int productId;
  int price;
  DateTime startDate;
  DateTime endDate;
}
  1. This one is seems to be simpler as the current price is always present in the class instance (table). If user changes the price he must say the system to place the current price record into archive (for example checkbox similar to Archive previous price on product edit dialog).
class Product {
  int id;
  String name;
  int price;
}

class ProductPriceHistory {
  int id;
  int productId;
  int price;
  DateTime endDate;
}

I am glad to hear if am I right or where am I wrong?

P.S. Any other suggestions?

Upvotes: 0

Views: 440

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29649

"Right" and "wrong" depend on the business domain, and pricing in real-world applications can be complex. For instance:

  • Prices can apply to different channels, customers, product combinations, over different periods of time
  • Often, you will have prices change collectively - for instance, when the winter sales start, prices will change for some, but not all, products
  • Users will often want to set pricing in the future
  • Prices usually have a currency
  • Prices are usually expressed as including or excluding sales tax
  • You usually want to guarantee that the application can retrieve prices for an arbitrary point in time, for instance to calculate the profit on a sale last year, or to predict the profit on a sale in the future

Your option 1 is "right" if:

  • You express prices in a single currency
  • Your price is stored as an integer (e.g. Japanese Yen)
  • Your application does not deal with sales tax
  • Your application needs to reason about price across time
  • You want to avoid triggers or application logic to duplicate information

Option 2 is "right" if:

  • You express prices in a single currency
  • Your price is stored as an integer (e.g. Japanese Yen)
  • Your application does not deal with sales tax
  • Your application needs to reason about price in the past, but not the future
  • You allow triggers or application logic to duplicate information

Upvotes: 1

Related Questions