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.