Using Application Id Generator vs. Database Id Generator

Question

What is the best strategy to generate database IDs? Using database generators? Using a custom generator? What are the advantages and disadvantages of each?

Answer 1

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.