I'm being asked to add extra information to an existing database structure that involves a User table and a trading card table called Card.
The extra information that I'm asked to add is the purchase price and date for when the user purchased one of the trading cards, so I figure that a third table is required, called UsersCard
Whenever a card is sold to someone else, a new entry is added to UsersCard. That table is to contain a continuous history of purchase date and price, which means that a user may have several entries for a single card, only the most recent entry being applicable for when viewing the card information.
Collecting the card information has been tricky for me though, and I can't help but think that there is a better way to retrieve the information.
SELECT Card.*, CurrentCards.price, CurrentCards.purchased
LEFT OUTER JOIN (
INNER JOIN (
SELECT max(id) as maxid
WHERE UsersCard.userid = %d
GROUP BY cardid
) as MostCurrent
ON MostCurrent.maxid = UsersCard.id
) as CurrentCards
ON CurrentCards.cardid = Card.id
WHERE Card.userid = %d
ORDER BY Card.id
It works, but it seems to be a lot of code just to get a the cards for a user with the most recent UsersCard info for that users cards.
Is there a better way of retrieving this info that I'm just not seeing?
you need a subquery to find the max id, then you need to join back to UserCards to get the price, then you need to join to cards
so, no, you're doing it the best way already
Thanks for the confirmation there.
If you were to come across a similar situation like this, would you have handled the table structure in a different (potentially better) way?
depends on scale
if the MAX subquery approach performs adequately, i'd leave it
alternatively, add yet another table, similar in structure to usercards, but which will contain only the current cards, and maintain this table whenever a card is sold
then your query can avoids the subquery altogether
If performance becomes a issue flagging the most recent card will eliminate the need for the subqueries. Maybe not ideal but if performance becomes a issue its something to think about.
Edit: Which is pretty much what r937 said above but with an additional table.
If you decide to maintain a separate table, triggers for insert, update, and delete could be a decent way to go about it.