Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2003
    Posts
    12

    Generating record IDs without gaps

    Currently I need to control the generation of IDs (primary key for a table). Thus, I cannot use IDENTITY autoincrement columns. The most stringent requirement is that the IDs should not have gaps since it will be printed on receipts.

    I'm sure people have encountered this problem before. Any ideas or comments on how to design this ? Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Sequence numbers printed on receipts serve a completely different purpose than id numbers used in a database. Confusing the two is an invitation to problems.

    Sequence numbers used on printed forms exist to make it possible to prove that the forms were used or accounted for. This is a basic procedure used to prevent clerks from issuing a receipt to a customer and then pocketing the proceeds without filing the receipt with the business. By having the receipt forms pre-numbered, and being able to account for all of the forms, the business owner can ensure that none of the receipt forms are un-accounted for (and therefore that none of the forms could have been mis-used).

    Sequence numbers (pre-printed or computer printed) have NOTHING to do with a well managed database. While your software might be willing to record the pre-printed serial number on a form issued to a customer, the whole concept is bad because the computer ought to generate the forms, so the pre-printing becomes meaningless.

    What you are trying to do is expressly forbidden as an accounting control for computer printed forms under GAAP. Any competent auditor should issue a negative notation on every audit of a system that relies on computer generated serial numbers that are used this way.

    My advice is to see an accountant or an auditor before you spend any time or money to implement a "feature" that they should strongly discourage.

    -PatP

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Well, I'm no accountant so I'll address the technical issues:

    "Gaps" in auto increment columns happen when an update fails, or if a row is deleted. Since the whole point of receipts is that they are permanent, you shouldn't be deleting anything. Update failure happens like this:

    1. DBMS sees you're inserting a new row.
    2. DBMS increments the auto_increment, and gives you a number.
    3. Update fails.
    4. DBMS doesn't reset the auto_increment.

    One technique would be to use the auto_id column as a tentative receipt number. A scheduled task would periodically lock down the table and issue out permanent receipt numbers, thus ensuring that numbers are only issued to successfully inserted records. Also, in line with Pat's advice, the scheduled task could have a premade list of receipt numbers to draw on.

  4. #4
    Join Date
    Nov 2003
    Posts
    12
    Thanks Pat for the legal point of view.

    sco08y, one way to solve the problems of gaps (in my opinion) is to implement a sort of pushback of IDs.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Another solution would be to simply renumber them to remove any gaps. This can be done with a single update statement, and it is a lot easier than doing the dance needed to support "pushback" to fill the gaps.

    -PatP

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Pat: yeah, I thought of the pushback and renumbering methods, but in both of them you either occasionally have a gap or have to change IDs. With the method I suggested, if you do "select * from table where permanent_ID is not null" you're guaranteed to see gapless IDs and the IDs will never be renumbered. In addition, if you forced the permanent IDs to be updated at the beginning of your transaction you don't have to use the where clause.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Sorry, I should have used a smiley... The suggestion about renumbering was a joke. I guess that I've worked around the AICPA folks too long, that one seemed obvious to me, but in retrospect it wouldn't be funny to anyone except an accountant.

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    it wouldn't be funny to anyone except an accountant.
    ergo not funny at all
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Watch me get hammered

    Pat's comments may well be correct. But there are people out there who have very good reasons for having next-sequential-numbers as part or all of their PK, and are not subject to GAAP. Most respondents here have already disagreed with me re the subject in previous threads, but the original poster has a question which has not been answered.
    - do not use IDENTITY feature, it is a dogs breakfast. Especially if you consider the Child tables (where the said PK is an FK), which most of us have. Severely impedes data migration (of the table and its children) which is a requirement once you have prod/test/dev environments.
    - do use some sort of method of distributing the key over the table (eg. InvoiceId [PK] is CustomerId plus InvoiceNo; InvoiceNo is simply generated in your app code by (of course, in a transaction):
    select max(InvoiceNo) where CustomerId = @CustomerId, plus one
    Before any screams, most DB vendors have enhanced the MAX() so that it is no longer a problem. Of course, you have to use optimistic locking, and do this as the last (NOT first) item in the tran.
    - if you cannot get the user to agree, then the other alternative is a carefully designed (physical features to ensure small locking duration) Control table which contains a row which hold the LastInvoiceIdUsed. Ugly but not uncommon.
    - no gaps. period. Unless your transaction design is out to lunch.
    - I do not agree with background or after-the-fact changes because the kind of people who want next-sequential-no want one that does not change
    There are other High performance methods but they are not for publication.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Again...
    There is absolutely nothing wrong with using Identity values (at least as far as SQL Server goes...I can't speak for all databases, so maybe Sybase sucks with identies, I don't know...).
    Identities do not create performance issues.
    Identities do not create any more data migration headaches than other non-GUID surrogate keys.
    Identity columns are preferable to the old-style "GetMaxID+1" method, which is a coding style that is 10 years out of date. The only time you see the GetMaxID+1 method these days is in legacy systems.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm curious Derek, I've seen many folks specify that they wanted sequential numbers, and I've had a couple of cases where the people paying the bills told me that they wanted sequential numbers and that is all that they'd pay for, but I've never seen any logically correct argument for sequence. There are certainly lots of good arguments for uniqueness, but I've never heard one for serialization.

    I don't know of any method for generating sequence numbers that works any better than the IDENTITY property. Every method I know of can produce gaps in a multi-user, transactional system because those gaps are actually part of the definition of the problem (because of the multi-user and transactional specifications). Unless you redefine the problem that you're solving, I don't believe that there is any solution for it.

    -PatP

  12. #12
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by Pat Phelan
    I'm curious Derek, I've seen many folks specify that they wanted sequential numbers, and I've had a couple of cases where the people paying the bills told me that they wanted sequential numbers and that is all that they'd pay for, but I've never seen any logically correct argument for sequence. There are certainly lots of good arguments for uniqueness, but I've never heard one for serialization.
    IMO, this is just a holdover from the days of paper records. It's a "We have Always Done it This Way" argument. (which I run into all the time at my company - usually, I can get the requestor to see reason. )
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would like to know what happens when you insert rows 1,2,3,4,5,6,7,8,9,10

    And then you delete 3 and 5

    What do you want to have happen?

    Reassign 3 and 5?

    What about children tables, do you want everything to cascade delete

    And what if you export data to som other system...

    3 was once an apple, now it's an orange?

    Makes no sense and seems dangerous
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Brett Kaiser
    I would like to know what happens when you insert rows 1,2,3,4,5,6,7,8,9,10

    And then you delete 3 and 5

    What do you want to have happen?

    Reassign 3 and 5?
    To be fair, in an accounting application records should not be deleted anyway. Instead, an offsetting transaction should be created.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    To be fair, in an accounting application records should not be deleted anyway. Instead, an offsetting transaction should be created.
    So true, and a reason why, and who did it and when
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •