Introduction to Database Relationships

The database terms relational and relationship describe the way that data in tables are connected. A relational database consists of a series of two or more tables linked by a specific key. A relational database differs from unstructured databases, which are common in big data initiatives. Relational databases tend to require strict rules about how tables are defined and what constitutes a valid relationship among tables.

Types of Database Relationships

Relationships allow you to describe the connections between database tables in powerful ways. These relationships can then be leveraged to perform powerful cross-table queries, known as JOINs.

There are three types of database relationships, each named according to the number of table rows 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 only one counterpart in the second table. One-to-one relationships are rarely used because it is often more efficient to put all the information in a single table. Some database designers take advantage of this relationship by creating tables that contain a subset of the data from another table.
• One-to-many relationships are the most common type of database relationship. They occur when each record in Table A corresponds to one or more records in Table B, but each record in Table B corresponds to only one record in Table A. 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 has several students. This one-to-many design helps eliminate duplicated data.
• Many-to-many relationships occur when each record in Table A corresponds to one or more records in Table B, and each record in Table B corresponds to one or more records in Table A. For example, the relationship between a Teachers table 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 occur when there is only one table involved. One common example is an Employees table that contains information about the supervisor of each employee. Each supervisor is also an employee and has a 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, a column in Table A contains primary keys that are referenced from Table B.

Consider the example of the Teachers and Students tables. The Teachers table contains an ID, a name, and a course column:

The Students table includes an ID, name, and a foreign key column:

The column Teacher_FK in the Students table references the primary key value of an instructor in the Teachers table. Frequently, database designers use PK or FK in the column name to identify a primary key or foreign key column.

These two tables illustrate a one-to-many relationship between the teachers and the students.

Relationships and Referential Integrity

After adding a foreign key to a table, create a database constraint that enforces referential integrity between the two tables. This step ensures that relationships between tables remain consistent. When one table has a foreign key to another table, referential integrity requires that any foreign key value in Table B must refer to an existing record in Table A.

Implementing Relationships

Depending on your database, you'll implement relationships between tables in different ways. Microsoft Access provides a wizard that allows you to link tables and also enforce referential integrity.

If you are writing SQL directly, first create the table Teachers, declaring an ID column to be the primary key:

CREATE TABLE Teachers (InstructorID INT AUTO_INCREMENT PRIMARY KEY,
Teacher_Name VARCHAR(100),
Course VARCHAR(100)
);

When you create the Students table, you declare the Teacher_FK column to be a foreign key referencing the InstructorID column in the Teachers' table:

CREATE TABLE Students (
StudentID INT AUTO_INCREMENT PRIMARY KEY,
Student_Name VARCHAR(100), Teacher_FK INT,
FOREIGN KEY (Teacher_FK) REFERENCES Teachers(InstructorID) )
);

Using Relationships to Join Tables

After creating one or more relationships in your database, leverage their power by using SQL JOIN queries to combine information from multiple tables. The most common type of join is a SQL INNER JOIN, which is a simple join. This type of join returns all records that meet the join condition from one or more tables.

For example, this JOIN condition returns Student_Name, Teacher_Name, and Course, where the foreign key in the Students table matches the primary key in the Teachers table:

SELECT Students.Student_Name, Teachers.Teacher_Name, Teachers.Course
FROM Students
INNER JOIN Teachers
ON Students.Teacher_FK=Teachers.InstructorID;

This statement produces a table something like this: