First, I am conscious of this, and also the suggestion (using GUID) does not apply during my situation.
I would like simple UIDs to ensure that my customers can certainly communicate these details over the telephone :
Hello, I have got an issue with order 1584
hello, I have got an issue with order 4daz33-d4gerz384867-8234878-14
I would like individuals to become unique (database wide) because I've got a couple of different type of 'objects' ... you will find order IDs, and delivery IDs, and billing-IDs and also, since there is no one-to-one relationship between individuals, I've not a way to you know what type of object an ID is mentioning to.
With database-wide unique IDs, I'm able to immediately tell what object my customer is mentioning to. My user can just input an ID inside a search tool, and that i save him the additional-click to help refine what's searching for.
My current idea is by using identity posts with various seed products 1, 2, 3, etc, as well as an increment worth of 100.
This boosts a couple of question though :
Let's say I end up a lot more than 100 object types? granted I possibly could use 1000 or 10000, but something which does not scale well "smells"
It is possible to possibility the seed is "lost" (throughout a replication, a database problem, etc?)
more generally, exist other conditions I should know?
can you really make use of an non integer (I presently use bigints) being an identity posts, to ensure that I'm able to prefix the ID with something representing the item type? (for instance a varchar column)
will it be smart to user a "master table" that contains only a name column, and perhaps the item type, to ensure that I'm able to just place a row inside it each time a require a new idea. I seem like it may be a little overkill, and I am afraid it might complexify my insertion demands. Plus the truth that I will not have the ability to determine an item type without searching in the database
exist other clever methods to address my problem?
Why don't you use details on all of the tables, but when you present it towards the user, simply tack on one char for that type? e.g. O1234 is definitely an order, D123213 is really a delivery, etc.? This way it's not necessary to engineer some crazy plan...
You could utilize an autoincrement column to create the initial id. Then possess a calculated column that takes the need for this column and prepends it having a fixed identifier that reflects the entity type, for instance OR1542 and DL1542, would represent order #1542 and delivery #1542, correspondingly. Your prefix might be extended around you would like and also the format might be arranged to assist distiguish between products with similar autoincrement value, say OR011542 and DL021542, using the prefixes being OR01 and DL02.
Handle it in the interface--give a prefix letter (or letters) to the ID number when confirming it towards the customers. So o472 could be a purchase, b531 will be a bill, and so forth. Individuals are quite comfortable mixing letters and numbers when giving "amounts" over the telephone, and therefore are better compared to straight numbers.
I'd implement by determining a normal root table. For insufficient a much better title refer to it as Entity. The Entity table must have at least just one Identity column onto it. You might include other fields which are common accross all of your objects as well as meta data that informs you this row is definitely an order for instance.
All of your actual Order, Delivery...tables may have a FK reference to the Entity table. This provides you with just one unique ID column
While using seed products for me is an awful idea, and something that can lead to problems.
A few of the problems you pointed out already. I additionally check this out as being a discomfort to trace and be sure you setup brand new organizations properly. Make a developer upgrading the machine 2 yrs from now.
Once I authored this answer I'd thought a but much more about why your carrying this out, and that i found exactly the same conclusion that Matt did.
MS's intentional programing project were built with a GUID-to-word system that gave pronounceable names from random ID's
Why don't you an easy Base36 representation of the bigint? http://en.wikipedia.org/wiki/Base_36