Price data modelling

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

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.
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.
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?

Solution

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 :) ).