A re-introduction to one of the most common database relationship: understanding many-to-one.
In database design many to one (or one to many) refers to a relationship between one or more entities where a single entity can have many entities connected.
Let's make an example. Consider two entities: Mother and Daughter. As we know from real life, these two entities are most of the times connected like so:
- Every Daughter has one Mother
- One Mother can have many Daughters
So we have a many to one here where many is the Daughter and one is the Mother.
In an Entity Relationship Diagram this relationship is described with two boxes connected by the popular crow's foot:
On the Mother side you can see two lines which stand for one and only one. That's the "one side" of the relationship. On the Daughter side instead you can see the crow's foot and a circle.
The circle means optional, that is, there could be zero, one, or many Daughters. Technically a Mother should have at least one Daughter to qualify for her status, but I wanted to show you the circle and I took this liberty.
A crow's foot with a straight line on top instead means at least one, or many, that is, the many side of the relationship must not be empty.
Now, after modelling your database with an Entity Relationship Diagram it's time to write actual SQL instructions. But these days you don't want to do that by hand.
Nevertheless it's important to have a basic knowledge of how many to one is implemented in practice. Let's see.
To wire up two entities in a many to one relationship we need at least two tables in the database. Given Mother for example we'll have a mother table (examples are shown in PostgreSQL dialect):
CREATE TABLE mother ( id smallserial NOT NULL PRIMARY KEY, first_name varchar(254) NOT NULL, last_name varchar(254) NOT NULL );
How about Daughter? This is the "many" side. Of course we can create another table like we did with mother:
CREATE TABLE daughter ( id smallserial NOT NULL PRIMARY KEY, first_name varchar(254) NOT NULL, last_name varchar(254) NOT NULL );
But now, how are we supposed to connect the two tables? This is done with a foreign key. A foreign key is what the name says: it's a key pointing from one table to another (the foreign). Before adding it let's drop the table and create a new one with a column named mother_id:
DROP TABLE daughter; CREATE TABLE daughter ( id smallserial NOT NULL PRIMARY KEY, first_name varchar(254) NOT NULL, last_name varchar(254) NOT NULL, mother_id integer NOT NULL );
mother_id will hold a reference to the corresponding mother for each daughter. At this point we're ready too add the constraint:
ALTER TABLE daughter ADD CONSTRAINT fk_mother_id FOREIGN KEY (mother_id) REFERENCES mother (id);
With this query we say to the database: add a new foreign key to the column mother_id so that it references the column id in the table named mother.
With this knowledge let's now focus our attention to many to one in Django.
You'll need to create a new Django project and a Django app named address_book before testing things out.
To illustrate many to one in Django let's make another example. Consider two entities: User and Contact. For these entities we say that:
- Each Contact has one User
- One User can have many Contacts
Think of an address book where there are many users identified by a nickname, and each user can have many contacts in its address book:
joel89 has email@example.com, firstname.lastname@example.org, email@example.com in its address book
jules84 has firstname.lastname@example.org, email@example.com, firstname.lastname@example.org in its address book
and so on. Translated to an Entity Relationship Diagram it becomes:
Now to create these entities (models) in a Django app we can use
django.db.models. We create two models, each one with the appropriate fields:
from django.db import models class User(models.Model): username = models.CharField(max_length=150) class Contact(models.Model): email = models.EmailField()
Next up after registering the app in Django settings we run
python manage.py makemigrations and
python manage.py migrate to create the new tables in the database.
The migration will create two tables, yet not connected. Time to add a foreign key!
To wire up the two entities so that many Contacts are connected to a single User Django offers
ForeignKey. It is a field that you can add in your models:
from django.db import models class User(models.Model): username = models.CharField(max_length=150) class Contact(models.Model): email = models.EmailField() user = models.ForeignKey(to=User, on_delete=models.CASCADE)
Here we add a new column named user referencing the User model with a
ForeignKey. Make sure to run
python manage.py makemigrations and
python manage.py migrate to apply the change.
It's important to note that
ForeignKey takes at least two arguments:
user = models.ForeignKey(to=User, on_delete=models.CASCADE)
to describes the entity we want to point to.
on_delete instead describes how the database should behave when the "one" side of the relationship is deleted. When the "one" entity is deleted, with CASCADE the "many" entities are deleted as well.
Another things to note is that Django does things a bit differently from other frameworks. Some frameworks allows you to have a one to many relationship where the "many" side can be defined in the "one". That's the case with Laravel one-to-many for example.
In the end the outcome is the same: the "many" table will always be connected with a foreign key to the "one".
Now we're ready to make queries with Django's ORM.
To test things out enter the Django console:
python manage.py shell
Next up import the two models, User and Contact:
>>> from address_book.models import User, Contact
Now populate the database with a User:
>>> jules84 = User.objects.create(username="jules84")
and create a bunch of Contacts related to that User:
>>> Contact.objects.create(email="email@example.com", user=jules84) >>> Contact.objects.create(email="firstname.lastname@example.org", user=jules84) >>> Contact.objects.create(email="email@example.com", user=jules84)
The way it works when creating a new Contact is that by passing User as an argument for
create we bond the two entities together. With these entities in place we're now ready to make queries.
To access a User from our database we can run:
If there's just one User like in our example we can run:
Now, how about fetching every contact related to that user? With the Django ORM we can use a so called lookup that goes under the form
.related_set where related is the name of the entity we want to access.
So to fetch all the Contacts connected to our User (User has many Contacts) we can run:
This query gives back:
<QuerySet [<Contact: firstname.lastname@example.org>, <Contact: email@example.com>, <Contact: firstname.lastname@example.org>]>
There's also a way to do the opposite: from a Contact we can get back to the related User (Contact has one User):
This query gives back the corresponding User for our Contact: