Multidimentional table in mysql?

thepreacher

How do i represent a multidimentional (2 0r 3) table in mysql and how do i read from and add to such a table.

Thanks

Rodney_H_

Can you please provide a little more information as to what you are trying to do here please?

NogDog

As Rodney says, we probably need more info on the functional requirements to give a firm answer; but most likely you're looking at defining multiple tables, at least some of which utilize a foreign key to another table.

thepreacher

It is currency converting webpage where one can go enter a source amount and currency and they will see the equivalent amount in their chosen currency. I have been been given a currency conversion rate table to use for the purpose. It is a grid like table with the various currencies serving as columns and the same as rows. So the appropriate rate to use will be the value at the intersection of any two columns.

I am thinking i need to convert this table into a mysql table. That is what i'm struging with. Hope i make a bit more sense now.

void_function

Yeah, NogDog gave good advice, you'll need two tables and foreign keys. In first table, just store currency name with its ID:

1 EUR
2 USD
3 YEN
4 ....

In second table store the rates, with three fields:

currency1, currency2, rate

Where currency1 and 2 are IDs in the first table, and rate is the rate of conversion from currency1 INTO currency2. Get the reverse with 1/rate.

NogDog

You could arbitrarily select one currency as the standard, let's pick EURO for this example. Then you could create one table with, at a minimum, 2 columns: "currency" and "euros". For each currency you track, you'd have one row, with the "euros" value being the equivalent number of Euros for one unit of the currency in question. Then to convert from one to another, take the first currency type and multiply its amount by the corresponding "euros" value from the table, then divide that result by the "euros" amount for the target currency type.

This may not be the "slickest" or fastest method, but it seems to me it would be simple to implement and easy to maintain. (This does assume an equivalent, unbiased conversion rate amongst the various currencies. If this is not the case -- i.e.: Pesos to USD gets a more favorable conversion rate than Pesos to EUROS to USD -- then you'll need to do the full table conversion.)