Where is the best place to place your Id Generator? Using Database Generators? Using Custom Generator? What are the advantages/disadvantages?
+4
A:
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.
Aaron Digulla
2009-08-31 09:36:04
Thank you for that list. Is there a best-practise for placing the Id Generator? DB Generator sounds easier to me.
bastianneu
2009-08-31 09:52:45
See my edits. Unless you write clustering software, the generator is usually in the DB.
Aaron Digulla
2009-08-31 11:28:23
I like this answer the most, cause its an simple List with important points you have to consider. Thank you!
bastianneu
2009-09-01 08:10:44
DB's function for assigning IDs (e.g. IDENTITY) may not provide contiguous numbers (e.g. if INSERT is rolled back), a point sometimes overlooked e.g. if used to assign Invoice numbers; of course you can write your own ID generator function :)
Kristen
2010-02-11 08:33:01
+5
A:
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.
HLGEM
2009-08-31 13:12:05
i am sorry i dont get the point from your answer. Can anyone clear that up please?
bastianneu
2009-08-31 15:42:24
The point is you are putting your data at risk if you generate the identifer anywhere except the database.
HLGEM
2009-08-31 17:50:35
+1, depending on your setting and how many different systems you interact with, it may not be possible to guarantee that all of the records in your database will be generated from within your application. You may be required at some point in the future to incorporate data from an external source, either as a one-time migration (as HLGEM suggests) or as part of a batch load process. In either case it would be much harder to load those records if your ID comes from within the application. You could have a web service or some other kind of API, but that would have a performance impact.
John M Gant
2009-08-31 19:37:05
+1
A:
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
Jeff Davis
2009-08-31 17:30:54
Please don't mix business keys and technical keys. Identifiers are technical keys to uniquely identify an object (for example in foreign keys). They play well with indexes, they are small and consistent in one way or the other. The business keys (like the license number) are what you display to the outside but since they aren't reliable (they can change in any way and they follow reality, not rules), you must not depend on them as primary keys. Think name: Your name can change when you marry. Or you can have two "John Smith" with the same birth date in your database.
Aaron Digulla
2009-09-01 10:00:12
@Aaron: "Please don't mix business keys and technical keys." I didn't confuse them, I specifically addressed the difference. @Aaron: "have two John Smith with the same birth date" That's a business reason to invent a new identifier, not a technical reason.
Jeff Davis
2009-09-01 16:43:33