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
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
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.