The type of information I wish to store within this type is, for instance: "330.30 eur/ton" or "25.15 usd/kg".
- Cost as decimal.
- Currency like a FK to a different table.
- Weight unit like a FK to a different table.
When creating queries I must access the people directly e.g. "selling_cost.cost" or "selling_cost.currency".
And I must have the ability to sort with this area too... Which produces lots of problems, I recognize, as we have to convert foreign currencies and weight models.
One solution is always to give a 4th element, which stores exactly the same information inside a standardized format (e.g. usd/kg like a float), sort with that, after which update this once we up-to-date foreign exchange rates.
So, essentially, this practice type (let us refer to it as Cost_PER_UNIT) would store:
- Cost as decimal.
- Currency as FK.
- Weight unit like a FK.
- Value in standardized format.
And sorting about this area would need to be carried out by the standardized area, like a float.
Since I have not done anything enjoy it in SQL before, I've no clue how you can even get began with any one of this... Anybody?
PostgreSQL supports CREATE TYPE for creating composite types. I believe that's where that you should start.
Unless of course you are while using beta 9.1 version, avoid PostgreSQL types such as the plague. They are an enormous discomfort to update, and it is not until 9.1 that you could add or remove values from enums or alter characteristics. For other versions, you are restricted to renaming, altering the dog owner, and altering the schema.
You might want to be skeptical of storing the "standardized" cost for sorting. Here I am presuming you are speaking about storing everything as (say) $ $ $ $ per kilogram. Anytime the foreign exchange rates change, you are going to need to update every row inside your table. Rather, you might want to keep conversion factor for that currency into USD, and keep conversion factor for that weight into kg, then sort according to an calculated value (conversion factors in the currency and weight type tables are became a member of for your data table).