Relational SQL Databases

written by: George Ionescu; article published: year 2007, month 12;


In: Categories » Computers and technology » Databases » Relational SQL Databases

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.

Note 

If link tables are used, the optimized way is to store INT values there and have an ID field with auto_increment in the playerdata table as a primary key. It is not as readable when you perform a SELECT, but it is faster from within your applications. When updating a player's name, it does not break the integrity of the database. Also, link tables are often structures to be used only when there are n amount of relations from one row to other rows. If there is always only one relation (one friend or enemy), a direct link should be used.

 
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:

Note 

To prevent being a friend and enemy at the same time, one relation table could be used. Just add a field "enemy" flag, and if it is set, it means that they are enemies; otherwise they are friends.

 
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';

legal disclaimer

1) Our website is not responsible for the information contained by this article as well for any and all copyright infringements by authors and writers. E-articles is a free information resource. If you suspect this article for any copyright infringements, please read the Terms of service and contact us to investigate the problem.
2) The E-articles directory team is not responsible for inaccuracies, falsehoods, or any other types of misinformation this tutorial may contain and will not be liable for any loss or damage suffered by a user through the user's reliance on the information gained here. Please read the Terms of service

Useful tools and features

Translate this article to...    Send this article to you or to a friend

Link to this article from your page   
If you like this article (tutorial), please link to it from your web page using the information above. Linking to this page, this is the only way to help us improve our service, the same time providing your visitors with a way to improve their online experience.

related articles

1. Managing and Monitoring SQL Server 2000 Security
Authentication There are two methods of authentication offered SQL Server 2000, Windows authentication and SQL Server authentication. Windows authentication allows the SQL server authentication to be integrated directly with security on a Windows 2000/NT system. Authentication will depend on the security mode that SQL Server 2000 runs under, Windows Authentication mode, or Mixed Mode. If you are running SQL Server 2000, you will need to configure the SQL server to run in mixed mode to allow the non-Win...

2. Managing Monitoring and Troubleshooting SQL Server 2000
SQL Server Agent Jobs SQL Server Agent is a job scheduling agent that aids in the management of a SQL server. SQL Server Agent can be started when the operating system starts and is configured through the SQL Service Manager or Enterprise Manager. The SQL Server Agent is responsible for the execution of jobs. Jobs are made up of multiple steps that have instructions on what to do depending on the success or failure of each step in the job. Jobs are assigned to an operator, a user who is responsible for tha...

3. Managing Monitoring and Troubleshooting SQL Server 2000 Databases
Troubleshooting SQL Databases is an operation that supposes multiple actions. They are described below. Optimizing Database Performance Database performance is an important part of administering a SQL Server 2000 environment. There are many ways that performance can be increased including indexing, locking and recompiling. Indexing Establishing indexes can dramatically increase query performance, but can also adversely affect data modification performance. Any changes to an...

4. Tips for Designing a Better SQL Database
Keeping the following subjects in mind helps ensure that your database design and updates go smoothly. Don’t design a database that copes with the norm. Tempting as it is to design a database that covers most situations, doing so is dangerous. The unexpected happens just when you least expect it, so make sure you design your database to cover all situations that could arise, or at least ensure that it can cope with the unusual situations. Even if a client tells you not to worry about the possibility of two people attem...

5. Why and When to Use a Database
When there are a huge number of alternative ways to store data, why should you trouble yourself creating a database? What advantages does a database hold? The main advantage is fast and efficient data retrieval. A database helps you to organize your data in alogical manner. Database management systems are fine-tuned to rapidly retrieve the data you want in the way you want it. Databases also enable you to break data into specific parts. Retrieving data from a database is called querying. You’ll often see the term SQL query, which brief...

6. Creating SQL Server 2000 Databases
Attaching and Detaching Databases Detaching/attaching is used in moving a database between computers or physical disks. When a database is detached, the links from the server to the data files and transaction logs are removed. When you attach a database, you re-establish those links. When you detach and re-attach, you eliminate the need to restore the database backup manually. When a database is reattached, all files that have changed location must be specified. Filegroups Filegroups ar...

7. Developing Notification Services Applications in SQL server 2005
The process for developing Notification Services applications begins with defining the rules that govern how the application works. Next, you must compile the application. Then you need to construct an interface that allows the user to add subscriptions to the application. Finally, you’ll need to add any custom components that may be needed by the application. Let’s look at each of these steps in more detail. Defining the Application The Notification Services developer uses a combination of XML...

8. SQL Server Service Broker Activation
SQL Server Service Broker activation is another unique feature of the SQL Server Service Broker subsystem. Activation enables you to create a stored procedure that is associated with a given input queue. The purpose of the stored procedure is to automatically process messages from that queue. As each new message comes in, the associated stored procedure is automatically executed to handle the incoming messages. If the stored procedure encounters an error, it can throw an exception and be automatically recycled. ...

9. SQL Server 2005 Administrative Tools
This article gives you an overview of the SQL Server 2005 administrative tools, including: Profiler enhancements Database Tuning Advisor Assisted editors Profiler Enhancements With SQL Server 2005, Profiler gets an overhaul as well. The new Profiler now supports the ability to trace both SSIS and Analysis Services commands. SQL Server 2000 was limited to tracing relational database calls only. By having these capabilities, you can use these ...