2

I'm designing an application where my Order objects need to have a sequential and user-friendly Id field. I'm avoiding the HiLo algorithm because of the rather large gaps it produces (see here). Naturally, Guid values would make my corporate users go bananas. I'm also avoiding Oracle sequences because of the major disadvantages of it:

(From: NHibernate POID Generators revealed)

Post insert generators, as the name suggest, assigns the id’s after the entity is stored in the database. A select statement is executed against database. They have many drawbacks, and in my opinion they must be used only on brownfield projects. Those generators are what WE DO NOT SUGGEST as NH Team.

> Some of the drawbacks are the following:

  • Unit Of Work is broken with the use of those strategies. It doesn’t matter if you’re using FlushMode.Commit, each Save results in an insert statement against DB. As a best practice, we should defer insertions to the commit, but using a post insert generator makes it commit on save (which is what UoW doesn’t do).
  • Those strategies nullify batcher, you can’t take the advantage of sending multiple queries at once(as it must go to database at the time of Save).

Any ideas/experience on implementing user-friendly IDs without major gaps between them?

Edit:

  • User friendly Id fields are ones my corporate users can memorize and even discuss and/or have phone conversations talking about a particular Order by its code, e.g. "I'm calling to know why the order #1625 was denied.".
  • The Id doesn't need to be strictly gapless, but I am worried that my users would get confused when they see gaps like 100, 201, 305. For my older projects, I currently implement NHibernate using Oracle sequences which occasionally lose a few sequences when exceptions are thrown, but yet keep a rather tidy order to them. The downside to them is how they break the Unit of Work which results in additional hits to the database for every Save command with or without the Session.Flush.
Community
  • 1
  • 1
rebelliard
  • 9,592
  • 6
  • 47
  • 80
  • How do you define "user friendly ID"? Just as consecutive integers? – Roman May 08 '11 at 01:54
  • Can you expand further on these "major disadvantages" of oracle sequences? I have never come across any issues with them when used correctly ( appropriate cache setting for your environment ). Why do you need it gapless, whenever someone asks for this, I tend to feel they are generally walking down the wrong path. – Matthew Watson May 08 '11 at 02:35

1 Answers1

1

One option would be to keep a key-table that simply stores an incrementing value. This can introduce a few problems, namely possible locking issues as well as additional hits to the database.

Another option might be to refine what you mean by "User-friendly Id". This could consist of a combination of a Date/Time and a customer-specific sequence (or including the customer id as well). Also, your order id does not necessarily have to be the actual key on the table. There is nothing to say that you can't use a surrogate key with a separate "calculated" column which represents the order id.

The bottom-line is that it sounds like you want to use a surrogate key, but have the benefits of a natural key. It can be very difficult to have it both ways and a lot comes down to how you actually plan on using the data, how users interpret the data, and personal preference.

Bobby D
  • 2,129
  • 14
  • 21
  • I actually like your idea of a "complex" date+customer enough to don't mind an extra hit to the database for it (if required). For a creation date of "05/07/2011 11:30:10pm" and a customerId of "614", how would you prefer the resulting surrogate key to be? **I really like this idea. – rebelliard May 08 '11 at 03:58
  • @binaryhowl there are a few options, i.e. "201105071130-614", or some combination thereof (I would format the date/time as above because you could later sort on it - if you wanted to). This would be a natural key. A surrogate key would be independent of the data. So, for example, you could have an `id` column of type identity, and an `orderid` column of type varchar. One natural key, or a natural+surrogate - both solve the problem, that's when it comes down to use, interpretation, and preference.. – Bobby D May 08 '11 at 04:02
  • Way... too... awesome. This solution helps me keep my HiLo in place as well having a surrogate key my customers could easily understand, *and* which protects my other entries as well (e.g., no more "Oh let's see what ?Id=200 gives me!"). Thanks! :'D – rebelliard May 08 '11 at 04:09