Best practices: Good idea to create a new table for every user?_问答_开发者

I'm currently writing a little django app to get some practice with the framework. The app lets user log in, write entries and see a list of their entries. How should I assign the entries to the user that created them? Is it a good idea to create a table for every new user and save the entries there or should I just add an additional field in the entry model (e.g. 'created_by') and filter the items to be displayed in the list accordingly?

One thing thats need to be considered, is that there should be absolutely no way that a user sees entries other than his own (e.g someones uses the app to write a diary). Is this given with both ways?

I've newer really worked with databases before, so I would appreciate an explanation why one way is bet开发者_JS百科ter than the other.

Based on your requirements, having a different database table for each user would make things way more difficult, and it wouldn't be worth the trade-off. As one example: in the "one table per user" scenario, when you go to retrieve the information for a user, you have to figure out what the name of that user's table is. I'm not even sure how you go about doing that, since the information about a user is stored in the table itself. (Ignoring session storage.)

An even bigger headache comes when you try to store the journal entries in their own table, and you want to maintain referential integrity. That is, you want to ensure that each entry belongs to a user that actually exists. That becomes almost impossible with a table for each user.

It's easy to use one table for users, one table for entries, and to link the two without any large, gaping security holes. Your "created_by" link is the way to go. A view function to load a page can easily constrain the user so they only see their own entries. Here's such a view:

@login_required
def my_entries(request):
    user = request.user
    entries = Entry.objects.filter(created_by=user)
    # return response here...

The @login_required is a decorator that requires the user accessing the page be logged in, and the .filter() call on the Entry model will only load those entries that were created by the user who is loading the page.

Now, this list might link to an 'edit' page for each entry. The URLs for each page will probably have a unique identifier in the URL, which is usually the ID field. So the first entry created with automatically get an ID of 1, the next one will get an ID of 2, and so on, so there's something unique to identify each entry. So URLs might look like '/entry/1/', '/entry/2/', etc. When the page loads, it checks the ID in the URL. If it's '1', it loads the entry with the ID of '1' for the user to edit. (Sorry if you already know that part.)

But, what that means is, a more savvy user might figure out how the URLs are formed and start putting in their own IDs, as a means of scouting for other people's entries. I could just start entering URLs with random ID values, until I find one that loads: '/entry/8/'. Maybe I don't own the entry with an ID of 8, but in theory, if things are set up correctly, I could load it.

There's some pretty easy ways to thwart this. When you write the view for loading a single entry, don't just load the Entry instance by its ID...load it by its ID and the user it was created by:

@login_required
def get_entry(request, entry_id):
    user = request.user
    entry = Entry.objects.get(id=entry_id, created_by=user)
    # return response here...

In the above case, if I tried to load this page for an entry that exists, but that doesn't belong to me, an exception will be raised. There's actually a helper method in Django called 'get_object_or_404' that helps with this:

@login_required
def get_entry(request, entry_id):
    user = request.user
    entry = get_object_or_404(Entry, id=entry_id, created_by=user)
    # return response here...

Now, if I try to access the page for an Entry instance that exists, but isn't mine, I'll just see the typical "Page Not Found" error that Django would offer if I tried to access a page that didn't exist.

I know your question was about the user database tables, but I hope this helps you configure Django so that your users aren't reading/editing each other's data.

No, a table per user is the exact opposite of best practice. Normalize the data by adding a 'created_by' column or similar.

Why not create a table per user? Creating a db table per user is definitely not recommended. How would you create a table for every user? Raw SQL statements? As far as I know, you would break the main purpose of Django, it's ORM (object relational mapper) -- it's easy interface to SQL because it doesn't support automatically creating tables per user / knowing how to query it.

Definitely, definitely use a table with a ForeignKey to the User table. Every table has a Primary Key field which is generally a number. One table can refer to another table via that Primary Key.

For example Entry 1 belongs to User ID 3.

Luckily, django abstracts the SQL layer and this is done via the ForeignKey field.

http://docs.djangoproject.com/en/dev/ref/models/fields/#foreignkey

As you are the one constructing queries for the database, only showing entries written by a specific user is a non-issue. You would simply only query the database for entries by the logged in user. As long as there are no loopholes for raw SQL to be input from outside your program, there's no way a user can access others data.

To learn about best practice / ForeignKey's / querying etc, I suggest following the tutorial online. I think the documentation and tutorials are one of django's greatest assets.

http://docs.djangoproject.com/en/dev/intro/tutorial01/

In the first tutorial they cover creating a Poll app, which includes a Choices model with a ForeignKey to Poll

You can definitely apply this back to your model : )

Well you have to have a field in your model which would hold the userid. ( This means one table. Creating a table per person isn't a good idea.) Then you'd have to apply some permissions so that that user may only see what he has composed and other people can not see what he has written.

These links are very helpful regarding row level permissions.

http://code.djangoproject.com/wiki/RowLevelPermissions

http://djangosnippets.org/snippets/1054/ This one is the one I used and found very helpful.

http://code.djangoproject.com/wiki/RowLevelPermissionsDeveloper

Like the others have said, even trying to create a table per user in django would be difficult.

Generally when working with relational databases, if you try the one table per user, you'll only try it once. :) Imagine if you started scaling to a few hundred users and now you have a requirement to get a count of all entries. You want to do 300 queries for that? Maybe a massive query with 300 UNION ALL statements? Not only will performance suffer, but you'll get tired of maintaining the app as well.

Because you haven't worked with databases much, you might want to read up a little on the relational data model a little bit. You could pick up a decent book on it, Database Design For Mere Mortals or maybe a tutorial online like: http://www.15seconds.com/issue/020522.htm. Get some background on RDBMS design and then come back to the django tutorial and it will make sense.