[Django]-How to create new tables in Database for each day in Django

by

7đź‘Ť

âś…

The short answer to the question you asked is “Yes, the quickest method would be to use raw SQL,” or a variant being dynamically create new models and run makemigrations.

That said, in the absence of more specifics about your problem or task, I must immediately question why you want to dynamically create tables. For starters, that will lose you the nice Django-provided ORM connections to the schema without more trickery (automatically generated models, daily makemigrations, potential misplaced data during migration, specialized code on your part, etc.). Some thoughts:

  1. For almost any web-based project with even minimal capacity, 2000 rows is almost nothing. Even per day. After a full year, that’s only 730k rows. Assuming proper schema indexes and normalization for your access pattern, that is still considered negligible to an RDBMS.
  2. If you truly need to plan for sharding, consider maybe doing it at the annual or decadal level. 730K * 10 years is only 7.3M rows, which is generally still considered in the realm of negligible (given proper normalization and indexes).

If the antecedent to this question is that you are encountering speed issues, then I suggest you need to think hard about your data model’s normalization, and think about where you need to add indexes. For example, if your default data model does not afford the proper indexes, then use @lodb’s (quick fingers!) suggestion and manually tell Django’s ORM to create indexes:

class Meta:
    indexes = [
        models.Index(fields=['day',]),
    ]
👤hunteke

5đź‘Ť

Honestly, a table per day is not the best way to go in Django as it would make the whole framework a lot harder to use:

  • you would need to create a new model every day
  • you would need to makemigrations and migrate every day
  • or you would skip using the ORM altogether

If speed is the reason why you would keep a table per day, I would just use one table, with a database index on the day-field. It might be a little bit slower, but it will make your life a lot easier.

You need to add this to your model-definition:

class Meta:
    indexes = [
        models.Index(fields=['day',]),
    ]

The manual explains it in more detail: https://docs.djangoproject.com/en/1.11/ref/models/indexes/

👤lodb

Leave a comment