CODEWITHSUNDEEP
mysqlsqldatabasecounternosql
josegomezr
josegomezr

Reputation: 916

DB Complex Incremental Counters

A little background for the issue:

In Venezuela, there is a law that defines how an special document called Withholding Receipt (issued when a company, designated by the Tax Administration, withheld taxes to be declared by the company and not the client, really confusing legal thing) will be identified and the information it will present. It says that it have to be numbered with the following format:

YYYYMMXXXXXXXX

Where YYYY represents the year, MM the month and XXXXXXXX represents an incremental number (up to 8 digits wide) that will be refreshed (start from 0 again) if overflowed.

I could've used a plain vanilla AUTO_INCREMENT field in order to solve this puzzle, however, the real issue begins here.

According to the Agents of the Tax Administration, the incremental numbering refreshes automatically each month, meaning Receipt No. 20151200000001 and No. 20160100000001 can exist on the database and dont collide.

This means, it makes impossible to use an AUTO_INCREMENT field since its value will be resetted to 0 each month.

What options can be used to solve this puzzle? Using of course, database features only.

PS: Can be in any database (including No-SQL).
PS2: year and month can different be fields on the table/document/entity.

Edit

I did some research on MySQL based on @Gordon Linoff answer, here is a working example

CREATE TABLE IF NOT EXISTS test (
    id int(11) NOT NULL AUTO_INCREMENT,
    invoice_no varchar(12) NOT NULL,
    year int(11) NOT NULL,
    month int(11) NOT NULL,
    identifier int(11) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;


DELIMITER //
CREATE TRIGGER ins_tr BEFORE INSERT ON test
FOR EACH ROW BEGIN 
    SET @maxID = (SELECT COALESCE(MAX(identifier), 0)
        FROM test 
        WHERE CONCAT(year, lpad(month, 2, '0')) = CONCAT(NEW.year, lpad(NEW.month, 2, '0'))
    );
    SET NEW.identifier = @maxID +1;
END
//
DELIMITER ;

INSERT INTO test (invoice_no, year, month) VALUES (1, 2015, 12), (2, 2015, 12), (3, 2016, 1), (4, 2016, 1);

Result:

+----+------------+------+-------+------------+
| id | invoice_no | year | month | identifier |
+----+------------+------+-------+------------+
|  1 | 1          | 2015 |    12 |          1 |
|  2 | 2          | 2015 |    12 |          2 |
|  3 | 3          | 2016 |     1 |          1 |
|  4 | 4          | 2016 |     1 |          2 |
+----+------------+------+-------+------------+

In researching on a way for MongoDB or any NoSQL engine.

Upvotes: 1

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You would implement this in a relational database using a trigger. The trigger would implement logic such as:

select new.TaxReceiptNumber := concat(date_format(curdate(), '%Y%m',
                                      lpad(coalesce(max(right(TaxReceiptNumber, 8) + 0), 0), 8, '0')
from t
where left(TaxReceiptNumber, 6) = date_format(curdate(), '%Y%m');

I might be tempted to store the incremental number and date of the receipt in different columns. However, given that you have to work with tax authorities, it might be better to just have the number as a single column.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

First, don't make this your primary key.

Second, store the current number somewhere. When you use it, increment it by 1.

Finally, on the first of the month or when it reaches a certain value, reset it to 1.

Upvotes: 0

Related Questions