Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unique Sequential Record Numbering

Status
Not open for further replies.

apkohn

Technical User
Nov 27, 2000
62
AU
I am currently developing two database, one in Ms Access and the other in Lotus Notes. This question I belive should be independent of language though.

The two database are both multi-site, with replication occuring every hour (or so). Users at each site are able to create records, one of which has a field that needs to be sequential and unique. That is the database records Action Items across multiple sites, and each AI must have a unique sequential number (this is in additon to a hidden primary key, that is set as a replication ID).

What is the best way to achieve this. The only method I have thought of that would enable this, is to have this number calculated on the fly, based on a date/time field, that records the time the record was created. Thus, I would count all the records created before the current one, add 1, and get the unique sequential number. The only problem is that this is really slow.

Can anyone else suggest how else this may be done?
 
The day your clock is not showing the right time, you are in problems, remember the w2k? use an unique numbering system, a generator it is called in Oracle, Sql-server enfim the real systems.

A "poormans" generator should be a number/counter stored in a table, when a new record is created, it uses this number, increases it, and stores it.

Regards
S. van Els
SAvanEls@cq-link.sr
 
The problem with having a number/counter in a table is concurrancy, transaction handling with that table is essential and can slow the system. I would suggest using calculating a value based on date/time just for indexing. If, for some reason, the value calculated is already in table, its easy to calculate a new one and insert. Then add a field with replication-date that is set when you replicate the row.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top