Relationships and Relational Database Concepts

As we saw in the last lesson, SQL is used to manage data stored in relational databases. A relational database is a collection of data that is organized into tables, with each table having a specific structure and relationship to other tables.

In a relational database, each table represents a specific type of data, and the columns of the table represent the attributes of that type of data. For example, consider these two tables:

Courses table:

Lessons table:

The relationship between these tables is defined by a common column—in this case, the ID of the course. This common column is called a primary key in the courses table, and a foreign key in the lessons table.

A table’s primary key is the unique identifier for each row in a table, and is used to establish relationships with other tables. In this example, the courses.id column serves as the primary key, while the lessons.course_id column serves as a foreign key because it references the primary key of the courses table.

Types of Database Relationships

In a relational database, tables can have several different types of relationships with other tables, including one-to-one, one-to-many, and many-to-many relationships. Let’s look at each of these in more detail.

A one-to-one relationship is one where each record in one table is related to (at most) one record in another table, and vice versa.

A one-to-many relationship is more like a parent-child relationship, where the parent can have multiple children. In the example above, the courses table would be the parent table and the lessons table would be the child table. Each course can have multiple lessons, but each lesson belongs to only one course. In other words, multiple lesson rows can contain the same value for the course_id column in the table above.

A many-to-many relationship occurs, on the other hand, when multiple records in one table can be associated with multiple records in another table. Expanding on the example above, let’s imagine we now want to allow multiple instructors to teach the same course, but each instructor can also teach multiple courses. Here’s one way to represent this relationship:

Table: courses

Table: instructors

Table: course_instructors

Note that we introduced a third table course_instructors here in order to represent a many-to-many relationship. This association table is necessary because we cannot easily store multiple instructors or courses in a single column of either table. Instead we use this new table to represent the “links” between courses and instructors, where each row references the primary ID of the corresponding course and instructor. This is a popular method for representing many-to-many relationships, and can be used to efficiently represent many: friends in a social network, likes or upvotes on a news site, customer reviews in an e-commerce site.

These relational patterns also demonstrate a fundamental concept in database design called normalization, which is the way we choose to store data across separate tables. Without going into the full details, the central idea is that by having a single home for each data type, we can avoid storing redundant data while improving data integrity. As we’ll see in upcoming lessons, SQL’s powerful querying syntax and joins are what allow us to make use of data stored in this format.

In conclusion, relational databases and SQL are deeply linked. Understanding the structure and relationships of database tables is an important foundation for working with SQL.