Viability of surrogate key based from existing column as primary key

Question

I'm designing the schema for a database and have a question about primary key choice. There aren't any natural keys available in the tables we're looking at now although there's interest from my team in using more "readable" keys to see relations between tables a bit easier.

The current proposed solution is to have a primary key that is an existing column + a random number. For example: we have a PERSON table that has a a Name column. The PersonID is the primary key with it being the Name + some number. So the person named Alex has the PersonID Alex1.

We're looking at potentially several thousand entries. Will this hinder performance using a string as a primary key? Is there any reason to use or not to use a more "readable" key?

It's probably worth noting that most systems our team is used to working have no more than a small handful of tables where bouncing around and seeing relations directly isn't too difficult. This project we're mapping out know will have a dozen to potentially several dozen tables. Also, we use MySQL and often enough team members use PHPMyAdmin to enter data directly which I think is the main reason there's a push to have readable keys.

Answer 1

You could do this - but I wouldn't. Here's why:

I generally try to avoid using "human meaningful" values as keys - because humans may decide they want the value to be something else. Since your key is partly a human meaningful value, there will be a temptation to change it.

For instance, "Alex" decides they want to use their full name, "Alexos". Do you change the PK to "Alexos1", or leave it as "Alex1"? With cascading updates, it's not hard to update PKs - but, it can cause locking issues, if there are a lot of foreign keys tied to it.

Next, there's the "random number" you want to add to the name to create your key.

• If it's truly random, you have to check for collisions, and set some upper limit (which could have to change, if you exhaust the possible values when tied to a particular name).
• If it's not random, but tied to the underlying data in some way, you may have to calculate it (for instance, if the next "Alex" should be "Alex2").
• If it's actually unique (for instance: if you have a auto-increment integer column, uid, that's actually unique, and you combine the name and uid columns to make name_id), it's easy to build (as long as the name column can't have digits at the end of it - if it can, then collisions are possible again), but:
  • you may start to lose the easy linkage - if you've got "Alex326", "Alex3156", and "Alex12581", you may get to a point where matching the number 3156 in both tables would be just about as easy as locating "Alex3156".
  • it's overkill, because uid is already a perfectly good key value.

The big pro for your team would be the ability to connect data rows in two different lists. If you're dealing with Excel, this can make sense; in a SQL database, it really doesn't. If you want to match the rows of two different tables, just run a query that JOINs them.

I mention a few potential "cons" above. Some of them tie back to more generic DB design concerns.

• The issue of whether to change the key if the name value changes, and of creating a combined key out of name and a uid column - both entail denormalizing the data, in effect.
• String-valued keys almost always consume more disk space than unique integer keys. This will affect every index on the table; they'll consume more space, too. And, the more space your data takes up, the more time it takes to for each query to run. (Admittedly, in practice, with a relatively small table, the size of your key may not cause a noticeable difference to your users; but at several thousand rows, the difference will be there).

I would be inclined to use an auto-increment integer ID in the table, and get the users used to pulling the data back via queries that join the relevant tables, instead of relying on keys that people can "eyeball" in separate table dumps. If necessary, create views for them to hide the joins.

This may make data entry via PHPMyAdmin a little harder, initially. However, users will adapt - for instance, copying and pasting the ID into a linked record, if necessary. Designing a database to make data entry tasks a smidgen easier isn't generally a great strategy - in most cases, the benefit you're looking for in having the data in a database in the first place is more a matter of using it once it's in your system, than just getting it entered.

Answer 2

Keys first and foremost should be designed to satisfy business requirements. You need to understand what the business process is and how people (customers? or employees? or something else?) need to be identified as part of that process. Then you can design a suitable key strategy to support those needs.

You have said nothing about the about the business requirement so it's difficult to give a specific answer. One observation I would make is that your proposed scheme is probably not ideal to support customer-facing identification online or by telephone. Possibly your scheme could serve a purpose for employee identifiers in some circumstances.