Skip to main content
    Country/region select      Terms of use
     Home      Products      Services & solutions      Support & downloads      My account     

developerWorks  >  Lotus  >  Forums & community  >  Best Practice Makes Perfect

Best Practice Makes Perfect

A collaboration with Domino developers about how to do it and how to get it right in Domino

I'm working on an article which includes and expands on the performance basics I blogged about earlier, and I want to have something to tell people who think it's a good idea to generate a sequential document identifier by using @DbColumn and adding one to the last value. The problem is, I can't find a good overview of all the alternatives -- just articles about specific techniques (e.g. Generating sequential numbers in replicated applications). Does anyone have a reference to an article that goes into, does your number really have to be sequential or just unique, and going over the options, pro and con, with references to articles such as the above describing specific techniques.

Any and all decent links about sequential/unique IDs appreciated, even if they aren't the overview I'm looking for, in case I need to write that overview myself...


Andre Guirard | 28 February 2008 10:37:09 AM ET | Man-cave, Plymouth, MN, USA | Comments (8)


1) Need reference for sequential numbering
Turtle | 2/28/2008 12:20:24 PM

Straight from Ken Yee's Notes FAQ:

{ Link }

2) Need reference for sequential numbering
Maria Helm | 2/28/2008 12:30:30 PM

I remember some pretty big conversations about this back in R4/R5 days. I know it went into pros/cons/caveats/alternatives.

Search back at the R4/5 Forum:

Notes-Domino 4 and 5 Forum : { Link }

Sorry I don't have a specific link for you.

3) My implementation
Scott Jenkins | 2/28/2008 12:56:56 PM

I cannot give you a reference to any article, but I can tell you what I did after researching this issue about 6 months ago, and coming up with very little other than commercial solutions.

I implemented a central database which has profile-type documents for each Sequence of numbers which need to be generated. It has a simple web agent which returns the next value in a sequence, and updates the master sequence document.

My standard code library has a wrapper procedure to use in the calling db which uses an ajax-type (its actually plain text, but you get the idea) web call to get the next sequence number.

This provides a single point of maintenance, eliminates and through some DNS games, is very easy to fail over to another server if necessary--especially if the two replicas are clustered.

This solution has been very robust in my organization.

4) Need reference for sequential numbering
Scott Jenkins | 2/28/2008 12:58:01 PM

The previous comment should have said "eliminates the possibility of two replicas being out of sync, since only one is ever used for updates"

5) Need reference for sequential numbering
Nathan T. Freeman | 2/28/2008 3:51:48 PM

6) Need reference for sequential numbering
James Sjostedt | 2/29/2008 6:09:48 AM

In TheView March/April 1996 Volume 2, Issue 2

and a couple of samples in Lotus Sandbox

7) Need reference for sequential numbering
Kerr | 2/29/2008 7:41:41 AM

This is an interesting topic. The main thing to understand is where the requirement is actually coming from. In cases where the requirement is for a sequential number incrementing by 1 with no gaps it is almost always a misguided idea from the client/customer based on some notion of "how things should be", but is completely unnecessary from the point of view of designing and building your domino app. If the client can't be dissuaded from having to have this then it must not interfere with the actual technical design using another, easier to maintain, possibly hidden, id.

8) Need reference for sequential numbering
Pejman | 3/3/2008 12:10:23 PM

You can also achieve that using the new relational capabilities :







9) Not always a dud
Morten Clausen | 3/3/2008 3:47:46 PM

Another option is to use a RunOnServer-agent with CodeLock-CodeUnlock pairs around the number creating code and CreateLock/DestroyLock using the UNID of the document with the number (which also allows parallel access to different sequences if need be). It works, I've done it, and it's not even that hard.

However, RunOnServer has some truly horrendous scaling problems so it's not really recommendable. In my experience several seconds per call with peaks up to minutes is not uncommon in a midsize installation with a lot of trafic - 2000 users, 30000 invocations per day, 1 parameter document per invocation. As an aside it creates a bottleneck and a single point of failure as all this HAS to be handled by a single server. Not ideal, but locking means someone has to take responsibility as a transaction manager so it's kinda inevitable.

As for unique numbers - create a document in a central database and use the UNID. Best thing I've ever used and it's even cluster safe. Never seen a miss yet. @Unique can miss, especially with agent signed by a central ID drawing numbers simultaneously as it's not that fine-grained. But UNID has to work so it does. :-)

10) re: Need reference for sequential numbering
Andre Guirard | 3/3/2008 9:42:28 PM

Morton, this is an interesting idea and I'll follow it up at greater length. One caution about the UNID, though; I believe it includes the database ID (not the same as the replica ID) which is supposed to be unique per NSF but if the file was file-copied, might not be.

Scott Jenkins' solution is similar in concept.

Pejman, Since Notes is not a relational database, I'm not sure how one would apply your suggestion. If you mean when using NSFDB2, I guess you might be able to add such a column to a DAV, but how would it end up being a usable field in Notes?

Kerr, I'm not sure I understand how it makes sense to go to all the trouble of generating a unique sequential ID because the customer insists on it, and then create a second unique ID, in addition to the UNID Notes already assigns. There's nothing wrong with a sequential ID -- you can still use it for whatever purpose -- it's just generally more trouble than it's worth.

