SQL select all items of an owner from an item-to-owner table

I have a table bike_to_owner. I would li开发者_运维问答ke to select current items owned by a specific user.

Table structure is

CREATE TABLE IF NOT EXISTS `bike_to_owner` (
  `bike_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `last_change_date` date NOT NULL,
  PRIMARY KEY  (`bike_id`,`user_id`,`last_change_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In the profile page of the user I would like to display all his/her current possessions.

I wrote this statement:

SELECT `bike_id`,`user_id`,max(last_change_date) FROM `bike_to_owner` 
WHERE `user_id` = 3 group by `last_change_date`

but i'm not quite sure it works correctly in all cases. Can you please verify this is correct and if not suggest me something better. Using php/mysql.

Thanks in advance!

In light of your comments, I'm going to just give a new answer here.

Since you could have a user taking ownership of a bike, then giving it up, then getting it back again and you want to be able to track that history, I would suggest a slight change to your schema:

CREATE TABLE IF NOT EXISTS `bike_to_owner` (
  `bike_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `acquired_date` date NOT NULL,
  `sold_date` date NULL,
  PRIMARY KEY  (`bike_id`,`user_id`,`acquired_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Whenever a bike changes hands, you would update the sold_date for the previous owner to the current date and create a new record for the new owner with a sold_date of NULL and an acquired_date of the current date.

Then your query to determine current ownership would look like this:

SELECT `bike_id`,`user_id` FROM `bike_to_owner` 
WHERE `user_id` = 3 AND sold_date IS NULL

Almost there, but you should be grouping by bike_id and user_id rather than last change date:

SELECT `bike_id`,`user_id`,max(last_change_date) FROM `bike_to_owner` 
WHERE `user_id` = 3 group by `bike_id`, `user_id`

ETA:

As an aside, I'm curious why you have the last change date in that table as part of the key. Would it be possible for a bike to be associated with a user multiple times with different change dates? If so, why? I would think that a bike is either associated with a user or not associated with a user - there's really nothing that could "change" there aside from the relationship being added or deleted. Unless there are other fields on that table that you aren't showing?

Here is the statement that works:

SELECT t1.*
FROM bike_to_owner t1
LEFT JOIN bike_to_owner AS t2 ON t1.bike_id = t2.bike_id
AND t1.last_change_date < t2.last_change_date
WHERE t2.last_change_date IS NULL
AND t1.user_id = 3

I think a lot depends on what information you are trying to store and be able to retrieve from the database. Attempting to second-guess this, I’d propose the following set of tables (and note that only the one column in any of these tables allow nulls):

--  User  -----
user_id  (primary key)
name

--  Bike  ------
bike_id  (primary key)
description
current_owner  nullable  (foreign key to User)

This presumes that “bike ownership” is a criticial and frequently desired piece of information, and is worth adding denormalized data to the structure to make finding a bike’s current user or a user’s current bikes trivial. (A key reason for adding denormalized data is to simplify data retrieval and/or improve performance. Skipping working out sales history on every query you make does both.) If duration of ownership is important, a column for “purchased_on” could be added, or you delve into the transaction tables.

To track when bikes were purchased:

--  Bike_Purchase   ------
user_id  (foreign key to User)
bike_id  (foreign key to Bike)
purchased_on
(primary key on all three columns)

would track every time a user purchased a bike. If instead you’d rather track when a bike was sold, you could have

--  Bike_Sale   ------
user_id  (foreign key to User)
bike_id  (foreign key to Bike)
sold_on
(primary key on all three columns)

To track both purchases and sales, include either both tables or a simple conglomerate:

--  Bike_Transaction   ------
user_id  (foreign key to User)
bike_id  (foreign key to Bike)
transaction_type  **
transaction_date
(primary key on all four columns)

This allows you to accurately track purchases and sales for your users, and disregard transactions by non-users. Sold a bike? Make an entry. Don’t know or care who they sold it to? Don’t make an entry.

Whichever time-tracking table you use, determining duration of ownership is as simple as joining on (user_id, bike_id) and getting max(date). Piecing together the “ownership chain” of a given bike, or what bikes a user owned and when/for how long would be tricky, but entirely doable.

** For transaction_type, you may need to set up a Transaction_Type table to track the different transactions (sold, purchased, traded, found, lost/stolen, etc. etc.) Alternatively, you could make it a varchar containing a descriptive string, or put a CHECK constraint to limit it to selected values.