1

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?

  • You can have the app do a passive getNextId() that inserts the row using the id, followed by updating the next id -- all inside a transaction. Thus, if the transaction is aborted (no row), so the id remains available for some other use, and if the transaction succeeds, then next id is updated. – Erik Eidt Aug 19 '18 at 15:20
  • @ErikEidt seems valid. Can you post that as an answer? Also provide a bit more technical details so that I can follow how I can implement it? I currently use transactions but not sure how to fit your answer so adding a bit more info would help. – doesnotmatter Aug 19 '18 at 15:40
  • For what it's worth, you'd be better off using a synthetic key such as a GUID for your primary keys. You wouldn't even need to ask the server for one, if you didn't want to. – Robert Harvey Aug 19 '18 at 16:43
  • I faced a very similar requirement a year ago. I can post an answer about how i solved it. However, there were three things that played a big role in my design and i dont know whether they apply to your situation: 1. Users entering the data to be associated with the ID needed to see the ID before submitting. 2- The incremental part need not be consecutive (e.g. increments could be skipped). 3. It had to be done using MariaDB. AFAIK Postgres Sequences could be of great help here. – marstato Aug 19 '18 at 17:35
  • @marstato I would appreciate if you can add that as answer and just leave a note that it might work if incremental does not need to be consecutive since it is still relevant and might help someone in the future. Thanks! – doesnotmatter Aug 19 '18 at 18:26
2

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.

  • This is similar to what I did while waiting for an answer. The only difference is that I only have single column for date and then I have another column called used. Setup a trigger to make it true on insert. The used column is for to prevent generating new ID if the last one is not yet used. Same as yours but with extra column/logic. Will accept this :) – doesnotmatter Aug 20 '18 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.