Composite Key of Composite Keys

Question

I'm trying to get an idea of what approach to take for a data model I'm creating. I have two tables which I originally created using composite keys. I am now adding a 3rd table which is a join table of the previous two, which would result in a composite key of three fields and two foreign keys with 2 fields each. This will likely be in MySQL and be used along with some sort of Java persistence framework. I've always liked using composite keys because it seems like a more natural way to represent the data, but I want to make sure I'm not setting myself up for a world of hurt later on. Should I keep going with the approach mentioned or just create some auto incrementing ids on the tables?

Answer 1

Hibernate recommends using purely technical, auto-generated, non-composite keys (except for pure join tables). And for very good reasons, IMO.

Your mapping will be much harder with composite keys. The performance will be reduced because of more complex indices. The general programming will be harder because instead of being able to identify a given entity with just a long, you'll need two or three longs (think about URLs and forms, for example, where you'll have to put two or three parameters/hidden fields to your URLs/forms).

And of course, if the keys are functional, it's even worse, because you WILL have to change one of the parts of a primary key some time, which will force you to update all the references to this primary key.