How to implement an efficient database driven ticket/protocol control system?

Question

Scenario: WAMP server, InnoDB Table, auto-increment Unique ID field [INT(10)], 100+ concurrent SQL requests. VB.Net should also be used if needed.

My database has an auto-increment field wich is used to generate a unique ticket/protocol number for each new information stored (service order).

The issue is that this number must be reseted each year. I.e. it starts at 000001/12 on 01/01/2012 00:00:00 up to maximum 999999/12 and then at 01/01/2013 00:00:00 it must start over again to 000001/13.

Obviously it should easly acomplished using some type of algorithm, but, I'm trying to find a more efficient way to do that. Consider:

1. It must (?) use auto-increment since the database has some concurrency (+100).

2. Each ticket must be unique. So 000001 on 2012 is not equal to 000001 on 2013.

3. It must be automatic. (no human interaction needed to make the reset, or whatever)

4. It should be reasonably efficient. (a watch program should check the database daily (?) but it seems not the best solution since it will 'fail' 364 times to have success only once).

The 'best' approach I could think of is to store the ticket number using year, such as:

12000001 - 12999999 (it never should reach the 999.999, anyway)

and then an watch program should set the auto increment field to 13000000 at 01/01/2013.

Any Suggestions?

PS: Thanks for reading... ;)

Answer 1

So, for futher reference I've adopted the following sollution:

I do create n tables on the database (one for each year) with only one auto-increment field wich is responsible to generate the each year unique id.

So, new inserts are done into the corresponding table considering the event date. After that the algorithm takes the last_inseted_id() an store that value into the main table using the format 000001/12. (ticket/year)

That because each year must have it's own counter since an 2012 event would be inserted even when the current date is already 2013.

That way events should be retroactive, no reset is needed and it's simple to implement.

Sample code for insertion:

$eventdate="2012-11-30";
$eventyear="2012";
$sql = "INSERT INTO tbl$eventyear VALUES (NULL)";
mysql_query ($sql);
$sql = "LAST_INSERT_ID()";
$row = mysql_fetch_assoc(mysql_query($sql));
$eventID = $row(0)
$sql = "INSERT INTO tblMain VALUES ('$eventID/$eventYear', ... ";
mysql_query($sql)

Answer 2

You can set up a combined PRIMARY KEY for both of the two columns ID and YEAR; this way you would only have one ID per year.

MySQL:

CREATE TABLE IF NOT EXISTS `ticket` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `year` YEAR NOT NULL DEFAULT '2012',
    `data` TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (`id`, `year`)
)
ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci

UPDATE: (to the comment from @Paulo Bueno) How to reset the auto-increment-value could be found in the MySQL documentation: mysql> ALTER TABLE ticket AUTO_INCREMENT = 1;. If you also increase the default value of the year-column when resetting the auto-increment-value, you 'll have a continuous two-column primary key. But I think you still need some sort of trigger-program to execute the reset. Maybe a yearly cron-job, which is launching a batch-script to do so on each first of January.

UPDATE 2: OK, I've tested that right now and one can not set the auto-increment-value to a number lower than any existing ID in that specific column. My mistake – I thought it would work on combined primary keys…

INSERT INTO `ticket` (`id`, `year`, `data`) VALUES
    (NULL , '2012', 'dtg htg het'),
    -- some more rows in 2012
);

-- this works of course
ALTER TABLE `ticket` CHANGE `year` `year` YEAR( 4 ) NOT NULL DEFAULT '2013';

-- this does not reset the auto-increment
ALTER TABLE `ticket` AUTO_INCREMENT = 1;

INSERT INTO `ticket` (`id`, `year`, `data`) VALUES
    (NULL , '2013', 'sadfadf asdf a'),
    -- some more rows in 2013
);
-- this will result in continously counted ID's

UPDATE 3: The MySQL-documentation page has a working example, which uses grouped primary keys on MyISAM table. They are using a table similar to the one above, but with reversed column-order, because one must not have auto-increment as first column. It seems this works only using MyISAM instead of InnoDB. If MyISAM still fits your needs, you don't need to reset the ID, but merely increase the year and still have a result as the one you've questioned for.

See: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html (second example, after "For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index.")

Answer 3

MongoDB uses something very similar to this that encodes the date, process id and host that generated an id along with some random entropy to create UUIDs. Not something that fulfills your requirement of monotonic increase, but something interesting to look at for some ideas on approach.

If I were implementing it, I would create a simple ID broker server that would perform the logic processing on date and create a unique slug for the id like you described. As long as you know how it's constructed, you have native MySql equivalents to get your sorting/grouping working, and the representation serializes gracefully this will work. Something with a UTC datestring and then a monotonic serial appended as a string.

Twitter had some interesting insights into custom index design here as they implemented their custom ID server Snowflake.

The idea of a broker endpoint that generates UUIDs that are not just simple integers, but also contain some business logic is becoming more and more widespread.