I need Auto-incremental like key but how?

Question

I have a business specific pattern for storing their IDs. It's in the format of yy-mm-autoincrement.

I could just store date and incremental id but there's another problem. It should be restarted every year.

Ok, so now having auto-increment PK is now useless right? Since it is actually not auto-incremental.

I could just do something like where id LIKE yy-mm- to get the last highest for the current year, get the last digit, and add 1 right?

No.

Why?

• Last value is 18-08-90 and then got deleted
• 18-08-89 is the last highest
• add 1 and it's now 18-08-90

Yes it may be unique since 18-08-90 is deleted already so no more duplication right? For the business side, no.

What I need based from last example is 18-08-91

Possible solution I could think of:

• create another table called x_identities
• have column called date and incremental (or counter)

How does that solve the problem? Say I have 18-08-xx and 90 as row. I will use the PK as PK+FK on the x table.

So now when I deleted 18-08-90, the last incremental value is existing on x_identities table and now can do + 1

Now, the only problem with that is, how do I get the next available value from the app side?

I could just have getNextId() that inserts a new row in there right? year and `last increment + 1? Then use that? (Yes insert instead of just read because concurrency?)

I'm afraid no. If the app continuously executed that query, it will generate unused IDs. And if I check first if it's not being used, I might receive an ID from a record that has been deleted.

I'm not good at SQL side. Is there any SQL feature that can help me with this? Triggers? Procedures?

Also I might add because this might help, the reason I need an ID beforehand is because project is currently following DDD. Cannot create entities in invalid state so new Entity(Id, ....) is required. What does DDD practitioners would recommend me here?

Answer 1

I faced a very similar requirement some time ago. However, there were a couple of things influencing my decision that may not apply to the situation outlined in the answer:

1. I had to do it using MariaDB/MySQL
2. Users needed to see the ID while entering the data for the record.
3. The incremental part didn't need to be consecutive (e.g. some could be skipped).

---

I used a table with a single row (id 1) and the LOCK TABLES feature:

CREATE TABLE id_increment_generator(
    id INT NOT NULL,
    increment INT NOT NULL AUTO_INCREMENT,
    year INT(4) NOT NULL,
    month INT(2) NOT NULL
)

When someone opened the form to insert a new record, i ran this code to produce the new ID:

Run(LOCK TABLES id_increment_generator)
try {
    generatorRow = RUN(SELECT * FROM id_increment_generator WHERE id = 1)
    if (generatorRow not present) {
        RUN(INSERT INTO id_increment_generator VALUES (id, increment, year, month) VALUES (1, 1, YEAR(NOW()), MONTH(NOW())))
        return 1
    }

    if (generatorRow.year != CURRENT_YEAR || generatorRow.month != CURRENT_MONTH) {
        RUN(UPDATE id_increment_generator SET increment = 2 WHERE id = 1)
        return 1
    }

    increment = generatorRow.increment
    RUN(UPDATE id_increment_generator SET increment = increment + 1 WHERE id = 1)
    return increment
}
finally {
    Run(UNLOCK TABLES)
}

It worked pretty well. I dont know how well this scales but given the semantics, nothing is really going to scale well. The semantics require to synchronize on the entire system.

---

That said, PostgreSQL has sequences. They are pretty much what my code above does, but they dont reset regularily. If you use Postgres, you could us sequences instead of doing the manual SELECT/UPDATE that i do.