1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Prevent Duplicate Document Number

Discussion in 'MySQL' started by Lee M. Childress, Jul 25, 2019.

0
  1. #1
    I am working on a helpdesk ticketing system. When a user submits a ticket I want to assign the ticket a unique number, for example: 20190724001 where the first four digits is the year, next two are the month, next two the day and the last three would be the next consecutive number FOR THAT DAY and then the numbering would start back at 1 the next day. I know to query the table for the next number where the date equals today, but I do I obtain the next consecutive number without another user grabbing the number at the same time? Odds are slim, but I want to plan for the worst.
     
    Lee M. Childress, Jul 25, 2019 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    Don't confuse ticket numbers with id numbers.

    While, technically, you could use your ticket number as your primary key I wouldn't.

    So, when you save a new record, ticket number (char 11?) would be a stored procedure that gets the highest ticket number that is LIKE '20190724%' adds 1 and returns it back as a string.

    Alternatively, ticket number could be 2 columns - one for the date, one for the day's counter and when someone searches on the ticket number your script breaks them apart and searches on the two bits.
     
    sarahk, Jul 25, 2019 IP
  3. SpacePhoenix

    SpacePhoenix Well-Known Member

    Messages:
    196
    Likes Received:
    28
    Best Answers:
    2
    Trophy Points:
    155
    #3
    Wouldn't you be better off using an numeric auto-number field? If you're basing the primary key on the date, what happens if the current server date and time get changed to an earlier time for whatever reason, you risk duplicate ticket numbers, unless you use transactions. What about during the twice yearly change to/from daylight savings time, you could end up with it looking like an entry was submitted on one day but in reality it might have been submitted either the day before or the day after.

    For usability you might want to have it so that the user types in the ticket number with the digits in groups (eg 1234-5678-9012) and then when the ticket number is submitted to the server, have the server strip out the - before doing something with the ticket number.

    If the date is important then you should use UTC and then to the user either display all times in UTC or display the times with whatever off-set is appropriate for their timezone.

    btw don't forget that some countries write dates with the month then the day and others with the day then the month
     
    SpacePhoenix, Jul 25, 2019 IP
  4. mmerlinn

    mmerlinn Prominent Member

    Messages:
    3,197
    Likes Received:
    818
    Best Answers:
    7
    Trophy Points:
    320
    #4
    I am assuming that you have a separate table for tracking which numbers have been used or not. If so, there are two ways to prevent duplicate numbers.

    1) If you can LOCK the table, then LOCK the table, update table with next number which you also grab, then UNLOCK the table. During the time the table is LOCKed, no one else can grab a number.

    2) If you cannot LOCK the table, then add the next number. Check for duplicate number(s) added at the same time. If no duplicate, then grab your number. If there are duplicates, delete only the excessive duplicates and try again until you get your unique number. Just make SURE you do not delete ALL numbers that were duplicates as ONE of them someone else has grabbed for their unique number.
     
    mmerlinn, Jul 25, 2019 IP