We’re building a ticketing system for our customers (enterprise clients) and we’re trying to come up with a way to have a sequential, unique ticket ID generation system that is also user-friendly.
Unique ID generation is a fairly well-known problem and we’ve gone through some of the solutions used by other companies, such as Flickr, or Twitter, but they don’t serve our purpose of being "user-friendly". They’re far too long for our customers to remember or use them in communication via email or telephone.
Run-of-the-mill GUIDs are also not usable by us since we want sequential ordering.
We initially thought of generating the ID as a combination of the customer ID and the ticket ID itself.
For example, customer 1’s first ticket will be C1-T1, and then C1-T2.
Customer 2’s first ticket will be C2-T1, and then C2-T2.
This is certainly doable. We can simply look at the last customer_id-ticket_id
combination and just add 1 to it, but it involves a DB query, and we also need to take a lock, so that concurrent transactions don’t re-use the same incremented ticket ID. This essentially means moving this to an asynchronous flow as we can’t hamper any synchronous flow. Keeping it in sync means that customers who have high volume, may end up waiting a long time for an API call to finish because a bunch of concurrent transactions (from previous tickets of the same customer) are still waiting.
But the business requirement is such that the ticket ID needs to be generated immediately for consumption by the user.
So, while we have some solutions to work with, every one of them has some downsides or the other. Either they hamper the latency of a customer-facing API, or they are not immediately available, or they’re far too long to be user-friendly.
We’re stuck right now and have no good leads to work with.
So, we wanted to know from the community if there is any way we can generate a sequential, unique ID that is also short enough (maximum 8-9 characters maybe).
2
Answers
Unless there is a high volume of writes (>1000/s), the temporary lock on a dedicated "IDs" table is not likely to be costly.
An example of this, while maintaining high throughput, would be:
You acquire an ID as a separate transaction from the longer-lived transaction you may start for business purposes. This matches the behavior of
AUTO_INCREMENT
and may cause gaps – but in most cases that is not a problem.If you have a small, fixed number of items to maintain separate sequences, you may use the
SEQUENCE
schema object instead. Doing so for hundreds or thousands of customers would be a good way to piss off some DBA’s, though.This is similar to Mitch’s answer, but without the detail for SP, and using the LAST_INSERT_ID() example from the MySQL docs.
A simplified table for tickets –
A table for ticket sequences per customer –
Now, instead of doing a SELECT followed by an UPDATE, we just use a single update statement, storing the new sequence value in LAST_INSERT_ID(), ready for the INSERT into tickets –
db<>fiddle