Creating relationships in an SQLite database
Building web applications with Python & Django
🕑 This lesson will take about 30 minutes
In this lesson, we will learn how to create relationships between tables in an SQLite database. In SQLite (and relational databases in general), relationships between tables are essential for organising and maintaining data in a logical and efficient manner. They help to link data across different tables, reducing redundancy (avoiding data duplication) and ensuring data consistency.
Relationships in SQLite (and relational databases in general) can be:
One-to-One (1:1): In a one-to-one relationship, a record in one table is linked to only one record in another table, and vice versa. For example, let’s say we have a system containing information about people and their passports where there is a table called Person (containing data about people) and a table called Passport (containing data about passports). In this scenario, each person can have only one passport and each passport can belong to only one person.
One-to-Many (1:N): In a one-to-many relationship, a record in one table can be linked to multiple records in another table, but a record in the second table is linked to only one record in the first table. For example, let’s say we have a database containing information about customers and their orders on an online store where there is a table called Customer (containing data about customers) and a table called Order (containing data about orders). In this scenario, each customer can have multiple orders, but each order is placed by only one customer.
Many-to-Many (M:N) Relationship: In a many-to-many relationship, multiple records in one table can be related to multiple records in another table. To implement this in SQLite, you need an intermediate (junction) table that stores the relationships. For example, let’s say we have a system storing information about students and courses at a university. In this scenario, each student can enrol in many courses, and each course can have many students. We may organise this by having three tables: Student (stores information about students), Course (stores information about courses), Enrolment (stores information about enrolments of students in courses and establishes a relationship between the Student and Course tables).
In this lesson, we will look at how to create a Many-to-Many relationship in our database. As an example, we will create a Cruise model for the travel guide app we have been working on. We will establish a link between the Cruise model and the Destination model (as cruises will be able to include one or more destinations, for example, a cruise between Barcelona and Monte Carlo that might stop off at St Tropez and Cannes on the way). There will be a Many-to-Many relationship, as cruises can have many destinations, and destinations can be included on many cruises.
To do this, we will create another model (class) in our models.py file.
Cruises will have a:
name (eg. “French Riviera & the Catalan Coastline”)
description (eg. “The best of the beautiful Mediterranean”)
destinations (a list of destinations eg. Barcelona, St Tropez, Cannes, Monte Carlo)
The new class for our Cruise model can be added to the bottom of the existing code in the models.py file (scroll down below the image to grab the full code)
Update your models.py file (in your app directory eg. myapp) so it now has the following code:
Save the models.py file and run the following commands in the VS Code terminal to create the new relationship and apply these changes to the SQLite database:
python3 manage.py makemigrations
python3 manage.py migrate
Note, if using Windows you might just need python instead of python3 at the start of the command.
Now if you open the SQLITE EXPLORER panel and click the Refresh Databases button, you should see a new table for the Cruise model (eg. myapp_cruise) and a new mapping table that maps between cruises and destinatins (eg. myapp_cruise_destinations).
Creating a magic method
Magic methods are special methods in Python that have double underscores on both sides of the method name (also known as dunder methods, short for double underscore methods) used for method overloading.
When we refer to objects in our views (like Destination or Cruise), we might want to, for example, print (display) information about the objects (eg. a destination) on the screen/page. The magic str method exists, and if used to print an object, it will print the name of the class (eg. Destination) and some information about it by default. We might only want to display the name of a destination, we can modify the functionality of the magic str method when it is called. To do this, add the following code. to both the Destination and Class methods in the models.py file:
def __str__(self) -> str:
return self.name
The full code in the models.py file should now look like this:
We will explore using this magic method when we query our database in the next lesson.
Next lesson: Querying an SQLite database