text based primary keys

[eluser]Bramme[/eluser]
Hey everybody

I wonder what the objections, if any, are against using text-based primary keys.

I'm building a little backend where I store my controllers in a db table 'controller_id', 'controller_name' and 'controller_fancy_title'.

In other tables I reference controller_id, but I might as well use controller_name (the filename of the controllers without .php), it's a unique key anyway.

[eluser]centurionas[/eluser]
Hi Bramme,

I have seen one micro solution/system with text type columns for PK and IMHO I believe everything is about the size. As for me, I always prefer number type PK, and, I have never seen such solution in solid production systems. The bigger system becomes (more data) the more slower query performance will be. That is because PK index (tree of values) based on number type columns is a lot more faster if compared to text type, because bumber comparison is faster than text comparison.

[eluser]Victor Michnowicz[/eluser]
I like using text-based primary keys. It makes dealing with foreign key relationships easier in phpMyAdmin. If you create a foreign key relationship between your controller_name in your controllers table and controller_name in your example table, when you go to insert data into your example table it will give you all the controller_names in a select drop down. It is a lot nicer than looking at a number and having to reference that table to see what item it is referring to.

[eluser]obiron2[/eluser]
@centuionas,

Numeric PKs are often auto-increment fields which ahve no meaning on their own. their advantage is that in index paging you will never be inserting records into the middle of an index which means you will (marginally) improve both space and performance.

@Bramme,

There is nothing wrong with text based PKs, (or date/time based), or multipart mixed PKs although I would shy away from fields in a PK that could potentially be NULL because Oracle doesn't like them. The PK should be whatever is the best real-world representation of uniqueness. You may also want an autio-increment field which you can use as a FK field to save multi-part FKs which are a PITA, especially if you have referential integrity turned on.

Obiron

[eluser]Bramme[/eluser]
Thanks for all the replies guys!

For now, I've decided to just look up the ID once (in MY_Controller) and use it from there on. I read on multiple sites that INT based PK's are faster when looking up/joining data, so I might keep using it like this.