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: 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: 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:
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: This statement produces a table something like this:
