12

I am working on a project which requires me to create a table of every user who registers on the website using the username of that user. The columns in the table are same for every user.

While researching I found this Django dynamic model fields. I am not sure how to use django-mutant to accomplish this. Also, is there any way I could do this without using any external apps?

PS : The backend that I am using is Mysql

Community
  • 1
  • 1
Archit Verma
  • 1,911
  • 5
  • 28
  • 46

2 Answers2

13

An interesting question, which might be of wider interest.

Creating one table per user is a maintenance nightmare. You should instead define a single table to hold all users' data, and then use the database's capabilities to retrieve only those rows pertaining to the user of interest (after checking permissions if necessary, since it is not a good idea to give any user unrestricted access to another user's data without specific permissions having been set).

Adopting your proposed solution requires that you construct SQL statements containing the relevant user's table name. Successive queries to the database will mostly be different, and this will slow the work down because every SQL statement has to be “prepared” (the syntax has to be checked, the names of table and columns has to be verified, the requesting user's permission to access the named resources has to be authorized, and so on).

By using a single table (model) the same queries can be used repeatedly, with parameters used to vary specific data values (in this case the name of the user whose data is being sought). Your database work will move along faster, you will only need a single model to describe all users' data, and database management will not be a nightmare.

A further advantage is that Django (which you appear to be using) has an extensive user-based permission model, and can easily be used to authenticate user login (once you know how). These advantages are so compelling I hope you will recant from your heresy and decide you can get away with a single table (and, if you planning to use standard Django logins, a relationship with the User model that comes as a central part of any Django project).

Please feel free to ask more questions as you proceed. It seems you are new to database work, and so I have tried to present an appropriate level of detail. There are many pitfalls such as this if you cannot access knowledgable advice. People on SO will help you.

holdenweb
  • 33,305
  • 7
  • 57
  • 77
  • I omitted to mention that one of the columns in this table will be some form of user identity. If you know what a foreign key is then you will probably understand my suggestion to use a foreign key into the User table (model). If you don't, think of it as a username for now - there are reasons why you wouldn't use the username, but to a first approximation you can think of it that way. – holdenweb Mar 03 '14 at 06:52
  • Thank you for your help. My database design was definitely wrong. I'll restructure my database and let you know if I face any problem. – Archit Verma Mar 04 '14 at 12:01
  • Happy to help. I'll look out for comments. – holdenweb Mar 04 '14 at 12:19
  • I've had the same question, but maybe a better reason for it: One table (django model) – Sauer Mar 12 '19 at 08:29
4

This page shows how to create a model and install table to database on the fly. So, you could use type('table_with_username', (models.Model,), attrs) to create a model and use django.core.management to install it to the database.

Bip Lob
  • 485
  • 2
  • 6
  • 15
defool
  • 301
  • 2
  • 4
  • Do you mind me asking why the project requires one table per user rather than storing all user data in the same table with the username as an attribute? ("Yes" is an acceptable answer). It seems like the equivalent of using `setattr()` to create dynamic variables in Python, which is rarely the best way to solve a problem. – holdenweb Mar 02 '14 at 11:25
  • @holdenweb The project that I am working allows one user to follow another user so that he can view the other user's activity on the website. So, I was thinking of creating a table for every user by which I could keep track of all the users that follow a particular user. If you have a better way of doing it, then please share it. :) – Archit Verma Mar 02 '14 at 19:20
  • 1
    Make the user ID a key field in the table, so you can easily select the information about a specific user. Using a database per user is just wrong, and will lead to all kinds of problems. – holdenweb Mar 12 '19 at 15:01