ERD Questions

[cknight]

Hi all,

I am trying to map out and ERD diagram for a Solution for my company. I am having a hard time determining what i should seperate as entities or attributes of an entity. I have other areas that I will tie together such as contacts etc but i want to focus on the problem area i am having.

I have about 5 different products. So obviously i would have a product entity. I also have Prices for each of these products BUT i am starting to sell more overseas and i need to have Multiple prices for these products. So Now i have a Many to Many relationship.My first question is Should i have a seperate entity for currency? and then make a join table between Currency and Products and call it Product Currency or is there a better way to do this?

Secondly I am also having to deal with Measurement differences. Obviously feet, gallons, inches in the US and the Meteric in the rest of the world. I really don't have any idea how to deal with this. Suggestions would be welcome.

Just some background. The products we sell are Daily covers for Waste sites each product covers a different sq footage and we show the prospective client the cost per sq ft. but we need to do it in meteric and be able to change the currency so they see the cost savings in their terms. I know this is a many to many relationship but i am dealing with 2 variables Currency and Measurement standards. Do i need to make a seperate join between these 2 called Currency measurments as well as the other joins between products etc...

Its just become a confusing mess. I have attached a PDF of the ERD so far if that is any help.

Anyone else out there had to deal with this? Am I thinking this through right?

ERD_test.pdf

[comment]

Aren't you making this more complicated than it needs to be?

UnitConversions.fp7.zip

[cknight]

Wow!!!

I see how your doing it. Thats really neat. Iam gonna try to add a field that calculates the cost per sq ft or sq meter. I can do it the same way you've done it?

I really appreciate the help. I am trying to learn this and its not something i have done before. Thank you Thank you

I have attached an excel spreadsheet to give you an idea of what i am trying to build if you could mb take a look at it and tell me if this design will work for it would be much appreciated. Right now i manual have to change the data in the cells and iam dealing with a crazy amount of spreadsheets and thats why i wanted to do this. Its been a great learning experience thus far and everyone here has been so very helpful.

Thanks a million

Carlisle Knight

excelref.xls.zip

[comment]

It is rather difficult to follow. I think you should make yourself a list of input data and divide it into groups - data from specific product, data from specific customer, data from (... I don't know, e.g. cost of fuel??). This will help you to develop a work-flow for the solution.

For example, in my demo the data is:

- Area (attribute of selected Product);

- Units system (selected locally);

- Currency (attribute of selected Country);

- Rate of exchange (attribute of selected Country)

But the country could easily be an attribute of customer, so by selecting the customer, it could automatically select the correct currency - and also the appropriate units of measurement, if we wanted.

Also make sure you separate real data from constants and calculations. For example, the ratio of square feet to square meters is NOT data, but a constant.