Char(4) as Primary key

Question

Classic database table design would include an tableId int index(1,1) not null which results in an auto-increment int32 id field.

However, it could be useful to give these numbers some meaning, and I wanted to know what people thought about using a Char(4) field for an table containing enumerables.

Specifically I was thinking of a User Role table which had data like;

"admn" - "Administrator"
"edit" - "Editor".

I could then reference these 'codes' in my code.

Update
It makes more sense when writing code to see User.IsInRole("admin") rather than User.IsInRole(UserRoles.Admin) where Admin is an int that needs to be updated/synchronised if you ever rebuild your database.

Answer 1

I always tend to use a surrogate primary key in my tables. That is, a key that has no meaning in the business domain. A primary key is just an administrative piece of data that is required by the database ...

What would be the advantage of using 'admn' as primary key in this case ?

Answer 2

Hard code => database references are always a bad idea! (Except you set them before application start etc.)

Beside this: Should mappings from admn=>administrator really be done in the database?

And you can only store 23^4-(keywords) entries with a varchar4 with a LATIN 23char alphabet.

Answer 3

An id field (not associated with the data) is called a surrogate key. They have their advantages and disadvantages. You can see a list of those on this Wikipedia article. Personally I feel that people overuse them and have forgotten (or have never learned) how to properly normalise a database structure.

Answer 4

A primary key is better if it's never going to change. You can change a primary key as long as you update all references to it but it's a bit of a pain.

Sometimes there's no natural non-changing column in a table and, in that case, a surrogate is useful.

But, if you have a natural non-changing value (like an employee ID that's never recycled or a set of roles that you never expect to change), it's better to use that.

Otherwise, you're introducing complexity to cater for something with a minuscule chance of happening.

That's only my opinion, my name isn't Codd or Date, so don't take this as gospel.

Answer 5

No. No, no, no, no, and no.

Keys are not data. Keys do not have meaning. That way when meaning changes, keys do not change.

Keys do not have encoded meaning. Encoded meaning is not even potentially possibly maybe useful unless you have an algorithm for decoding it.

Since there's no way to get from "admn" to "Aministrator" without a lookup, and since the real meaning, "Administrator" sits right next to the SEKRET ENKODED "useful" key, why would I ever look at the key instead of the real data right next to it in the table?

If you want an abbreviated form, an enum-like name, or what have you, then call it that and realize it's data. That's perfectly acceptable. create table( id int not null primary key, abbv char(4), name varchar(64));

But it's not a key, it doesn't hash like a integer key, it takes up four character compares and a look for the null terminator to compare it to "edtr", as opposed to one subtraction to compare two integers. There's no decent way to generate a next key: what's next in the sequence ('admn', 'edtr', ?)?

So you've lost generate-ability, easy comparison, possibly size (if you could have used, day, a tinyint as your key), and all for an arbitrary value that's of no real use.

Use a synthetic key. If you really need an abbreviation, make that an attribute column.

Answer 6

I think the answer is in your post. Your example of User.IsInRole("admin") will always return false as you have a primary key of char(4) and used "admn" as key for the administrator.

I would go for a surrogate Primary key which will never ever change and have the option for a 'functional primary key' to query certain roles which are used hardcoded in the code.

Answer 7

A key should preferrably not have any special meaning in itself. Conditions tend to change, so you may have to change a key if it's meaning changes, and changing keys is not something that you want to do.

Also, when the amount of information that you can put in the key is so limited, it's not much point of having it there.

Answer 8

You are comparing auto-increment vs. the fixed char key, but in that scenario you don't want an auto-incremented id.

There are different routes to go. One is to use an enum, that maps to int ids. These are not auto incremented and are the primary key of the table.

Answer 9

If you use a non-related number as primary key it's called Surrogate key and a class of person believe it's the best practice.

http://en.wikipedia.org/wiki/Surrogate_key

If you use "admn" as primary key then it's called Natural key and a different class of developers believe it's the best practice.

http://en.wikipedia.org/wiki/Surrogate_key

Both of them are never going to agree. It's a age old religious war (Surrogate Key vs Natural key). Therefore use what you are most comfortable with. Just know that there are a many that supports your view for everyone that disagrees --- no matter which one you chose.