What's the best way to store (and access) historical 1:M relationships in a relational database?

Question

Hypothetical example:

I have Cars and Owners. Each Car belongs to one (and only one) Owner at a given time, but ownership may be transferred. Owners may, at any time, own zero or more cars. What I want is to store the historical relationships in a MySQL database such that, given an arbitrary time, I can look up the current assignment of Cars to Owners.

I.e. At time X (where X can be now or anytime in the past):

• Who owns car Y?
• Which cars (if any) does owner Z own?
  

Creating an M:N table in SQL (with a timestamp) is simple enough, but I'd like to avoid a correlated sub-query as this table will get large (and, hence, performance will suffer). Any ideas? I have a feeling that there's a way to do this by JOINing such a table with itself, but I'm not terribly experienced with databases.

UPDATE: I would like to avoid using both a "start_date" and "end_date" field per row as this would necessitate a (potentially) expensive look-up each time a new row is inserted. (Also, it's redundant).

Answer 1

Make a third table called CarOwners with a field for carid, ownerid and start_date and end_date. When a car is bought fill in the first three and check the table to make sure no one else is listed as the owner. If there is then update the record with that data as the end_date.

To find current owner:

select carid, ownerid from CarOwner where end_date is null

To find owner at a point in time:

select carid, ownerid from CarOwner where start_date < getdate()
and end_date > getdate()

getdate() is MS SQL Server specific, but every database has some function that returns the current date - just substitute.

Of course if you also want additional info from the other tables, you would join to them as well.

select co.carid, co.ownerid, o.owner_name, c.make, c.Model, c.year  
from  CarOwner co
JOIN Car c on co.carid = c.carid
JOIN Owner o on o.ownerid = co.ownerid
where co.end_date is null