Think of a database that related the players in a game to one another (for example, to determine who was a friend of each player and who was an enemy of each player).

First let's create a table to store the data for each of the players. Notice the addition of a primary key, which allows you to rely on the fact that all rows have a unique field that can be used as a reference. Note also that every row of data in the primary key must be unique to one another. Here is the statement required to create our database (called gamedata) and our playerdata table.

mysql> CREATE DATABASE gamedata;       
mysql> USE gamedata;       
mysql> CREATE TABLE playerdata (      
-> username CHAR(255) UNIQUE NOT NULL,      
-> password CHAR(255),      
-> age INT,      
-> datecreated TIMESTAMP,      
-> PRIMARY KEY(username));

Notice here how we set the username column to UNIQUE and also NOT NULL. In simple terms, this means that it must contain a value, and that value must not be the same as any other username in any other record in the table. Note also that we have set the primary key of the table to be the username field, as we will be mainly searching on this field, which you will see in a moment.

In addition to this information, we also need some way to store friends and enemies. This is done by means of a link table. A link table is really just a normal database table, but its main purpose is to relate data in some way or another to conserve space and optimize the way the database accesses the information.

Let's create two link tables, one for relating friends and one for relating enemies to each other. Following are the statements that are required to accomplish this.

mysql> CREATE TABLE relatefriends (     
-> player CHAR(255),      
-> friend CHAR(255));

Also:

mysql> CREATE TABLE relateenemies (    
 -> player CHAR(255),    
 -> enemy CHAR(255));

If we now show the tables in the database with the following command:

mysql> SHOW TABLES;

...we can see our database now contains three different tables—our playerdata table and the two link tables.

Let's now add a sample of data to the player data table, so we can experiment with the link tables and understand the logic of how to use them effectively. Here is the statement required to add our sample data to the playerdata table:

mysql> INSERT INTO playerdata VALUES      
-> ('Andrew', 'qwerty', 20, NULL),      
-> ('Henry', 'letmein', 34, NULL),     
-> ('Sandra', 'dra33', 19, NULL),      
-> ('John', 'j12d', 23, NULL),      
-> ('Jenny', 'jen123', 34, NULL);

If we select all the information from the playerdata table now using the following command:

mysql> SELECT * FROM playerdata

Now that we have some sample data, let's try to create some relations between the players in the database. First add to the relatefriends link table the fact that Henry is friends with Sandra. Here is the statement required to add this to the link table:

mysql> INSERT INTO relatefriends VALUES      
-> ('Henry', 'Sandra');

Let's now add some more sample data into both the relatefriends and relateenemies link tables and see how we can manipulate the data. The two statements required to add in the sample data are below:

mysql> INSERT INTO relatefriends VALUES      
-> ('Andrew', 'Henry'),     
-> ('Andrew', 'John'),      
-> ('Andrew', 'Jenny'),     
-> ('Sandra', 'Jenny');

And also:

mysql> INSERT INTO relateenemies VALUES      
-> ('Andrew', 'Sandra'),      
-> ('Henry', 'Jenny'),      
-> ('Henry', 'John');  

Now that we have all of our sample data, let's see if we can find out who Andrew is friends with by using the following statement:

mysql> SELECT friend FROM relatefriends WHERE player = 'Andrew';

When we execute this statement, the console displays a list of all the players that Andrew is friends with.

Again, we can do exactly the same with the relateenemies link table. For example, we could find out all of Henry's enemies with the following statement:

mysql> SELECT enemy FROM relateenemies WHERE player = 'Henry'

With this data, if we then wanted to find out more information about Henry's enemy that has the username of Jenny, we would use the following statement:

mysql> SELECT * FROM playerdata WHERE username = 'Jenny';