Newcomers to the world of databases often have a hard time seeing the differences between a database and a spreadsheet. They see tables of data and recognize that databases allow you to organize and query data in new ways, but fail to grasp the significance of the relationship
that gives relational database technology its name.
Relationships allow you to describe the connections between different database tables in powerful ways. Once you’ve described the relationships between your tables, you can later leverage that information to perform powerful cross-table queries, known as joins.
Types of Database Relationships
There are three different types of database relationships, each named according to the number of table rows that may be involved in the relationship. Each of these three relationship types exists between two tables.
- One-to-one relationships occur when each entry in the first table has one, and only one, counterpart in the second table. One-to-one relationships are rarely used because it is often more efficient to simply put all of the information in a single table.
- One-to-many relationships are the most common type of database relationship. They occur when each record in the first table corresponds to one or more records in the second table but each record in the second table corresponds to only one record in the first table. For example, the relationship between a Teachers table and a Students table in an elementary school database would likely be a one-to-many relationship, because each student has only one teacher, but each teacher may have multiple students.
- Many-to-many relationships occur when each record in the first table corresponds to one or more records in the second table and each record in the second table corresponds to one or more records in the first table. For example, the relationship between a Teachers and a Courses table would likely be many-to-many because each teacher may instruct more than one course and each course may have more than one instructor.
Self-Referencing Relationships: A Special Case
Self-referencing relationships are a special case of a normal table relationship. The only difference is that in this case, there is only one table involved and it is on both sides of the relationship. One common example is an Employees table that contains information about the supervisor of each employee. Each supervisor is also an employee and has his or her own supervisor. In this case, there is a one-to-many self-referencing relationship, as each employee has one supervisor but each supervisor may have more than one employee.
Creating Relationships with Foreign Keys
You create relationships between tables by specifying a foreign key
. This key tells the relational database how the tables are related. In many cases, it consists of including a column in the first table that contains primary keys from the second table.
Consider again the example of the Teachers and Students tables. You create this relationship by adding a column to the Students table called Teacher. In that column, you include the primary key value corresponding to the student’s teacher in the Teachers table (typically a Teacher ID number or similar field).
Relationships and Referential Integrity
Once you’ve added a foriegn key to a table, you can then create a database constraint that enforces referential integrity
between the two tables. This ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
When you’re ready to create a database relationship, the exact steps will depend upon the type and version of database software you’re using:
Using Relationships to Join Tables
Once you’ve created one or more relationships in your database, you can leverage their power by using JOIN queries to combine information from multiple tables.
It's often easiest to describe relationships in graphical form. For this purpose, database designers often use entity-relationship diagrams
, such as the one shown above. For more information on ER Diagrams, see Creating and Reading Entity-Relationship Diagrams