Use (numeric) IDs over names as unique key?

Question

I have a set of data (assume they are objects) with unique immutable names, like this:

class Datum {
 final string name
 // other fields
}

Considering that:

1. I don't need to support rename. (The names are immutable as I mentioned)
2. The names are unique. Among the data I store, there are no two data with the same name.
3. There may or may not (depending on how future plugins will be using the API) be a lot of API calls to search a datum by name.

(削除) Should (削除ここまで) When should I index my data with an addition numeric ID, (削除) or (削除ここまで) rather than just by the name?

• in databases?
  • The ID would be an auto_increment primary key from the database engine
  • The name is provided
• at a runtime memory storage (most likely hashtable, but also other data structures that may be applicable)?
  • The ID is an increment value attached with each datum when a datum is loaded.
  • The data may be inserted into or removed fro the storage anytime with no specific order, and may be re-inserted (after being removed), but the same datum only occurs in the storage once at the same time.
  • Consider implementation in different languages. For example, in Java, a HashMap<String, ?> vs HashMap<Integer, ?> may be used. In PHP, the difference is the key used in the array that stores the data.

Note: The two questions are independent from each other, i.e. yes for database only but no for runtime only may also be a reasonable answer.

• dagnelies: IMHO, both are valid options. Using a numeric ID is often used, but by no means necessary. If names are unique and don't require renaming, you may as well go with it.
• dagnelies: Btw, "search a datum by name" seems like a bad idea. Better convert the name into a "real" date and search with that.
• Dan Pichelman: @dagnelies A datum is not necessarily date or time related information.
• user22815: Possible duplicate of Is there a canonical source supporting "all-surrogates"?
• Laiv: You can also use UUID. What dislike to me about auto incrementals is at the time to design web APIs. I don't feel confortable using this sort of ID as path vars. Seems to me a very easy design to exploit. This is why I started recently to use UUID.

Answer 1

The case you describe is so bizarre that I question whether it is a purely theoretical one.

Here are the facts as I understand them:

1. You must have a unique Name independent of the need for a unique Identifier
2. People will search for data by that Name, and the Names will be user entered (no auto-generated)
3. You don’t care what the name is, Ie if a mistake is entered there is no need to fix it.

The primary advantage of using an auto-generated Key is the easy of creating new records. (Auto incremented or an Auto created Text ID is the same), you don’t have to check if a value already exist before inserting, you don’t have prompt the user for to enter one.

Now if you HAVE to check if a value already exist before inserting and you HAVE prompt the user for one. There is no need another Key, you can just use Name. But I strongly urge you to check your assumptions.

Answer 2

This makes sense to me. There are some problems where the business model offers up a natural "primary key".

If you can guarantee that your unique field is in fact unique and immutable, then you may as well use it.

Put another way, would adding a generated key improve your app?

Having said that, I agree with Morons - it's mighty rare to come across a situation where you can make that sort of ironclad guarantee.

One possible example might be if you had to store information related to a chessboard - there are 64 squares, with the rows numbered 1 - 8 and the columns labeled a - g. There will never be any more, and "a1" is guaranteed to be unique.

In business, it's tempting to use "part number" or "stock number" as an ID. Don't do it.