Using Application Id Generator vs. Database Id Generator

DB Generator:

• Easy to make sure it's unique
• Needs an extra round-trip (you must read the generated ID back)
• Often pretty simple (sequence)
• When transactions are rolled back, gaps can appear in the sequence (thanks to Kristen for pointing this out).

App ID Generator

• Can be as complex as you need (for example, you can encode the object type in the ID if you want)
• Hard to make unique (unless you use UUIDs)
• You can assign an ID even without talking to the DB

[EDIT] Since UUIDs are pretty expensive (no native support in many DBs, index fragmentation, etc), most apps use a DB based generator.

Another thing to remember is that not all inserts to databases come from the application. To use an application driven guid will really hurt hurt you when you get a new customer and have to migrate 100,000 records from their last vendor.

I think the most important thing is that you pick something that can be usefully used by your business as an identifier. For instance, the DMV puts your license number right on the card, and if you forget your wallet and remember the number, it can be used to verify your identity (e.g. when pulled over by a policeman). You wouldn't put a UUID on a card.

Hiding identifiers from your business is likely to cause a lot of confusion, so pick something that you wouldn't mind telling a customer, client, or business partner. I'm not saying that everyone should be able to memorize it, but you should at least be able to read it to someone over the phone if you are looking at a receipt (for example).

There are exceptions for performance, of course, but those should be used with care and not conflated with a business-visible identifier.

See my related blog post