Forums

I hope I can create database dynamically. Can I do it?

Looks like I can only create database from the web databases tab. am I right ?

I'm also struggling to decide which way I should go?

Create multiple databases, each database for each of my user; or only create one database, but create multiple tables , each table for each of my user; or only create one database and one table, but insert username into the table columns.

Can anybody give an advice? Definitely I have to care security between different users. Thanks a lot.

Hi hyin,

Of those options, I suspect you definitely want the last one -- one database, one table, and then manage your users using rows.

If you've never worked with web development and databases before, I would suggest using one of the Python web frameworks, and following their tutorial. Django is probably the most popular, and their tutorial is excellent:

https://docs.djangoproject.com/en/1.3/intro/tutorial01/

Django 1.3 is already installed by default. Start a new Django app on the web tab to get the "Welcome to Django" screen, and you can use PythonAnywhere instead of manage.py runserver

I'm assuming you're talking about MySQL/Postgres here? If you're talking about SQLite, you can create as many databases as you like, since each one is just a standard file.

For the others, I don't believe it's possible for web apps to create databases - as you've mentioned, you have to go to the Datbases tab of your Dashboard and create them there. Once created, web apps should have full permissions to create and manipulate whatever tables they like.

This is fairly standard - many web hosting providers don't even let you create databases, they force you to use a single one that they create for you. This isn't typically a problem as web apps are usually shipped with a schema file which you run through the command-line mysql client and which creates the schema in whatever database you specify. Then you usually set a configuration setting in the web app to specify the database name, along with the MySQL hostname, etc.

In terms of your schema, I would certainly not recommend a database per user - that's really overkill. To be honest I wouldn't even suggest a table per user. The standard way to use a relational database is to have a set of tables which don't change except on upgrades to the software, and each table has a row per record.

What you want to go for is a normalized schema where you never duplicate data between tables - instead, you link them together by using unique IDs to identify rows in each table. If there is data which is always unique to a user, you can just put it as additional columns in the users table. If there is data which may be shared between users, such as the country they live in, you'd instead have a separate countries table, each row of which would have an ID and the users table would have a country column which would contain the ID of the row in the countries table applicable to that user. Read the Wikipedia article for background on the advantages of this approach, but it's definitely standard practice for relational databases like MySQL.

Security isn't an issue directly because users should never have access to the database directly. As long as you write your application to be careful about the data it returns to them, there shouldn't be an issue putting multiple users in the same table. One thing you must always do when accepting input from users (usernames, passwords, emails, anything) is protect against SQL injection. This typically involves passing the input through a function which will escape the SQL before executing the statement. A quick search turned up this presentation about it in Python, which you may find useful.

EDIT

Given that I started writing this post 2 hours ago when my baby daughter woke up and needed feeding, I probably shouldn't be surprised that Harry got there first! (^_^)

I'll leave this post intact because some of the links may still be useful.

Hi Guys, thank you so much! Looks like I need to change my current database structure.

I have an application which was running offline now, I'm thinking how to move it to online and share with anybody. The current structure is one database per user, user have full access to their own database. (currently I have only no more than 5 users). And, the application just send SQL sentences to database directly.

Originally I don't want to make big change to my current application, looks like I have to do it now. if put user name into table, then It means all SQL sentences needed to be added user name in it. that would means more than 100 places needed to change.

Thanks again!

If you have to change a constant in 100 different places then what that is actually saying to you is that you have a lot of repetition in your code. If you had one function or class that all your DB queries went through then you would only be modifying it in one place. Any time you see repeated code you should think about how to rewrite it in such a way that you get rid of it. You might want to read about the the DRY principle.