Question: We had a meeting this morning about how would should store our ID for some assets that we have in our database that we are making, the descusion generated a bit of heat so I decided to consult the experts of SO.

The table structure that I belive that we should have(short version) is like the following:

**Example 1)**

* AssetId - int(32) - Primary Key
* Type - string

so some example data is like this:

```
==AssetId======Type===
  12345        "Manhole"
  155415       "Pit"

```

etc.

Another member of the team suggested something like this:

**Example 2)**

* AssetId - string - Primary Key
* Type - string

so some example data is like this:

```
==AssetId======Type===
  "MH12345"    "Manhole"
  "P155415"    "Pit"

```

where we make a short version of the type and append it to the front of the ID and store it in the database. I have seen a few asset databases that do this and have never really this approach. 

I have never really liked the idea of using strings as ID for sorting reasons. I also feel like it is storing useless information just for the sake of it when you already have the type of asset store anyway.

What approach would you take? And why? Are there any benefits to using approach 1 over 2?

EDIT: Yes I will be using AUTO\_INCREMENT for approach 1.

Answer: 
I'd go for the former. Creating unique IDs should be left to the SQL server, and you can't have those created automagically in a thread-safe manner if they're strings. To my understanding you'd have to handle that yourself somehow?

Speed is another factor. Dealing with int values is always going to be faster than strings. I'd say that there are other perf benefits around indexing that a much more SQL savvy person than me could elaborate on ;)

In my experience, having string IDs has been a fail.
The one and only advantage of example 2 is that you can easily tell just from the primary key alone which row of which table this key applies to. The idea is nice, but whether or not it is useful depends on your logging and errormessage strategies. It does probably have a performance disadvantage, so I would not use it unless you can name some specific reasons why to use it.

(You can have this advantage also by using a global sequence to generate numerical keys, or by using different numeric ranges, last digits or whatever. Then you don't have performance disadvantages, but maybe you won't find the table so easily.)