Database design query

Question

Hi,

I've designed a database structure where data is collected about cars over a period of time, for research purposes. The owner of the car enters a large amount of related data each month about the car, it's performance etc. However, I now have to handle situations where ownership of the car is transferred (possibly more than once), and I'm wondering what the best way to handle this in the database would be.

If a car is transferred, the previous owner should no longer be able to add data about the car, however they should still be able to view the data they entered up until the date of the transfer. The new owner will be able to enter data about the car from the date of the transfer onwards. For research purposes, I need to be able to join the data between these transferred car records and consolidate them into one set of data.

Currently, each car record in the db belongs to an owner via a foreign key in the Cars table. What I'm thinking about at the moment, is to create a recursive parent/child relationship between car records, where one car record may belong to another car record (e.g car_id as a foreign key in Car table). Where a car record belongs to another car record, this indicates a transfer. This allows me to preserve each car record's set of data (specific to its owner), while also chaining together the related car records. In addition to this, I'm thinking about adding a Car_transfer table, to record extra data about the transfer (probably just the date actually, as the previous and new owners will be evident from the owner_ids in the Car table) - adding a date_transferred column in the Car record would likely be largely redundant for most records, so i'm unsure if this data alone merits a new relationship table.

Anyway, i hope this makes sense! I've been going round in circles trying to find a sensible solution - any advice would be greatly appreciated!

Answer 1

I would add a car ownership table. The recursive design isn't too intuitive.

Answer 2

You don't need recursive parent/child here, but just Many-To-Many relationship:

Basically you need links table [cars-owners]:

car_id, owner_id, ownership_date

So you will have data in it:

---------------
1, 2, 2009-01-01
1, 3, 2010-05-01
...

The same car owned by two people with different dates.