Document protocol

Question

I am making a program in php, mysql and would like to generate a protocol with some argument.

The protocol would be generated automatically in the system, without interference from the user, but it would have to be the following standards:

Numbering: With each new registration he would have to increase the value and not repeat, following the following logic:

0001/ano --> 0001/2017
0002/ano --> 0002/2017

Another year would have to turn to 0001 again:

0001/ano --> 0001/2018
0002/ano --> 0002/2018

How could I implement this in the system?

I was thinking of taking the current year of the last part of the protocol, example 2017, and if it did not return any lines it would start the protocol counter from the 0001, as soon as it was recorded the 2017 the counter would already have a line and it would continue the logical sequence, but how will I catch the next number?

Answer 1

Approach with Myisam

Prerequisites:

• Have a composite primary key.

• Table engine must be Myisam.

Disadvantages:

• If the table has any foreign key the referential integrity should be implemented via application.

• You can add a bit of complexity in updates, queries and queries, as the auto increment field will not be enough to identify the line. Ex: an update based on id will update all records with value 30 for example every year (2015, 2016, 2017 etc.)

An interesting solution that solves this problem only with the database is to create a composite key with auto increment. A detail important on the other of creating the table the auto increment field should be the last. Thus the number only increases based on the value of the previous column (year in case) for each record inserted as the equal value (2017) the auto increment increases, turn the year (2018) the next value of the auto increment will be 1.

Table example:

CREATE TABLE `atendimentos` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `ano` int(11) NOT NULL,
   `atendente` varchar(45) DEFAULT NULL,
   PRIMARY KEY (`ano`,`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8
COMMENT='Perceba bem a ordem dos campos da chave primária.'

Inserts:

INSERT INTO atendimentos (ano, atendente) VALUES(2017, 'Fulano'); //id: 1
INSERT INTO atendimentos (ano, atendente) VALUES(2017, 'Ciclano'); //id: 2
INSERT INTO atendimentos (ano, atendente) VALUES(2017, 'Beltrano'); //id: 3
INSERT INTO atendimentos (ano, atendente) VALUES(2018, 'Novo'); //id: 1
INSERT INTO atendimentos (ano, atendente) VALUES(2019, 'Beltrano'); //id: 1

Relating:

How auto increment works with composite keys in Myisam engine?

Answer 2

I didn’t understand your logic very well, but to implement without having to spend all the time returning the current year, or going through the table to know the protocol index, you can create a table in the bank and store the current year and the current protocol index.

Create a Rigger that whenever a new protocol is generated updates the index and a function in the bank that when giving 00:00 check the year, and if it is different from the current one stored in the bank then replace the year and reset the index.

Answer 3

Another approach using only the database, would be to look for the highest protocol value within the current year and add one more. Follow an example:

INSERT tabela
    (
        protocolo,
        ...campos...
    )
VALUES
    (
        (
            SELECT
                CONCAT(MAX(CONVERT(REPLACE('0001/2017', CONCAT('/', CONVERT(YEAR(NOW()), CHAR(4))), ''), UNSIGNED)) + 1, '/' + CONVERT(YEAR(NOW()), CHAR(4)))
            FROM
                tabela AS x
            WHERE
                YEAR(x.data) = YEAR(NOW())
        ),
        ...campos...
    );