On Sun, Jul 6, 2008 at 7:33 PM, Berend Tober <btober@xxxxxxxxxxxxxxxx> wrote: > Jack Brown wrote: >> >> Dear list, >> >> I need some tips and/or pointers to relevant documentation implementing >> (what I chose to call) "a perfect sequence" i.e. a sequence that has no >> missing numbers in the sequence. I'd like it to auto increment on insert, >> and auto decrement everything bigger than its value on delete. There are >> many mechanisms (rules, triggers, sequences, locks etc.) but I'm not sure >> which combination would result in the most elegant implementation. >> >> Oh, and if you know the right term for what I just described, I'd be more >> than pleased to hear it! :-) >> > > This question comes up a lot. A term used in prior discussions is "gapless > sequence". > > What would be really more interesting for discussion on this community forum > is a detailed description or your actual use case and requirements. I will say that if you need a gapless serial numbering system it's still better to NOT try and do it with a pre-checked out number. For instance, you might have a system like a court document system that might have this requirement, that you hace CR-1 through CR-99999999 or whatever. In that case it's better to let the user start work, then hit CREATE DOCUMENT when they're ready. Then your business logic can put the data into the database, and if it goes in, then check out a number from the sequence. I.e. there are no deletes, only failed inserts. A system that requires you to show a number before the document has been "created" in the system but wants no gaps is flawed. Don't give them a number until they HAVE a document. reusing numbers already shown to a user is a recipe for a disaster. they write down the number, and two weeks later reference it, but it's not there. That's one use case. It's important here to look for the way that is less likely to lead to "oh crap!" moments. Adding gapless sequences increases the complexity. Better to let the complexity only live in a display layer of sorts than to rely on it for FK-PK type stuff. If there's any FK->PK relations involving these keys and they aren't fully cascaded, then allowing them to be renumbered is courting disaster. If you use a separate table for "user visible sequence number" and store the plain sequence, gaps and all in the db, then your actual core data is safer. You can recreate the user visible sequence number table without affecting the actual relationship of the data in the real data table. I hope I'm not rambling too much.
