REST API Primary Key Design MySQL

Question

We're building a new REST API and have a few decisions to make regarding the primary key and idempotency_key. Our API has many users and users are only allowed to lookup objects that belong to them. Requests are authenticated so we know the userId associated with a given request.

Properties of concern:

• userId The id of the user.
• idempotenyKey The idempotency key used in idempotent requests to the API. Unique per user.

Let's suppose there is a hypothetical table Cars with endpoint GET .../cars/{id} which we'll base our discussion on.

Options

Option 1 - Use composite of userId and idempotencyKey as primary key

CREATE TABLE Cars (
 userId VARCHAR(255) NOT NULL,
 idempotencyKey VARCHAR(255) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (userId, idempotencyKey),
 INDEX user_index (userId)
);

Item Lookup: GET .../cars/{idempotencyKey}

Thoughts:

• The id used in lookups is guessable.
• Since objects are authenticated this doesn't appear to pose an immediate security concern, but it does mean if ever there is an endpoint in the API that allows our user's user to look something up, we need to assume the lookups are guessable.

---

Option 2 - Using a hash of the userId + idempotencyKey as the primary key

CREATE TABLE Cars (
 id VARCHAR(255) NOT NULL,
 userId VARCHAR(255) NOT NULL,
 idempotencyKey VARCHAR(255) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (id),
 INDEX user_index (userId)
);

When inserting we'd calculate id = hash(userId + idempotencyKey).

Item Lookup: GET .../cars/{id}

Thoughts:

• Primary keys are unguessable.
• This further prevents the possibility of security risks on operations against ids.
• No longer a composite primary key.
• Unlike option 3, doesn't require a unique constraint on (userId, idempotencyKey).

---

Option 3 - Generating a uuid primary key

CREATE TABLE Cars (
 id VARCHAR(255) NOT NULL,
 userId VARCHAR(255) NOT NULL,
 idempotencyKey VARCHAR(255) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (id),
 INDEX user_index (userId),
 CONSTRAINT unique_by_user UNIQUE (userId, idempotencyKey)
);

When inserting we'd set id = UUID.randomUUID().

Item Lookup: GET .../cars/{id}

Thoughts:

• id is totally unguessable and isn't tied to any business data.
• Requires an extra index.

---

Option 4 - auto incremented primary key that's never exposed to the user

CREATE TABLE Cars (
 id INT NOT NULL AUTO_INCREMENT,
 userId VARCHAR(255) NOT NULL,
 idempotencyKey VARCHAR(255) NOT NULL,
 description VARCHAR(255),
 PRIMARY KEY (id),
 INDEX user_index (userId),
 CONSTRAINT unique_by_user UNIQUE (userId, idempotencyKey)
);

There're a few ways to do a lookup. Lookup could be based solely on idempotencyKey, ie: GET .../cars/{idempotencyKey}

Alternatively, we could autogenerate a referenceId uuid which would be indexed and used for lookups.

Thoughts:

• The primary key is never exposed to the user which is a pretty common practice.
• Allows for more flexibility if business logic changes (though for fundamental concepts like userId and idempotencyKey, I can't see how these would change).
• The primary key is an INT and is thus smaller. This allows for smaller indexes and arguably more performance.
• Again requires an extra index.

---

Looking for advice on what approach to take. Considerations around performance and scale are very important.

Thanks!