CODEWITHSUNDEEP
djangodjango-modelsmultiple-tables
Manuel
Manuel

Reputation: 21

Multiple Tables for one Django Model

How can I dynamically create multiple tables for one specific model?
I have a model (e.g. "Data") and want each user to get a separate database table for this model.

There will be between 30 and 40 users, each with about 20 million entries in "Data". So that the database table does not get 800 million rows, I would like to split it.
Of course, it is also possible that there will be more in the future

Is this still possible with Django ORM?
I'm on PostgreSQL

Thanks :)

Upvotes: 1

Views: 1464

Answers (1)

Naym
Naym

Reputation: 81

For a strict requirement, I had to go through this. I will just leave my solution here for future reference. After User creation, let's say we want to create a "user_data_100" table with model "UserData" with pgsql database. Note: here "100" is the "User" model(table entries) primary key value.

in app/model.py create the user model.

from django.db import models
from django.db.models.signals import pre_save, post_save
from django.dispatch import receiver 
from django.db import connection
from django.contrib.auth.models import User


class UserData(models.Model):

    class Meta:
        pass

    dt_column_one = models.CharField(max_length=10,)
    dt_column_two = models.CharField(max_length=5,)

def create_model_by_prototype(model_name, schema_name, tbl_name, prototype):
    import copy
    class Meta:
        pass
    setattr(Meta, "db_table", schema_name + "\".\"" + table_name)
    fields = {}
    for field in prototype._meta.fields:
        fields[field.name] = copy.deepcopy(field)

    attrs = {'__module__': "app.models.models", 'Meta': Meta}
    attrs.update(fields)
    model = type(model_name, (models.Model,), attrs)

    return model

Note: function "create_model_by_prototype" will generate a model with given table name.

Now let's use the function. I assume the user will get created from the admin site. Let's create a post_save function to create UserData table.

@receiver(post_save, sender=User)
def create_user_data_table(sender, instance, *args, **kwargs):
print('User post save')

if kwargs['created']: 
    user_id = instance.id
    dy_table_name = "user_data_" + str(user_id)
    model= create_model_by_prototype("MyUserData", "public", dy_table_name, UserData)
    with connection.schema_editor() as schema_editor: schema_editor.create_model(model)
else:
    print('updated')

hope this works for you. Thanks.

Upvotes: 1

Related Questions