Complicated sort, needs schema redesign

Databases
1

I wonder if anyone has any thoughts on how to improve this…

We have a number of database tables (designed before my time) which have multiple prices against them. Imagine an item with USD, CAD, EUR and GBP prices. Sometimes (usually) the price only exists in one of those values (ie we may have a USD price, but nothing for the others, other times it may be a EUR price only). So if you imagine something like this:
[TABLE=“width: 500”]
[TR]
[TD]id[/TD]
[TD]name[/TD]
[TD]usd[/TD]
[TD]cad[/TD]
[TD]eur[/TD]
[TD]gbp[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]My item 1[/TD]
[TD]1.23[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]My item 2[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]4.56[/TD]
[TD]0.00[/TD]
[/TR]
[/TABLE]

The currency is converted silently, so you can order in any currency. The problem becomes when pulling from the database and sorting by price. The schema is poor as it is, to be honest. I was looking to change it to something more like:
[TABLE=“width: 500”]
[TR]
[TD]id[/TD]
[TD]name[/TD]
[TD]currency[/TD]
[TD]price[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]My item 1[/TD]
[TD]usd[/TD]
[TD]1.23[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]My item 2[/TD]
[TD]eur[/TD]
[TD]4.56[/TD]
[/TR]
[/TABLE]

This way means that if an item has multiple prices available that it needs two records, but I feel that it’s at least more flexible and doesn’t require us to use cases or similar in a query to determine which field is the price to look at.

The biggest problem though is that these prices are all worth something different when an exchange rate is considered. To be able to sort (ie provide items ordered by price) would mean that we need to perform the calculation on each value before we can actually know how to sort. For example, we’d end up with something like this (which is using cases either way):

SELECT id
     , title
     , CASE
           WHEN usd != 0.00 THEN usd
           WHEN cad != 0.00 THEN cad * 1.0877
           WHEN eur != 0.00 THEN eur * 0.7301
           WHEN gbp != 0.00 THEN gbp * 0.5949
           WHEN usd = 0.00 AND eur = 0.00 AND gbp = 0.00 AND cad = 0.00 THEN 0.00
        END AS price
  FROM items

Or, using the new schema:

SELECT id
     , title
     , CASE
           WHEN currency = 'usd' THEN price
           WHEN currency = 'cad' THEN price * 1.0877
           WHEN currency = 'eur' THEN price * 0.7301
           WHEN currency = 'gbp' THEN price * 0.5949
        END AS price
  FROM items

Both would have an order by price and a limit on them, of course. Neither are ideal, though. It works, don’t get me wrong, but I don’t like the idea of having to do these calculations on the fly in order to determine the order.

I considered doing a couple of queries, imagine one per currency, so at least we can efficiently get the cheapest 10 out for each currency, but this way we end up with a lot of extra data that we’ll throw away, and some of those queries will be plain pointless. It would work, but it would require that we do some of the sorting in MySQL, and some in the client (most likely PHP). But when you start needing to offset, as you paginate through the results, it begins to become troublesome.

Honestly, I’ve never needed to sort in this fashion before, so it’s new territory. I feel that the solution that is currently in place is wrong, but I’m not sure the best way to improve it. I’d be interested in any thoughts that anyone has in order to make this better.

FYI, the table that I’m looking at right now has ~500,000 rows, but there are others with millions. And no, we can’t store an equivalent price that is common because exchange rates change too much so the price would be out of date within 24 hours and require that all records be updated.

Any advice, gratefully received.

Thanks

2

add a currency table, and join to it

that way, no CASE expressions are required

it also means your exchange rates can vary, and you don’t have to hardcode them into your queries

3

Sorry, you lost me. If you mean have another table with the prices in and join that, we do have something similar in some cases, but it still doesn’t solve the problem (unless I’m misunderstanding your solution). We need to be able to select all items, regardless of the actual price or currency, so we’d be joining on the product id which would result in fairly similar results. I wasn’t hard coding exchange rates in, just giving an example of how we’d need to do the calculation. We have an exchange rate table that contains the latest rates

4

well, duh :smiley:

so, you join to it in your queries, yes?

also, what do you do if more than one of the 4 columns is non-zero?

5

At the moment, nothing. I don’t know why it was designed this way but at present there will only ever be one, but moving forward there may be more due to various regional conditions that are, frankly, confusing