Database design problem

[silver157]

Dears
i need to design a database for real estate agency, the price is based on the size of the unit multiplied with the meter price, for example if an unit size is 200 meter and the price for 1 meter is 10 US$ so the price for this unit is 2000 US$.
the problem i face now is i need to but the price in multi currency in a separate table so i can change it easily, like this:-

table1 (unit_id, unit_size, unit_finish, Unit Description)

table 2(price_standard_US$, price_turn_key_US$, price_standard_EUR, price_turn_key_EUR)
the problem now i can't make a relationship between those tables, any ideas?
Thanks a lot in advance for your help and time

[timothybard]

I'm not exactly clear on how the pricing will work, but from what I read, here is my suggestion.

Create two tables with the following fields:
1) Unit: ID, unit_size, unit_finish, Unit Description
2) Price: ID, unit_id, price, currency(USD, EUR, etc)

You record each unit in the unit table. For the price, you record each price in the price table; if there is a price in USD and EUR, then you would have two records in the price table for that unit.

Having said that, if the EUR price is simply the USD price after taking into account the exchange rate, then you can structure the database like this:

1) Unit: ID, unit_size, unit_finish, Unit Description, Price (in USD)
2) Exchange Rate: ID, date, USD, EUR

In this design, you record the price of the unit in your base currency (I chose USD only because I live in the US). You then have a table to track how many USD a EUR is worth. To convert from USD to EUR, you would need to programatically lookup the most recent Exchange Rate record, pull out the conversion and multiply it by the USD value in the unit record.

[silver157]

Dear timothybard

Thanks a lot for your reply;
but i need to know, where is the relation between the two tables??

[timothybard]

In my first reply, I gave you two options:

1)
Unit: ID, unit_size, unit_finish, Unit Description
Price: ID, unit_id, price, currency(USD, EUR, etc)

and

2)
Unit: ID, unit_size, unit_finish, Unit Description, Price (in USD)
Exchange Rate: ID, date, USD, EUR

In the first option, the relation is between the ID field in the Unit table and the unit_id field in the Price table.

For the second option, you need to create a query to show only the most recent Exchange Rate. You would then wrap that query with a Cross Join query to the Unit table.