One of the fundamental parts of the use of databases is the fact that they are relational databases. A relational database allows you to tie data from multiple tables together in meaningful ways. We'll be following up with some examples, but for all intents and purposes, no piece of data should exist in more than one place in a database. This is of utmost importance when designing your database. One of the most obvious reasons is data integrity. In a case where you have something in multiple places in a database, what happens if you change data in one place of your database, but not another? The data becomes meaningless. This is because data integrity has been destroyed. If you come back to this data later, you will have conflicting information and thus can't trust either result to be accurate.

Let's have an example. In our previous tutorial I created a table called users. A screenshot is below.


The Users Table
Note our userid which is defined as the key field and is the identity. Now let's add another table to hold logging information. This could record user actions, errors, or whatever you want.


The Log Table
Notice we have another key identity called logid. We also have a column called userid. The userid referenced would be the userid of the person being logged. We could have put in the username instead, but that would have a few problems.

1. We would be duplicating information between the log and user tables. The username would appear more than once.
2. If the username were changed in the users table, failing to change it in the log table would result in a record we can't associate to an actual user.
3. What if there's more than one user with the same username?

However, by using the userid we don't run into this issue. The userid is unique and immutable. You can't have more than one and you can't change it. You can then use it to relate the log entry in the log table to the username, firstname, lastname, or any other field in the users table. A simplified example would go as follows.

First you would do a select from the log table to get the userid for a specific log.


Getting the Userid
Now that you have the userid of 2, you can get the user information like follows.


Getting the User Information
Seems a little long winded? Let's talk about joins on the next page.