0

In Django I am testing some architectures for a site where one of the features is that users will be able to make what essentially amounts to blog posts. My initial thought was to have a model called Post and fill that table with every post by all users, with an id to link which post is associated with which user. Would this be inefficient, as every query would have to search through a potentially massive table?

Alternatively, I had an idea that every time a user is created, add that user to the users table and then create a new table called something like Posts_by_<user-id>. Then instead of querying a massive table, the query finds the Posts_by_ table associated with that user, and has a much smaller chunk of data to parse. Is it more efficient to have a large number of small tables instead of one massive table?

So my questions here are twofold:

1) In Django, is it possible to generate a table in my database from a view.py file instead of models.py? 2) Is the above method an effective way of structuring the database, regardless of framework?

Hat
  • 1,691
  • 6
  • 28
  • 44

1 Answers1

2

It is possible, with raw SQL or using internal Django DB API. But this is not a good idea, that's why there is no officially supported way of doing this in Django.

Databases are designed precisely to give you fast access to your data. If you know you will often retrieve data by user id you should create index on that field - this will tell the database to create a separate data structure that will keep track of how to find rows for a particular user really fast.

In Django creating an index on a single field would look something like that:

user = models.ForeignKey(User, db_index=True)

Update: as btoueg pointed out in the comments Django does this automatically for ForeignKey fields. So you would need to do this manually only for other fields and when using other frameworks. Of course only when appropriate - you usually definitely wouldn't want to have an index on every field.

Ludwik Trammer
  • 24,602
  • 6
  • 66
  • 90
  • Django automatically create indexes for foreign keys http://stackoverflow.com/questions/5984842/django-does-it-automatically-generate-indexes-for-foreign-keys-columns – Benjamin Toueg Oct 23 '13 at 19:30
  • That's true. Django docs [would be probably a better reference point](https://docs.djangoproject.com/en/dev/ref/models/fields/#foreignkey). I usually know about that, but I think I got caught up in illustrating a bigger point. Which still steads. – Ludwik Trammer Oct 23 '13 at 19:34