Prices in ZXDB

This is the place for general discussion and updates about the ZXDB Database. This forum is not specific to Spectrum Computing.

Moderator: druellan

Post Reply
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Prices in ZXDB

Post by Ralf »

Inspired by the thread about most expensive Spectrum software:
viewtopic.php?f=21&t=1463

I tried to search such releases directly in ZXDB database.

That's what I came with:

SELECT R.release_price, E.*
FROM entries E
INNER JOIN releases R ON E.id=R.entry_id
ORDER BY R.release_price DESC

The problem is that this sorting doesn't really work as supposed. Release_price in the database is not a number, it's a string. So I get list like this:

Image

The order is wrong, also the column contains values in different currencies so it doesn't make sense to compare pounds to pesetas.

The proper way from database point of view, I guess, would be to hold pure numbers in one column and currency_id in another column

Is there any better way to get games sorted by a price?
[mention]Einar Saukas[/mention] I recall you did some work on ZXDB prices. Do you have any future plans about it?
User avatar
PeterJ
Site Admin
Posts: 6855
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: Prices in ZXDB

Post by PeterJ »

You could use 'left' to get the currency, then the following to convert the remainder of the field to a numeric:

https://stackoverflow.com/questions/596 ... ysql-query
User avatar
StooB
Dynamite Dan
Posts: 1073
Joined: Mon Oct 22, 2018 11:03 am
Contact:

Re: Prices in ZXDB

Post by StooB »

Ralf wrote: Tue Apr 16, 2019 10:44 am It doesn't make sense to compare pounds to pesetas.
It doesn't even make sense to compare pounds to pounds. Inflation was much higher in the 1980's - full price and budget title prices both doubled from 1983-93 - so any comparison is meaningless unless it's over a very short term.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: Prices in ZXDB

Post by Einar Saukas »

Ralf wrote: Tue Apr 16, 2019 10:44 amThe proper way from database point of view, I guess, would be to hold pure numbers in one column and currency_id in another column
... and also additional information (standard or deluxe case, etc) in a third column.

The problem is, this information came from Martijn's WoS, that stores multiple prices in the same field. For instance:

https://spectrumcomputing.co.uk/index.p ... 6&id=11043
https://spectrumcomputing.co.uk/index.p ... id=1000456

Ralf wrote: Tue Apr 16, 2019 10:44 amIs there any better way to get games sorted by a price?
Here's a query to obtain highest prices in pounds:

Code: Select all

select * from  (select
replace(substring_index(release_price,' ',1),'£','') as price,r.*,e.*
from entries e inner join releases r on e.id = r.entry_id) as x
where release_price like '£%' order by price*1 desc;
It shouldn't be difficult to adapt this query for other currencies.

Ralf wrote: Tue Apr 16, 2019 10:44 am@Einar Saukas I recall you did some work on ZXDB prices. Do you have any future plans about it?
A future improvement I'm planning for ZXDB, is to categorize multiple "editions" (or "versions") within each release. For instance, if a certain game was originally released by Ocean and later re-released by Erbe, it's already stored in ZXDB as 2 different releases. However if Erbe sold both standard and deluxe versions, there's currently no distinction in ZXDB. This could be changed, so they would be stored in ZXDB as 2 separate "editions" of the same release (each one with its own price, release date, tape images, inlays, etc). This way, we will have a single price per edition, thus making it possible to split price parts into separate columns as you suggested.

Supporting both "releases" and "editions" in ZXDB would help further integration with SPA2 and ZX-Art. Both these databases currently categorize "editions" instead of "releases".

However this change will take considerable effort. It must be planned carefully and coordinated with other sites that depend on ZXDB, so it won't break anything. But this will have to wait for later. Right now, I'm prioritizing other areas that won't take so much work and provide more visible benefit for end-users, for instance improving data association with magazines.
Ralf
Rick Dangerous
Posts: 2279
Joined: Mon Nov 13, 2017 11:59 am
Location: Poland

Re: Prices in ZXDB

Post by Ralf »

Fair said, Einar!

Now when the structure of old WOS database became public we can all see that it's certainly not perfect and allows for a lot of improvement. It's great that you took the job as otherwise we would have just old, broken website not updated for years.

And I agree totally that things "visible" and annoying to user should have priority. Prices stored as they currently are were used for years and 99% of time nobody complained about them.

So do things in your order. I have a feeling that you'll be doing it right :)
Post Reply