11) Need reference for sequential numbering
Andy Broyles | 3/4/2008 10:33:33 AM

In my experience, once you get past the 'its the way we have always done it' mentality, the next reason for this requirement is 'our auditors like it that way' and this one is one you can't ignore as being valid.

I have typically used a server agent to assign sequential numbers from a SQL based datastore (I used MySQL frequently, or a Jet database on Win32 platforms.) The other alternatives are either too cumbersome or lack surety.

12) Re: Methods of lower surety
Andre Guirard | 3/4/2008 11:17:32 AM


Really, you don't trust something like what I just posted here, that uses NotesDocument to check for conflicts?

13) Need reference for sequential numbering
Nathan T. Freeman | 3/4/2008 11:27:57 AM

"the next reason for this requirement is 'our auditors like it that way' and this one is one you can't ignore as being valid."

Sure I can. "Tell the auditors to talk to me. I'd like them to explain why it is necessary to add 30% more code to a system that serves no functional purpose other than they can count items on their fingers."

And auditor is just another kind of user. Often the auditor has LESS knowledge than the user. Certainly any auditor that says "record IDs should be numeric, starting at one and increasing in constant increments of one, and must be available at initial creation time of said record," has no understanding of data systems and such simple concepts as an "index."

14) Need reference for sequential numbering
Scott Jenkins | 3/4/2008 1:26:57 PM

Of course, you -can- explain to auditors or any other user why sequential numbers are not required.

The question is if the user is willing enough to listen, you have the time for the political battle, and enough influence to prevail when its all said and done--all assuming its not a paying customer in the first place.

Many times, as in the case of my own organization, it is actually more feasible to implement a centralized serial number issuing service than it is to overcome the organizational inertia.

15) Need reference for sequential numbering
Andy Broyles | 3/4/2008 2:15:34 PM

Your auditors clearly aren't as nice as our auditors.

We actually have three sets: first our 'normal' auditors who look over our books annually..they are the easy ones. We can work with these folks pretty easily.

Next we have SAS70 auditors...they are the people who crawl up our asses and check every nook and cranny of the 'process'...while they are through, they too can be swayed with logic.

Next however, are the state auditors. Every three years we must undergo an expensive and horrible audit by our state regulators...these folks are not easy to work with at all.

Regardless of the level, it more comes down to a choice of the battles you want to fight. Pissing off an auditor over something as 'trivial' as sequential numbering may have very adverse affects on the wording of the executive letter that prefaces the audit results...the worse possible outcome when you have to be the one explaining to your board of directors why you chose to piss off the auditors.

16) Need reference for sequential numbering
Andy Broyles | 3/4/2008 2:18:45 PM

Whoops, flip that first statement...your auditors must be much nicer (more reasonable?) than our auditors.

17) Why you may sometimes need a third unique identifier
Rupert Clayton | 3/4/2008 4:34:55 PM

@10) Andre: I think there are some circumstances where it's reasonable to have a unique ID separate from both the UNID and the "serial number" of the customer's mindset.

This came up when coding a project-tracking application for an organisation that had previously mostly used paper files. Projects already had serial numbers, and in fact many offices used the manual process of issuing serial numbers as a way to control authorisation of project spending. There was no way they were going to stop using their project numbers. And there was some sense here: There were already years of project drawings and correspondence files organised by project number.

Also, we needed the flexibility to restore (read "cut-and-paste") documents from backups to remedy user error ("I didn't think that 'delete' really meant delete." And this was despite removing delete access from as many people as possible.) So referencing documents by UNID would be setting ourselves up to fail, as the pasted documents would have changed UNIDs.

As far as I remember, @Unique worked just fine for linking projects to timesheets to assesment forms, and so on.

You might think we could have used the serial project number to link documents. But sites couldn't agree at which point in the process the project number should be issued. We just allowed them to add it whenever they wanted, and then looped through all the "linked" documents (with the same hidden @Unique-generated value) to add it to those, too.

Overall the project number had too much meaning to the users, and the UNID had too much meaning to Domino, for us to want to hitch our application logic to either one.

18) Need reference for sequential numbering
Pejman | 3/5/2008 12:28:19 PM

@10 : I don't find my db2/nsf database anymore. But of course I was using a db2 enabled database to get the sequential number. If you are really interested by this method, I can search again in my all backups.

In the relational world this requirement is quite trivial to achieve but in Domino world not easy. I generally use @right(@unique;"-"). It's short and readable.

19) Need reference for sequential numbering
Kerr | 3/6/2008 6:20:12 AM

@10, It comes down to separating the requirements. From my point of view the user requirement for a sequential single incrementing id is orthogonal from the unique id I need to make my app work.

The user might have all sorts of wild ass requirements for their id. You could try and reuse it, but then you may run into conflicts between your internal requirements and the users. The classic example is that you need the unique id on creation, but the user doesn't need the sequence id to be fixed until a later point.

You might end up doing a little more work, but the savings made in flexibility of ongoing development can be huge since you are dealing with separate problems.

 Add a Comment
Comment:  (No HTML - Links will be converted if prefixed http://)
Remember Me?     Cancel

Search this blog 


    About IBM Privacy Contact