Price data modelling

Question

PROBLEM

I currently have a product database. Here's a quick schema:

table bdProduct

|   IDBDProduct |
|   Code        |
|   Description |
|   DCreated    |
|   UCreated    |
|   DModified   |
|   UModified   |

I'm now at the point where I wanna store the price, where I have some problems foreseeing the best way.

Eventually, I'm pretty sure we're gonna go worldwide, depending of the store where I would sell my application, which means that I might have CAN dollars (to start), then US dollars, EUR european, etc.

What would be the best way to store that information? I've already thought about:

| CANPrice |
| USDPrice |
| EURPrice |

I don't know why, but I feel that's not much of a good way. For information, I've been working with that system (CAN/USD/EURPrice) for the past three years, and we've struggled with the question to add a new type.

HERE'S WHAT I ALREADY GATHERED

1. Storing price in another table (I think that would be the best way ?)

   Having a bdProductPrice in which I would save the price and extra information

   | idAuto      |
   | IDBDProduct |
   | RPrice      |
   | PriceType   |
   | DCreated    |
   | ........... |
   

   in which I would store the IDProduct, with it's price and the type of price (is it CAN / USD / EUR)

   What I don't like about this option is that I'll have to make another query each time I read the product to get its price.

2. Adding the price in the current database

   I don't like much this option. I feel like it would polute my database much. And we lose the history of the price of the product as much as who made the modification.

3. Anything else you have to suggest?

   Here we go, I'd be happy to hear about you, what have you tried, used... What worked the best for you?

SIDE QUESTION

I asked that question about the price, but I have the same problem for data that are translated, for example, I'll have a productDescription for English, but I'll have French customer too, so, maybe I should transform the bdProductPrice to a bdProductExtension in which I would store the type of information I wanna give to my products?

Answer 1

different approaches are possible, depending on what you want to achieve.

My suggestions:

• Prices: Try to understand how many currencies you want to serve, if they are <10, then add the columns with the prices in the product table. There you will store the current price. In a second table you can store the historical changes of the prices. So when you need to show the current price you will have it available without additional join. The historical value will be there in case of specific needs. In case you have more than 10 prices or you don't like the solution with multiple columns, you can keep the prices in a separate table like:

  table Product_Prices

  IDBDProduct
  IDCurrency
  Price
  Start_date
  End_date
  

  With End_date like 31-DEC-9999 for the current values (also useful to get the current price).

• Descriptions: usually they are fields with text or even html tags. Like for the prices you can have the same approach, put them in the Product table or keep them separate and use them only when you need. You can even have short descriptions and a long ones, the first in the Product table, the latter in the Product_Description table. I'd rather keep the descriptions separated if they are not required very often.

Anyway at the end of day you need to have this information stored somewhere. You can try to limit the complexity for the front-end of your application creating views for the Price/Language combination of your stores (and maybe also an Extended version with all product data).

 Product_UK     - basic product info, GBP_Price, English Short Description
 Product_FR:    - basic product info, EUR_Price, French Short  Description
 Product_CAN_EN - basic product info, CAD_Price, French Short Description
 Product_CAN_FR - basic product info, CAD_Price, French Short Description
(Product_UK_Ext - all product info, GBP_Price, English Short and Long Description)

Last thing, for what I saw in the past, usually you don't have performance problems dealing with products, prices and descriptions. The huge amount of data for a retail application is in the sales/transactions table, the additional join to get the product description won't hurt your database (but this is just SQL not science :) ).

Answer 2

In my opinion, a data base is a place to store information and moving semantic information (further than the own Data Base schema provides) to the dabase is usually an overhead which many times is not needed.

Unless the same store needs to work with prices in different currencies there is no point of moving this information to the database. Any shopper will usually want all prices using the same currency.

Therefore I would only store a price in the data base (as you initially thought). It's a matter of your the business logic to know what your price "means".

From the point of the whole application for different stores I see two options.

1. Every store application keeps the prices in their own currency. The application business logic would be straitghtforward. This could be a problem if different stores with different currencies need to keep synchronized databases.

2. Every store application keeps the prices in the same currency. The problem above vanish, but theapplication business logic would have a slight overhead, since it would have to do conversions after/prior to reading/writing prices to the data base.

BTW, you might want have a look to this stackoverflow answer about database design patterns, which provides recommended reading.

Answer 3

Maybe I missed something but why not just add a column to your table, perhaps CURRENCY_TYPE, and have another table to be used for referential integrity so users can't put several values for the same currency (CAN, CANADIAN DOLLAR, etc) and you can store whatever information you may want to about the currency.

You'll still need to do a join to the reference table for any information about the currency, but on the base table you'd have the price and it's currency type.