Create a database for database tables

Question

I'm working on a project where I have to give users the ability to build their own tables, each user can create more than one table.

(the type of all data is string).

The user can create a table and specify what columns he wants inside it.

What is the best way to do that:

1. to create databases dynamically

2. to have a table for tables (key,value ...).

3. something else

Another issue is: is it better to separate my database into two, so one is for the internal use (users, accounts,....) and the other is for the tables of the users (or for the table of tables)?

Answer 1

I think the most sane approach would be to have a database for each user. Most databases are built to handle security much better if one user has power over its own database and nothing else (you're going to want to use a special back-end user to be able to create the database, create the user, and assign permissions for that user to access that database -- anything else is a security risk).

Each user will have his or her own database with permissions to modify only that database. This makes everything far more straightforward. You no longer have to turn everything virtual since most problems on the database you can simply forward onto the user. You would require a master database which keeps track of users, permissions, and their databases. Many databases offer meta information, but I wouldn't recommend using it, at least not for users, since any extra information you would have to manage yourself anyway, and so you're likely doing what you'd have to do eventually anyhow.

One thing you should not have to save in master at this point is a table of tables -- This, you really should leave to the database to manage. If you require a list of tables, you can query the meta for this info.

At this point, your only true concern is database name conflicts. To solve this, you could do one of several things:

1. Provide the name yourself. It's going to be an ugly name, and this isn't the most popular decision for users, but it's the easiest to do.
2. Let them provide the name, and you perform some sort of mapping of the virtual to actual name that you create yourself. This is simple enough, with the exception that you need to parse queries and substitute the name yourself (I don't recommend it!).
3. Append the username on the left-hand side of the database name that they pick so there's no possibility of conflicting names (use some sort of character divider which cannot be used in the database name in order to not have to worry about user "me" with database "atloaf" and user "meat" with database "loaf" creating the same name).

This is obviously a database-heavy solution in that you rely mostly on the database engine to carry the logic behind what you can and can't accomplish, therefore I highly recommend that you know how to maintain the database (security being a major priority here) that you do ultimately pick.

With time, you can override certain functionalities if you require certain behaviors which you can't obtain normally with a database, but this approach is certainly advantageous in that you already have a lot of the logic completed for you with little or no tweaking.

Answer 2

How many users are you expecting? I would advise against creating new tables for users. This could create a very large database rather quickly.

Take a look at the EAV (Entity–attribute–value) model. This is quite flexible. http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model.

This way, you can have one table that stores the users 'tables'.

Answer 3

I would just have one table for users and a one table for user_columns.

The whole notion of users dynamically adding tables is not one I'd tackle.

The user_columns table would have user_id as a non-unique foreign key, plus columns such as description, timestamps, etc.

That way you just add and remove rows from user_columns as needed.

Answer 4

You should prefix the table with user name like userName_tableName pattern. the userName will be the name of user who created the table.