英文:
Need a user friendly UniqueId string for DB records
问题
我需要一个用户友好的唯一标识符，用于我的应用程序中的每个约会项。这个唯一标识符是附加到该项的BLOB的“文件夹”名称。因此，Guid 不太合适。
我想到的一个解决方案是将项的主题混合在一起，其中我保留所有的字母数字字符，然后附加一个连字符，然后插入记录，获取记录 Id，然后使用该 Id 更新记录。因此，主题为“Birthday Party”的项变成了“BirthdayParty-23”。但这需要对数据库进行2次写入。
一个半约束是，如果我在创建数据库记录之前知道了唯一标识符的值，我的保存代码会更快。这样，我可以调用异步方法来创建BLOBs，然后调用 SaveChangesAsync()，然后等待所有这些任务完成。使用 Item.Id 主键意味着写入变成了同步的（仍然是异步调用，但必须在可以进行BLOB异步调用之前完成第一次 SaveChangesAsync()）。
所以...如果我最终得到“BirthdayParty-{num}”，其中 num 是 1 到 999,999 之间的随机数，这为我提供了一个干净的用户友好的唯一标识符。而且只需要进行1次数据库写入。
缺点是存在名称冲突的非零几率。这种几率是否足够小，可以接受？
或者是否有更好的方法？
英文:
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?
答案1
得分: 1
我知道我之前询问了您为什么需要这样做的问题，但如果我站在您的位置，这是我会做的事情（过去我也曾这样做）。
忘掉将用户友好的唯一标识的数字部分与记录的标识列值进行关联。试图使它们保持一致对您没有任何意义。相反，使用一个单独的序列（假设您的数据库供应商支持这种功能），这样您甚至在写入数据库之前就可以获取下一个数字。这样可以只向数据库写入一次。
您仍然需要访问数据库以获取该序列值，但这是一项非常廉价的操作。以下是此方法的一些优点和缺点...
优点：
- 只需要一次向数据库写入。
- 可以将字母数字组合的计算值保存并显示给用户，无需在插入后读取数据库。
- 由于您在初始插入之后没有立即的更新命令，所以无需担心可能已经启用的自动审计来跟踪更改。想象一下，如果每次记录插入都会生成2个日志条目，第二个条目总是"UserFriendlyId changed from 'xxxxxx' to 'xxxxxx'"。
缺点：
- 标识和此值不同步，但如果这是一个要求，我认为您可能做错了一些事情。
- 如果您请求下一个值但最终未能写入数据库，序列可能会包含间隙。但标识列中也可能会出现间隙。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论