CODEWITHSUNDEEP
c#primary-keyguiduniqueidentifier
David Thielen
David Thielen

Reputation: 32986

Need a user friendly UniqueId string for DB records

I need a user friendly UniqueId for each appointment item in my application. This UniqueId is a "folder" name for BLOBs attached to the item. So a Guid is not good.

One solution I came up with is munge the item subject together where I keep all alphanumerics, append a -, and then insert the record, get the record Id, and update the record with the Id. So the item with a subject of "Birthday Party" becomes "BirthdayParty-23". But that's 2 writes to the DB.

One semi-constraint is my save code is faster if I know the UniqueId value before creating the record in the DB. (This way I call the async methods to create the BLOBs, then call SaveChangesAsync(), then wait for all those tasks to complete.) Using the Item.Id PK means the writes become synchronous. (Still async calls, but the first SaveChangesAsync() must complete before the BLOB async calls can be made.)

So... what if I end up with "BirthdayParty-{num}" where num is a random number between 1 ... 999,999? This gives me a clean user friendly UniqueId. And it's jut 1 write to the DB.

The downside is there's a non-zero chance of a name conflict. Are the odds small enough that this is ok?

Or is there a better approach?

Upvotes: -1

Views: 57

Answers (1)

Logarr
Logarr

Reputation: 2417

I know I've commented asking for the "why" of your requirement, but if I were in your shoes here's what I would do (and have done in the past).

Forget linking the numeric component of your user friendly unique ID to the record's identity column value. It serves no purpose for you to try and keep them in line. Instead, use a separate sequence (assuming your DB vendor has such a thing) so that you can claim the next number before you've even written to the DB. This lets you write to the database only once.

You still have to make a trip to the DB to get that sequence value, but it's an extremely cheap operation. Here are some pros and cons for this approach...

Pros:

  • Only one write to the DB needed.
  • The calculated value of the combined alphanumeric and this sequence can be kept and displayed to the user without having to read the DB after the insert.
  • Since you don't have an immediate update command after your initial insert you don't have to worry about any automatic auditing you might have in place to track changes. Imagine if for every record insert you generated 2 log entries, and the second one is always "UserFriendlyId changed from 'xxxxxx' to 'xxxxxx'".

Cons:

  • Identity and this value are not synced, but if that's a requirement I feel you're doing something wrong.
  • The sequence can end up containing gaps if you request the next value and end up failing to write to the DB. But gaps can appear in an identity column as well.

Upvotes: 1

Related Questions