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: 
This is a decision between [surrogate and natural keys](http://www.agiledata.org/essays/keys.html), the first being surrogate (or "technical") and the second being natural.

I've come to the conclusion that you should pretty much always use surrogate keys. If you use natural keys, those may change and updating primary/foreign keys is not generally a good idea.
I personally believe the first approach is far, far better. It lets the database software do simple integer comparisons to find and sort by the key, which will improve table operation performance (SELECTs, complex JOINs, by-key INDEX lookups, etc.)

Of course, I'm assuming that either way, you're using some kind of auto-incrementing method to produce the IDs - either a sequence, an AUTO\_INCREMENT, or something similar. Do me a favor, and don't build those in your program's code, OK?