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 prefer Example 1 for the reasons you mentioned and the only argument that I can think of for using Example 2 is if you are trying to accomodate string IDs from an existing database (quite common) however even in that scenario, I prefer to use the following approach.

```
==AssetId(PK)==Type========DeprecatedId====
  12345        "Manhole"   "MH64247"
  155415       "Pit"       "P6487246"

```
If your assets already have unique natural identifiers (such as employees with their employee IDs), use them. There's no point creating another unique identifier.

On the other hand, if there's no natural unique ID, use the shortest one you can that'll ensure enough unique keys for your expected table size (such as your integer). It'll require less disk space and probably be faster. And, in addition, if you find yourself needing to use a string-based key later, it's a simple substitution job:

* add sting primary key to asset table.
* add string foreign key to referring tables.
* update string relationships with simple UPDATE command using integer relationships.
* add foreign key constraints for sting columns.
* remove foreign key constraints for integer columns.
* remove integer columns altogether.

Some of these steps may be problematic on specific DBMS', perhaps requiring a table unload/reload to delete the integer primary key columns but that strategy is basically what's required.