I was reading the documentation and came upon something that has thrown me. In the last section of chapter 6, it says "if you have fully embraced our view that composite keys are a band thing and that entities should have synthetic identifiers (surrogate keys)...."

This was the first time I heard of composite keys being a bad thing. I have little database background (which is why I am interested in Hibernate), so I don't understand what the problem is. I searched the forums and found a couple more references to composite keys being not recommended, but there is no detail on 'why'.

So: Why are composite keys bad? Are they just bad in the context of Hibernate, or databases in general?

And: What is a surrogate key? I couldn't find any information on the site. Is it just when you ignore the composite key columns for that purpose and add another index column?

Thanks.

Composite Keys are bad because most of the time they have buisness meaning which is bad because that means they might change and that will cause you a lot of troubles. And besides they make using Hibernate more difficult.

Surrogate Keys are primary keys without buisness meaning (e.g. object identifiers)

They are not a bad thing if they have no business meaning.
In collection of values and in many-to-many association they don't
have business meaning.

Give a look at:

http://www.lech.priv.at/publications/rwbook/node237.html

Entities represented in the database must be known to the database management system. We as users can also only refer to a particular entity by using its identifying attributes. An identifying attribute (or key column of a table) has a unique value. If a table has only one identifying attribute is is by definition the primary key. However, if multiple attributes are candidate keys, the choice is more complex.

Many legacy SQL data models use natural primary keys. A natural key is a key with business meaning, that is an attribute (or attributes) that is unique by virtue of its business semantics. Examples of natural keys might be the U.S. social security number or Australian Tax File Number. The rule is simple: If a candidate key attribute has meaning outside of the database context, it is a natural key, no matter if it is automatically generated or not.

Experience has shown that natural keys almost always cause problems in the long run. A good primary key must be unique, constant, and required (never null or unknown). Very few entity attributes satisfy these requirements, and some that do are not efficiently indexable by SQL databases. You should be absolutely sure that a candidate key attribute never changes through the data's entire lifetime before promoting it a primary key.

For these reasons, we strongly recommend that new applications use synthetic identifiers (also called surrogate keys). Surrogate keys have no business meaning - they are unique values generated by the database or application. There are a number of well-known approaches to surrogate key generation.

Composite keys only complicate data management and don't contribute anything. However, they are of course required for things like association tables in many-to-many associations.

No, not quite; not if you use an <idbag> :)

Oh well :)

I have a non-business reason to use composite id's.

Maybe ya'll can suggest an alternative because it's nudging me slowly, albeit very unwillingly, away from using Hibernate.

Most of my objects have a composite PK consisting of a sequence ID and a Site ID. This is because my business involves a central database and many (eventually thousands) of remote databases which generate data and synchronize it back to the central DB. The problem is with the surrogate keys generated. If I have a non-composite PK how can I guarantee that surrogate keys generated at the remote sites are unique system wide? (performance and reliability issues prevent me from querying a central ID server)

The most airtight method I've found is to have a composite id consisting of a surrogate key and a site id. But then I get into all of the issues Hibernate has with composite id's. :(

Any suggestions?

Couldn't you use some kind of HiLo scheme, where you assign each "site" an unique range of id's it can generated in?

Yeah. That's not as airtight as a composite id, though. Id's could still overlap. Maybe I'm overly paranoid. :)

I'm considering that approach now. Basically each site would have a 32 bit offset that would be applied (XOR'ed to the top 32 bits) of each site objects's 64 bit id when generated.

Another problem is that I'm a few months into coding with composite id's and will have to retool everything. I've modularized enough that (hopefully) that won't be overly painful. I was just hoping that Hibernate's problems with composite-id's would become more palatable. :(

OK, Hibernate just makes my life too easy.

I just got done converting all of my composite id's to single column surrogate keys. It's taken about an hour of coding and two hours of testing.

Works like a champ.

I've got 30 joined-subclass mapped objects with about that many interobject relationships, lots of complex queries. A relatively major structural change took an hour to code and two hours to test. Who could ask for anything more? :)

This all makes complete sense. I understand how composite keys impart business logic into the database context and why that would be bad. Standard tier de-coupling, right? But taking that a step farther, how is that different than table relationships? If a Parent has a Child, and that is reflected as foreign key relationships in the database, isn't that imparting business logic into the database as well?

No.

I see where you're coming from, angryplanets, but by the same logic ANY database structure could ascribed to business logic. By that logic you could say that the table and field structure of a DB is derived from business logic.

Does this get us anywhere? Nope.

I think the mistake the Hibernate team is making is in characterising all use of composite keys as being driven by business logic. (i.e. they're always "organic" and never "synthetic.") Granted, this is true most of the time, especially in the case of legacy databases. But there are cases (including mine) where there are legitimate, purely synthetic reasons for using composite keys.

However, I think those cases are rare. It could be argued that they're so rare as to be too low on the list of development priorities for the Hibernate team to consider making the necessary improvements. Despite my need for Hibernate to better handle composite id's I'd have to agree with this argument.

Greg,

I totally agree with you. I wasn't making the case that table relationships should be viewed as business logic, I was just trying to figure out where (and how) they drew that line. Maybe I didn't make that clear. To me, not being a database guy, I don't see a huge difference between calling uniqueness contraints and relationships "business logic", but I accept that is where the line is drawn.

Thanks for clarifying.

Btw... I am also in Dallas, and I might write you a quick YM about something today if that is cool with you.

I can't YM at the moment. e-mail me at greg_barton@yahoo.com