How to create a smart key, which would reset its auto increment value at the end of the month

Question

I am currently working on a project for the management of oil distribution, and i need the receipts of every bill to get stored in a database. I am thinking of building a smart key for the receipts which will contain the first 2 letters of the city, the gas station id, the auto increment number, first letter of the month and the last 2 digits of the year. So it will be somewhat like this: "AA-3-0001-J15". What i am wondering is how to make the AI number to go back at 0001 when the month changes. Any suggestions?

Answer 1

Do you want to store all that in one column? That sounds to me like a composite key over four columns...

Which could look like the following:

CREATE TABLE receipts (
  CityCode VARCHAR2(2),
  GasStationId NUMERIC,
  AutoKey NUMERIC,
  MonthCode VARCHAR2(2),
  PRIMARY KEY (CityCode, GasStationId, AutoKey, MonthCode)
);

Which DBMS are you using? (MySQL, MSSQL, PostgreSQL, ...?)

If it's MySQL you could have a batch-job which runs on the month's first which executes:

 ALTER TABLE tablename AUTO_INCREMENT = 1

But that logic would be on application layer instead of DB-layer...

Answer 2

To answer the direct question - how to make the number restart at 1 at the beginning of the month.

Since it is not a simple IDENTITY column, you'll have to implement this functionality yourself.

To generate such complex value you'll have to write a user-defined function or a stored procedure. Each time you need a new value of your key to insert a new row in the table you'll call this function or execute this stored procedure.

Inside the function/stored procedure you have to make sure that it works correctly when two different sessions are trying to insert the row at the same time. One possible way to do it is to use sp_getapplock.

You didn't clarify whether the "auto increment" number is the single sequence across all cities and gas stations, or whether each city and gas station has its own sequence of numbers. Let's assume that we want to have a single sequence of numbers for all cities and gas stations within the same month. When month changes, the sequence restarts.

The procedure should be able to answer the following question when you run it: Is the row that I'm trying to insert the first row of the current month? If the generated value is the first for the current month, then the counter should be reset to 1.

One method to answer this question is to have a helper table, which would have one row for each month. One column - date, second column - last number of the sequence. Once you have such helper table your stored procedure would check: what is the current month? what is the last number generated for this month? If such number exists in the helper table, increment it in the helper table and use it to compose the key. If such number doesn't exist in the helper table, insert 1 into it and use it to compose the key.

Finally, I would not recommend to make this composite value as a primary key of the table. It is very unlikely that user requirement says "make the primary key of your table like this". It is up to you how you handle it internally, as long as accountant can see this magic set of letters and numbers next to the transaction in his report and user interface. Accountant doesn't know what a "primary key" is, but you do. And you know how to join few tables of cities, gas stations, etc. together to get the information you need from a normalized database.

Oh, by the way, sooner or later you will have more than 9999 transactions per month.

Answer 3

In such cases, it is best to use a User-Defined function to generate this key and then store it. Like :

Create Function MyKeyGenerator(
@city varchar(250) = '',
 @gas_station_id varchar(250) = '')

AS


/*Do stuff here

*/

My guess is , you may need another little table that keeps the last generated auto-number for the month and you may need to update it for the first record that generates during the month. For the next records, during the month, you will fetch from there and increment by 1. You can alse use a stored procedure that returns an Integer as a return code, just for the autonumber part and then do the rest in a function.

---

Btw, you may want to note that, using the first letter of the month has pitfalls, because two months can have the same first letter. May be try the the two-digit-numeric for the month or the first three letters of the month name.

---

If you ready not to insist the the AI number exactly be of identity type, you can have another table, where it is a non-identity regular integer, and then run an SQL Server Agent Task calling a stored procedure that'll do the incrementing business.