Database Keys, Strings and Integers

5/23/2011

Most database tables have a primary key defined as an integer, often set to auto increment. In code it is common to also define ID fields as Integer data types. This is generally harmless as integer data types are light and simple. However defining the IDs as strings in code has a benefits.

IDs are Not Integers

Well yes technically they are but but they are also Tokens and Cookies. General integer or numeric operations are not performed on the ID column of a table, if you are then something is wrong as the key is not a true key it has some business meaning.

IDs are Cookies

Given the ID column of a record has no meaning, it is just a black box, or black field if you wish. Just a key to pass around. Declaring variables that hold ID values as strings is closer to this black box approach than a numeric would be. Declaring it as a specific custom type, such a ID or Token would be better but often tools and components will get in the way during data binding or URL encoding. Declaring the variable in code as a String makes the field easier to code with and has no downside. Strings are simpler to debug as you can write them out. Store in logs and append to SQL (not that any one would do that).

Declare IDs as Strings

Declaring ID fields as Integers in the database and Strings in code seems to be the best pattern. The database is happy and the code is cleaner.