I have a business specific pattern for storing their IDs. It's in the format of
I could just store
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?
- Last value is
18-08-90and then got deleted
18-08-89is the last highest
- add 1 and it's now
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
Possible solution I could think of:
- create another table called
- have column called
How does that solve the problem? Say I have
90 as row. I will use the PK as PK+FK on the
So now when I deleted
18-08-90, the last incremental value is existing on
x_identities table and now can do
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?