MySQL unique numbers that reset every year

[MrDudeMan]

The project numbers for our internal tracking system are of this format:

YY-XXXX-NX

YY = the last two digits of the year
XXXX = incrementing number from 0000 to 9999
NX = job type (P, L, N, D, etc.) + job number (0-9)

The entire thing is unique, but you won't know the NX part until sometime later, so it actually isn't important because the first 6 digits are also unique. However, the XXXX part needs to reset every year. Normally I do this with an autoincrement field because then it's easy: insert in a transaction and read the id back out to guarantee the intended id is returned. Can I reset the column on the first insert every year? Is there a better way to do this?

Ultimately, this is what I want:

ProjectNumber = GetProjectNumber()

Where project number is a string that is always unique and resets at the beginning of each year.

 

[MrDudeMan]

Maybe I could use another table simply for autoincrement, which can be reset every year. I wasn't planning on having a config table, but maybe I could do that.

config_table
------
year
<other configs>

xxxx_table
------
id int unsigned not null autoincrement

if year > year in config_table:
     truncate xxxx_table
     
insert/get new xxxx value from xxxx_table
insert yy-xxxx into project_number table

 

[uclabachelor]

You can try to mash all that into sql and let it do it for you.. or handle it on the application level, which is probably the route I'd take if I were implementing it simply because there is there is one too many logic involved.

 

[DaveSimmons]

If you have a table with one row per year you could have a count field initially set to zero

update row 2014 set count = count +1

Then use the new count+1 as the xxxx

You'd want to lock the table row / use a transaction if there's any chance of a race condition between 2 new projects

 

[MrDudeMan]

If you have a table with one row per year you could have a count field initially set to zero

update row 2014 set count = count +1

Then use the new count+1 as the xxxx

You'd want to lock the table row / use a transaction if there's any chance of a race condition between 2 new projects

This is a good idea. I don't know how to lock table rows from C#, but I'm assuming it's not that hard. Race conditions are very likely to happen, which is why I've been struggling to find a simple, easy to implement solution.

 

[Leros]

Manage the per-year auto-increment yourself.

Keep a table that something like:

CREATE TABLE `project_id` (
  `year` int(11) NOT NULL,
  `counter` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

You need to fetch and update the counter in the same transaction as where you create the project. This is so you don't increment the counter if the project creation fails. Also, you need to lock the counter row when you fetch the current counter so that another query can't update it before you're done with it.

It would look something like this:

START TRANSACTION;
SELECT counter FROM project_id WHERE year = ? FOR UPDATE; -- this locks the row 
-- use the counter value to create the new project
UPDATE project_id SET counter = counter + 1 where year = ;
COMMIT;

I'm missing logic for inserting the row at the start of the year, but that's pretty simple to add.

 

[MrDudeMan]

Thanks. That looks like a good solution.