Unique, incremental ID generation constrained within an entity

Question

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).

Answer 1

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 -

CREATE TABLE tickets (
    customer_id INT UNSIGNED NOT NULL,
    ticket_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (customer_id, ticket_id)
);

A table for ticket sequences per customer -

CREATE TABLE customer_ticket_sequence (
    customer_id INT UNSIGNED NOT NULL PRIMARY KEY,
    seq INT UNSIGNED NOT NULL
);

-- initialise sequences for customers 1, 2 & 3
INSERT INTO customer_ticket_sequence VALUES (1, 0), (2, 0), (3, 0);

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 -

UPDATE customer_ticket_sequence SET seq = LAST_INSERT_ID(seq + 1) WHERE customer_id = 1;
INSERT INTO tickets VALUES (1, LAST_INSERT_ID());

db<>fiddle

Answer 2

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:

DELIMITER ;
-- Schema
CREATE TABLE Customers (
    CustomerID INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(50) NOT NULL,
    PRIMARY KEY (CustomerID)
);

CREATE TABLE CustomerSequences (
    CustomerID INT NOT NULL,
    Sequence varchar(50) NOT NULL,
    NextValue INT NOT NULL,
    PRIMARY KEY (CustomerID, Sequence),
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
);

-- Sample Data
INSERT INTO Customers (Name) VALUES ('Customer 1'), ('Customer 2');
INSERT INTO CustomerSequences (CustomerID, Sequence, NextValue)
SELECT CustomerID, 'Ticket', 1
FROM Customers;
INSERT INTO CustomerSequences (CustomerID, Sequence, NextValue)
SELECT CustomerID, 'Asset', 1
FROM Customers;

DELIMITER $$
CREATE PROCEDURE GetCustomerSequenceNo (IN cid int, IN seq varchar(50), OUT val int)
BEGIN
    START TRANSACTION;

    SELECT NextValue into val 
    FROM CustomerSequences 
    WHERE CustomerID = cid and Sequence = seq 
    FOR UPDATE;
    
    UPDATE CustomerSequences 
    SET NextValue = val + 1
    WHERE CustomerID = cid and Sequence = seq;
    
    COMMIT;
END$$
DELIMITER ;

-- Example use
select * from CustomerSequences;

call GetCustomerSequenceNo(1, 'Ticket', @Ticket11ID);
select @Ticket11ID;
call GetCustomerSequenceNo(1, 'Ticket', @Ticket12ID);
select @Ticket12ID;
call GetCustomerSequenceNo(2, 'Ticket', @Ticket21ID);
select @Ticket21ID;

select * from CustomerSequences;

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.