I have experienced solutions to some couple of similar questions requested on SO, but tend to not determine what I had been searching for.

It is possible to more effective method to generate 8 character unique IDs, base 36 (-9A-Z), than producing a distinctive ID and querying the DB to ascertain if it already is available and repeating until you receive a unique ID that is not used?

Other solutions I discovered use time, but this really is possibly too simple to guess and could not work nicely in distributed systems. Think about these IDs to become coupons.

One choice is to get it done the opposite way round: generate a large number of them within the database whenever you have to, then either fetch just a single one in the DB when you really need one, or reserve a lot of them for the particular process (i.e. mark them as "potentially used" within the database) after which dole them from memory.

I question that the "inefficient" approach is really inefficient. Think about this:

  • You will find 36^8 == 2,821,109,907,456 (2.8 Trillion) possible IDs.
  • For those who have N existing IDs, the risk of a brand new at random produced ID colliding is N in ~2.8 trillion.
  • Unless of course N is incorporated in the 100s of billions, you "produce a unique ID and querying the DB to ascertain if it already is available" formula will more often than not terminate in a single cycle.

With careful design, you need to have the ability to produce a guaranteed unique ID in a single database request, many of the time ... unless of course you possess an awfully many existing IDs. (And when you need to do, just add another handful of figures towards the ID and also the problem disappears again.)

If you wish to, you are able to lessen the average quantity of database procedures to under one per ID by producing the IDs in batches, however their are potential complications, especially if you want to record the amount of IDs which are really being used.

But, for those who have for the most part 150,000 IDs (I suppose, produced on the lengthy time period) then creating the IDs in batches isn't well worth the effort ... unless of course you do a bulk upload operation.