I'm trying to come up with a data model, and I'm stuck with this one table. It's named Rental_History, and it tracks the rental history of vehicles for a car rental service. My issue is this:

Currently, a member can rent the same car as many times as they want as long as they are different instances. So, in the table, I just need to make sure that there's something uniquely identifying this rent transaction. So a memberNumber for the user, VIN for which car, and then I was thinking of doing start date as the third field in the composite key, but for users, a rental date may not be the easiest to remember/find. That's when I started thinking about using a transactionId, which uniquely identifies the entire transaction.

This would get rid of the need for memberNumber and VIN to be a part of the primary key. At that point I might as well use an autoincrement field as the lone primary key. Or, should I use memberNumber, VIN, AND transactionId as the composite primary key?

I'm struggling to decide how to model this data, especially since I want to ensure data integrity when I am joining this table on VIN, or memberNumber to my other tables, which is what leads me to believe I should have those fields as a part of the primary key. Also, yes, transactionId will uniquely identify the row, but transactionId, memberNumber, and VIN will too.

  • Faced with a similar situation (not involving cars) I created a composite transaction ID and used the auto increment column to keep things unique. The composite transaction ID can/should be unique too and indexed if it is used as a pointer to another table or as a foreign key of course. It has been working out very well. – Dave Mar 21 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.