Relationships

One of the powers of relational databases is the ability to establish relationships between data stored across database tables. Relationships help maintain data integrity by establishing the correct references across tables, which in turn helps maintain the database. Relationship rules, on the other hand, ensure data consistency and prevent duplicates.

In a relational database, there can be the following types of relations:

• Many to one
• Many to many
• One to one

Let's explore each relationship in detail.

Many to One

In this relationship, many records (rows/entries) from one table can refer to one record (row/entry) in another table. For example, there can be many books produced by one publisher. This is a case of a many-to-one relationship. To establish this relationship, we need to use the database's foreign keys. A foreign key in a relational database establishes the relationship between a field from one table and a primary key from a different table.

For example, say you have data about employees belonging to different departments stored in a table called employee_info with their employee ID as the primary key alongside a column that stores their department name; this table also contains a column that stores that department's department ID. Now, there's another table called departments_info, which has department ID as the primary key. In this case, then, the department ID is a foreign key in the employee_info table.

In our bookr app, the Book model can have a foreign key referring to the primary key of the Publisher table. Since we have already created the models for Book, Contributor, and Publisher, now let's establish a many-to-one relationship across the Book and Publisher models. For the Book model, add the last line:

class Book(models.Model):
    """A published book."""
    title = models.CharField\
            (max_length=70, \
             help_text="The title of the book.")
    publication_date = models.DateField\
                       (verbose_name=\
                        "Date the book was published.")
    isbn = models.CharField\
           (max_length=20, \
            verbose_name="ISBN number of the book.")
    publisher = models.ForeignKey\
                (Publisher, on_delete=models.CASCADE)

Now the newly added publisher field is establishing a many-to-one relationship between Book and Publisher using a foreign key. This relationship ensures the nature of a many-to-one relationship, which is that many books can have one publisher:

• models.ForeignKey: This is the field option to establish a many-to-one relationship.
• Publisher: When we establish relationships with different tables in Django, we refer to the model that creates the table; in this case, the Publisher table is created by the Publisher model (or the Python class Publisher).
• on_delete: This is a field option that determines the action to be taken upon the deletion of the referenced object. In this case, the on_delete option is set to CASCADE(models.CASCADE), which deletes the referenced objects.

For example, assume a publisher has published a set of books. For some reason, if the publisher has to be deleted from the application, the next action is CASCADE, which means delete all the referenced books from the application. There are many more on_delete actions, such as the following:

• PROTECT: This prevents the deletion of the record unless all the referenced objects are deleted.
• SET_NULL: This sets a null value if the database field has been previously configured to store null values.
• SET_DEFAULT: Sets to a default value on the deletion of the referenced object.

For our book review application, we will be using only the CASCADE option.