Can a client ever reliably generate a PK for an object being written to a DB?

Question

I've been fussing with this dilemma for a while now and I thought I'd approach SO.

A bit of background on my scenario:

• I have Playlists which contain 0 or more PlaylistItem children.
• Playlists are created infrequently. As such, I am OK with requesting a GUID from the server, waiting for a response and, on success, refreshing the UI to show the successfully added Playlist.
• PlaylistItem objects are created frequently. As such, I am NOT OK with a loading message while I wait for the server to respond with a UUID.

This last fact is an optimization, I know, but I think it greatly improves the usability of the program.

Nevertheless, I would like to discuss my options for uniquely identifying my object client-side. I'll first highlight the two options I have tried and failed with, followed by a third option I am considering. I would love some insight into other possible solutions.

---

Generating a PK UUID client-side which will be persisted to the server.

This was my first choice. It was an obvious decision, but has some clear shortcomings. The first issue here is that client-side UUIDs can't and shouldn't be trusted for this sort of purpose. A malicious user can force PK collisions with ease. Furthermore, my understanding is that I should expect a greater collision chance if I chose to generate UUIDs client-side. Scratching that.

Generate a composite PK based on Playlist GUID and position in Playlist

I thought that this was a tricky, but great solution to my issue. A PlaylistItem's position is unique to a given Playlist collection and it is derivable both client-side and server-side. This seemed like a great fix. Unfortunately, having my position be part of the PK breaks the immutability of my PK. Whenever a PlaylistItem is reordered or deleted -- a large amount of PlaylistItem keys would need to be updated. Scratching that.

Generating a composite PK based on Playlist GUID and an auto-increment PlaylistItem ID

This solution is similar to the one above, but ensures that the PK is immutable by separating the composite key from the position. This is the current solution I am toying with. My only concern is that a malicious user could force collisions by modifying the auto-incremented id of the client before sending along. I don't think that this sort of malicious act would cause any harm to the system, but something to consider.

Okay! There you have it. Am I being stupid for doing all of this? Do I just suck it up and force my server to generate the GUIDs for my PlaylistItem objects? Or, is it possible to write a proper implementation?

UPDATE:

I am hoping to represent the user's action visually before the server has successfully saved to the database and implement the needed recovery techniques if the save fails. I am unsure if this is fool-hardy, but I will explain my reasoning through a use case scenario:

The client would like to add a new PlaylistItem. To do so, a request to YouTube's API is made for all the necessary information to create a PlaylistItem. The client has all necessary information to create a PlaylistItem after YouTube's API has responded, except for the ability to uniquely identify it.

At this point, the user has already waited X timeframe for YouTube's API. Now, I would like to visually show the PlaylistItem on the client. If I opt to wait for the server, I am now waiting X + Y timeframe before there is a visual indication of success. In testing, this delay felt awkward.

My server is just a micro instance on Amazon's EC2. I could reduce Y timeframe by upgrading hardware, but I could eliminate Y completely with clever programming. This is the dilemma I am facing.

Blabbermouth

It's unclear exactly what the client/server communication is. Does it really take much longer to get back a UUID from the server (generated on insert) than it does to do an insert with a client-supplied GUID? Or are you trying to avoid any server interaction when creating a PlaylistItem, deferring the insert until later? – Merwin

@JonSkeet I've updated my original post with additional information based upon your questions. Does this help clarify my problem? – Blabbermouth

Yes, thanks. Now I just need to think about a solution. If you're happy to trust the client a bit, you could use a high/low algorithm - effectively "reserving" a bunch of IDs. You could then keep track of what the client actually supplies, and provide an error if they've been naughty... – Merwin

That's a pretty genius way of going about it. Feel free to think on it for a while, I'm in no rush... going to have a think about that suggestion though. I've never even considered such a possibility! – Blabbermouth

Answer 1

Okay, as you seemed to like it when I suggested it in a comment :)

You could use a high/low approach, which basically allows a client to reserve a bunch of keys at a time. The simplest way would probably be to make it a composite primary key, consisting of two integers. The client would have one call along the lines of "give me a major key". You'd autoincrement the "next major key" sequence, and record which client "owns" that major key. That client can then use any minor key alongside that major key, and know that they'll be isolated from any other clients.

When the client performs an insert, you can check that the client is using the right major key, i.e. one assigned to them.

Of course, an alternative way of approach this would be to just make the primary key { client ID, UUID } and let the client just specify any UUID...

Merwin

I am going to do this! You made my day. I'll let you know if anything goes awry, but I suspect this is 'the solution I was looking for.' :) THANK YOU! – Blabbermouth

Here's some links for people trying to understand the concept. https://mcmap.net/q/73917/-what-39-s-the-hi-lo-algorithm, quora.com/What-is-the-Hi-Lo-algorithm-and-when-is-it-useful, •talkingdotnet.com/… – Whaley