Protocol for documents

Approach with MyISAM

Prerequisites:

• Have a composite primary key.

• The table engine should be MyISAM.

Disadvantages:

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

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

An interesting solution that solves this problem with only the database is to create a compound key with auto increment. An important detail in the other one to create the table the auto increment field should be the last . In this way the number only increments based on the value of the previous column (year in the case) for each record inserted as the equal value (2017) the auto increment rises, the year 2018 will turn 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

Relates to:

How does auto increment work with composite keys in the MyISAM engine?

I do not understand your logic very well, but to implement without having to spend all the time returning the current year, or scrolling through the table to know the protocol index, you can create a table in the database and store the current year and the current protocol index.

Creates a trigger that whenever a new protocol is generated updates the index and a function in the database that, when giving 00:00, checks the year, and if it is different from the current one stored in the database, then replace the year and clear the index.

Another approach using only the database would be to look for the highest protocol value within the current year and add one more. Here is 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...
    );