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.
- We would be duplicating information between the log and user tables. The username would appear more than once.
- 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.
- 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.