0

We have N persons in a city and one Car. A person can be an owner of this Car but the condition is a person at a time. I mean if there are 5 persons only one of them can be owner of the car at a time.

now problem is any person can be the owner of this car at any moment(we can decide that with any logic, for e.g. every day owner can be change, or may be some more complex algorithm, but that doesn't matter, just keep in mind that owner has changed at this particular time moment and remained owner till a time)

A owner from time T1 to T2
another Owner from T2 to T3
... so on.

Now another thing is any person can left the city OR/AND a new person can come in the city and have same rights as others, I mean new person can be the owner of this car.

I want to design a DB for this problem so that I can be able to query all important things efficiently. e.g.
who was the owner of the car at this time-stamp.? who were the owners of the car during this time period?, longest time remained owner? all persons in the city available at a time? etc.

any suggestions would be appreciated.

Improve this question
1

1 Answer 1

Reset to default
1

One of the biggest problems in database design is that people design for the user interface not the eventual use of the data. This is triply true when you have temporal data. To properly design this you must understand that temporal data is not denormalized data. It is storing history over time.

I would suggest a table with the car information, a table with the owner information and a join table that contains the carid, the ownerid and the start and end dates. You may also want to have other data concerning the time period such as notes or whether insurance was used or issues reported about the car at the time, etc. I don't know all the requirements for reporting on the various usages that you will have. But I do know that you need to talk to your stakeholders and find out what the reporting needs are going to be before trying to design this.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.