Table relationships in Relational Database design
1. One to One relationship (1-1)
- A relation is said to be 1-1 if a row in
Table A can have only one matching row in Table B
Let's take an example of simple 1-1 relationship between user and user_detail table
Here, in this figure we can see that, a row from user table (Table A) can only reference a row (1 row) for user_detail (Table B) through user_detail_id
column on user table (Table A)
- Practically speaking, all data stored on
user_detail table (table B) could have been stored on user table (Table A) and we could have just ignored the relationship, but this relationship is quite useful when you have lot's of column in your table and you want to separate those columns in different table according to their business nature
- I basically work with this relationship to make my data model/table more readable
Let's implement (1-1) relation on Django (OneToOneField)
from django.db import models
class UserDetail(models.Model):
address = models.CharField(max_length=100)
phone_number = models.CharField(max_length=20)
date_of_birth = models.DateField()
github_username = models.CharField(max_length=50)
class User(models.Model):
first_name = models.CharField(max_length=50)
middle_name = models.CharField(max_length=50, null=True, blank=True)
last_name = models.CharField(max_length=50)
email = models.EmailField()
password = models.CharField(max_length=50)
user_detail = models.OneToOneField(UserDetail,
on_delete=models.CASCADE)
so using models.OneToOneField we can easily create up 1-1 relation between different models in Django.
You could have switched the relationship as well, i.e 1-1 relation from UserDetail to User
Basic ORM to create data/rows for this relation
detail = UserDetail.objects.create(
address="Lake Street",
phone_number='123456',
date_of_birth='2000-01-01',
github_username='django'
)
user = User.objects.create(
first_name='Django',
last_name='Django',
email="django@example.com",
password="django",
user_detail=detail
)
2. One to Many Relationship (1-M)
- One of the most common forms of relationships
- A relationship is said to be 1-M if a row in
Table A can have matching row in Table B but a row in Table B can only have one matching row in Table A
- Let's take an example of simple 1-M relationship between
address and user table
- Here, in the figure we can see that a row in
address (Table A) can be referenced by many rows from user (Table B). i.e a user can have only one address but a address can be of multiple users
- This can be simplified as
1 Address = Multiple Users but 1 User = 1 Address
- Experiment: Lets take an imaginary requirement of
1 User= Multiple Address and 1 Address = 1 User, in such a case, we can reverse this relation and call it Multiple Address = 1 User and call this relation a Reverse One to Many Relation as shown in the figure below
Now, there lies a big confusion on the 1-M and Reverse 1-M, because both looks same. Infact, both of them looks same, but the only difference occurs when you look the relationship from table point of view. i.e In the reverse 1-M figure, we named it Reverse relation because we looked the relation from address point of view, but if you had looked the relation from user point of view, then it would have been a normal 1-M relation
How you prepare your 1-M relation matters when you try to perform db query for the data
Let's implement (1-M) relation on Django (ForeignKey)
from django.db import models
class Address(models.Model):
name = models.CharField(max_length=100)
latitude = models.CharField(max_length=100)
longitude = models.CharField(max_length=100)
class User(models.Model):
email = models.EmailField()
name = models.CharField(max_length=50)
password = models.CharField(max_length=50)
address = models.ForeignKey(Address, on_delete=models.CASCADE)
Basic ORM to create rows for this relation
address = Address.objects.create(
name="Lake Street",
latitude='1.1',
longitude='2.2',
)
user = User.objects.create(
name='Django',
email="django@example.com",
password="django",
address=address
)
3. Many to Many Relationship (M-M)
- A row in
Table A can have many matching rows in Table B and vice versa
- A many-to-many relation can be defined as a one-to-many relation linked by an intermediate table
- Under the hood this relation is created using two
1-M relation. i.e 1-M and M-1
- Let's take an example of simple M-M relationship between
user and address table
- As you can see in the figure, the relation between
user and address is maintained by an intermediate table Table C, this way we can achieve a relation where a user can have multiple address and a address can have multiple user
Let's implement (M-M) relation on Django (ManyToManyField)
from django.db import models
class Address(models.Model):
name = models.CharField(max_length=100)
latitude = models.CharField(max_length=100)
longitude = models.CharField(max_length=100)
class User(models.Model):
email = models.EmailField()
name = models.CharField(max_length=50)
password = models.CharField(max_length=50)
address = models.ManyToManyField(Address)
This looks so simple, isn't it? But i can feel a big confusion. We discussed that a M-M relation would need an intermediate table but on the Django end we just created two models without an intermediate one, This is due to the awesome implementation of Django where, Django automatically creates an intermediate table when we define our field with models.ManyToManyField, but don't worry you can always tell models.ManyToManyField to point to some custom intermediate table or you can even implement M-M with just 1-M and M-1 relation without even using models.ManyToManyField
Basic ORM to create rows for this relation
address = Address.objects.create(
name="Lake Street",
latitude='1.1',
longitude='2.2',
)
user = User.objects.create(
name='Django',
email="django@example.com",
password="django",
)
user.address.add(address)
user.address.remove(address)
Implementing M-M without models.ManyToManyField
By using just 1-M relation and an intermediate table let's try to create M-M relations
from django.db import models
class Address(models.Model):
name = models.CharField(max_length=100)
latitude = models.CharField(max_length=100)
longitude = models.CharField(max_length=100)
class User(models.Model):
email = models.EmailField()
name = models.CharField(max_length=50)
password = models.CharField(max_length=50)
class UserAddress(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
address = models.ForeignKey(Address, on_delete=models.CASCADE)
Basic ORM for this custom way
address = Address.objects.create(
name="Lake Street",
latitude='1.1',
longitude='2.2',
)
user = User.objects.create(
name='Django',
email="django@example.com",
password="django",
)
user_address = UserAddress.objects.create(
user=user,
address=address,
)
Relation field Arguments
on_delete
As you can see we used on_delete argument on our OneToOneField and ForeignKey field, but what does it do?
- When an object/row referenced by ForeignKey/OneToOneField is deleted, Django will emulate the behaviour of SQL constraint specified by the on_delete argument
- e.g.1 You have a
ForeignKey and want to delete the row when the object referenced by foreign key gets deleted
from django.db import models
class Info(models.Model):
name=models.CharField(max_length=20)
user= models.ForeignKey(User, on_delete=models.CASCASE)
Here, The Info objects/rows gets deleted when the referenced user gets deleted on the User table
Commonly used Values for on_delete
models.CASCADEmodels.PROTECTmodels.SET_NULL
and more...
related_name
related_name attribute specifies the name of the reverse relation- If you do not provide
related_name Django automatically created related_name for every FK automatically, whose default value is:
- [name of class]_set for
ForeignKey
- [name_of_class] for
OneToOneField
