This section illustrates some of the trickier concepts related to database design. In order to complete this section, you should be familiar with all the database concepts discussed so far. The following examples use MX Query Builder to represent database diagrams.
By now, you should already know that a foreign key is used to reference a record in another table. A self-referencing foreign key (or self foreign key, for short) links to the same table, instead of another one. Why would you need to link to the same table? This is useful when you need to define a parent-child relation.
For example, in a complex product catalog, you may need to define nested categories. Under Electronics, you have subcategories for audio devices, video devices, computers, and cameras. Then, under Cameras, you have categories for digital cameras, video cameras, and so on. Why use a separate table for each level of categories, when you could store all of them in the same table and remember which categories belong to which? This is where the self foreign key comes in.
In the categories table shown below, the self foreign key is used to reference the primary key of another category. For the main categories (those on the first level, which do not have a parent), the self foreign key defaults to 0.
Here is a listing of the categories, to understand how subcategories are linked to their parents using a self foreign key:
The first level categories are Books, Electronics, Home & Garden. Audio, Video, Computers and Cameras are all subcategories of Electronics. Finally, Digital Cameras, Film Cameras, and Accessories fall under Cameras.
Here is how the menu of the catalog would look:
A good (and classic) example of a many-to-many relations is a table storing students and classes. A student can attend multiple classes, and one class is attended by several students. Using only two tables, one for the students and one for the classes will not solve the problem, because you will certainly end up with duplicate records. This is a situation where a linking table is used:
The linking table stores only the primary keys of the tables involved in the relation. The goal is to transform the many-to-many relation into two one-to-many relations, which can be handled easier.
The three tables involved in this example are:
student_std – stores the student information (ID and name).
course_crs – stores the course information (ID and name).
stdtocla_stc – stores references between students and courses.
Another classic example, used mostly to demonstrate the use of JOIN operations to retrieve information from multiple tables is the one that models the departments–employees relation.
The relation modeled is a simple one-to-many relation: each department has several employees, but one employee can only work in one department.
Based on these table, you can retrieve different sets of data:
All employees that have matches in the department table (e.g. all employees that are assigned to a specific department). This is achieved by using the INNER JOIN:
SELECT departments_dep.id_dep, employees_emp.iddep_emp, departments_dep.name_dep, employees_emp.name_emp
FROM (employees_emp
INNER JOIN departments_dep ON departments_dep.id_dep=employees_emp.iddep_emp)
All employees, regardless of whether they work in a department or not. This is achieved by using a LEFT JOIN between the employees and departments tables:
SELECT departments_dep.id_dep, employees_emp.iddep_emp, departments_dep.name_dep, employees_emp.name_emp
FROM (employees_emp
LEFT JOIN departments_dep ON departments_dep.id_dep=employees_emp.iddep_emp)
All departments, even if there are no employees assigned to it. This can be achieved in two ways: either by using a RIGHT JOIN between the employees and departments tables, or by using a LEFT JOIN between the departments and employees. It is just a matter of switching the places of the tables in the relation:
SELECT departments_dep.id_dep, employees_emp.iddep_emp, departments_dep.name_dep, employees_emp.name_emp
FROM (employees_emp
RIGHT JOIN departments_dep ON departments_dep.id_dep=employees_emp.iddep_emp)
or
SELECT departments_dep.id_dep, employees_emp.iddep_emp, departments_dep.name_dep, employees_emp.name_emp
FROM (departments_dep
LEFT JOIN employees_emp ON employees_emp.iddep_emp=departments_dep.id_dep)
The result is the same: