Mysql – What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs

In a lot of relational database designs there are fields that are referenced in other tables.

For example consider a user table with a unique username and a second table storing address data.

One possible layout, that I would say is the common approach, because I have observed in most software, is to use auto increment ids like this:

Table users
===========
userId int primary auto_increment
userName varchar unique

Table adressdata
==========
userId int references users.userId
adress_type varchar // for example country
address_value varchar // for example US
(you probably also want to put a unique key on (userId,adress_type))

This is how I used to do it and how I have seen it in most cases.

Another way would be:

Table users
===========
userName varchar primary

Table adressdata
==========
userName varchar references users.userName
adress_type varchar // for example country
address_value varchar // for example US
(you probably also want to put a unique key on (userName,adress_type))

Here we store the full username also in the adressdata table.

To me this has the following advantages:

• You can select the username right away from the table without the need to join it to another table. In this example this is from an application point of view probably not so relevant, but it is only an example.

• It may be easier to scale the database in a master-master replication environment, because there are no auto_increment conflicts.

But also the disadvantages:

• The space requirements for the index and data (but more relevant will probably be the index) on the field in the second table are higher.
• A change of the username would need to propagate to all tables, which is more resource consuming than just changing it in one table and leave the ids as they are.

In my opinion it is much easier to work with text fields and don't use increment ids, and the trade offs are minimal and in most applications not relevant.

Of course some object ARE identified with an incrementing number by their nature (for example forum posts should receive an incrementing id because there probably is no other unique field like title or so).

But I before I start designing my database layouts in a completely different way I would like to know whether there are things I did not think of.

• Are there any best practices?

• Are there pros/cons that I did not think and the effect of which may arise at a later poit in time?

• How do you personally design databases regarding the points above and why?