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)

# models.py
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()
    # note: do not save password in plain text 
    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

# import your model 
# i.e from app.models import UserDetail, User
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  # reference to the user_detail instance created above
)

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',
)
# or to get already created object
# address = Address.objects.get(name='Lake Street')

user = User.objects.create(
    name='Django',
    email="django@example.com",
    password="django",
    address=address  # reference to the address instance from above
)

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',
)
# or to get already created object
# address = Address.objects.get(name='Lake Street')

# first create user object
user = User.objects.create(
    name='Django',
    email="django@example.com",
    password="django",
)

# add address to user/user to address
user.address.add(address)

# to remove address from user/user from 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)

# intermediate table
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

1. 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.CASCADE
   • models.PROTECT
   • models.SET_NULL and more...

2. 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:
     1. [name of class]_set for ForeignKey
     2. [name_of_class] for OneToOneField