Disadvantages of using a short string for an enum rather than an int?

I've got many enum-like fields in my database. Just an integer that's a FK to another table that might contain a display name or maybe a little additional data. Problem is, the number is pretty meaningless and not very nice to work with in the code. Also, you might run into some name/key clashing if you try to merge some of these enums from different databases, or move the data around, or if it's an autoincrement...

Are there any downsides of just using a short string? Kind of like a c开发者_如何学编程onstant? And use that as the primary key instead? An int is usually 4 bytes, I'm thinking I don't really need more than 10 chars, so a varchar(10) would do... I don't suspect disk usage would really be an issue.

My personal approach is to use text keys for items that would require changing code to add or remove. If a given item is something that a user should be able to add or remove, then I would stick with a surrogate key. This allows for your code to use text values which is far easier to read. An example might be "states" (e.g. On/Off, Acquired/Pending/Sold). Invariably there will be code that acts differently depending on the state and thus, that code needs look for specific state values. Those "states" are not something that anyone can simply add or remove or even rename. Code is dependent on them. Thus, for these, I use a string PK. However, something like say "categories" a user should be able to add or remove those at will and in those cases a surrogate key (combined with the appropriate business key) is better.

It depends on how immutable the key is.

If we're talking about linking to a table of US states using a postal code as a key, for example, there is no harm. It is exceedingly unlikely that anyone would ever change California's postal code from CA to something else, for example, so storing that as a key would be perfectly reasonable.

On the other hand, something like an Order Status Code is likely to change over time. When you first build the application, you may have a handful of statuses (i.e. "Taken", "Shipped", "Complete"). But then over time, the business may decide that new statuses need to be added and existing statuses need to be modified-- they may want to take the "Shipped" status, for example, and decompose that into "OnPallet", "InTransit", and "InCustoms". If that happens and you're relying on "Shipped" being a key, you'll have a bunch of updates to do to all the rows in the Order table that have a "Shipped" key or you would end up with a bunch of orders that have a less meaningful key. If you use a meaningless numeric key, on the other hand, you would merely have to update the Order_Status table to insert the new values and to update the one "Shipped" row.