Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Nov 23 '11 at 5:05

This question came from our site for professional and enthusiast programmers.

4 Answers

I'd suggest to use the id and not the username, because if you start to use the username as a join column in multiple tables, you've to remember to update all of them.

The foreign key for the users table, becomes the primary key of addressdata table and the primary key must remain stable. It's a good practice to not change primary key fields. A primary key must exist when the record is created, and has to remain unchanged for the whole lifetime of the record.

If you'd like further insights The great primary-key debate is a great article.

share|improve this answer
add comment

I am strongly in the "don't use natural keys" camp. That's because I have seen how hard it is on the sytem when they get updated and virtually all natural keys that involve names of anykind get updated.

Databases are optimized to use joins. Yes, you can save some joins by using natural keys but the performance hit when you need to update 1,000,000 records because a group of natural keys changed (or even depending on what is happening) can be a massive logjam.

I would only use natural keys under two conditions:

  1. if the key is fairly guaranteed not to change (think automobile VIN numbers) and
  2. if it is never going to be reused (even unique things like phone numbers and emails are not a candidate for a PK because they get reused when someone stops using them).

And of course all too many natural keys that are supposed to be unique are not. If you are worried about replication you can use GUIDs.

share|improve this answer
add comment

The Wikipedia article on Surrogate key has a few interesting bits scattered around:

  • "Attributes that uniquely identify an entity might change, which might invalidate the suitability of the natural, compound keys." E.g., later restrictions on user names may invalidate existing keys when using the natural key user name whereas this will not affect a synthetic key.
  • "Surrogate keys do not change while the row exists." Thus, you don't need to (manually or automatically) cascade key changes to references tables.
  • "The values of generated surrogate keys have no relationship to the real-world meaning of the data held in a row." This can make auditing difficult.

I believe that the attentive reader can find additional points to consider.

share|improve this answer
    
Good answer. Many natural keys have a tendency to changes. This makes them unsuitable for keys which may be referenced as a foreign key. There are many reasons that it is appropriate to change a user's userid. –  BillThor Nov 24 '11 at 0:56
add comment

I'll post from my experience which will probably be vastly different to what various DBAs might suggest. I'm mostly oriented towards mix of performance and maintainability when designing databases for various projects.

I'd never, ever use a natural key for primary key. Especially if I use MySQL / InnoDB. I still haven't seen any benefit in using a natural key, usually what I see are performance implications if nothing. I bolded "never, ever" only because natural keys used to create performance hogs for my projects. Surrogate (integer) was always a better choice. Some might not agree, but we live in a world where performance does play a part over the theory.

When it comes to JOINs, I don't try to avoid them at all costs but I tend to optimize them. I try to abuse InnoDB's clustered index (primary key) as much as possible. If JOINs are performed via PKs, then they are extremely fast. I also tend to avoid FKs where they make no sense. Honestly, I wouldn't care so much about data integrity when it comes to linking users and their address information. I would enforce it when linking invoices to items to users. Overusing FKs is an overkill and a nightmare to maintain after you reference everything, thinking it's great design to stick relations all over the place. At some point in time, things need changing and when MySQL starts complaining with error 150 constantly, you just want to go home.

You also mentioned replication and avoiding clashes due to the nature of auto_increments. I had a project where we had an amount of databases storing product sales information, the amount of databases was variable. Every day the databases were replicated to one "master" database which we used to run reports off. The way I avoided PK clashes was by making a compound primary key from an auto_increment part and another INT part that denoted the location where the record came from. That way I could track where things came from and I didn't lose anything (products had the same ID, only the location identifier was changed).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.