Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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)?

share|improve this question
    
Would depend on a few more details. What search features would the users need on those tables? How much data do you expect for each user? –  thorsten müller Apr 22 '13 at 10:23
    
@thorstenmüller on the users tables there will be many SELECT requests. And the data for each user is theoretically unlimited and can be big. –  HasanAboShally Apr 22 '13 at 10:26
2  
Are the users tables related to each other so you would need id fields and JOIN queries? Otherwise, if it's mainly text storage, I would consider a NoSQL database. How "different" are the user tables? Can you limit the number of fields in a single table? (In which case you could have a single table with user_id and the max number of fields and just "map" fields named like entry1, entry2 etc to the users field names). –  thorsten müller Apr 22 '13 at 10:36
add comment

4 Answers

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.

share|improve this answer
    
Users don't have direct access to the database anyway (The application has access and the user is just a record in a table.), so why create one for each user? There's no security gain. –  JeffO Apr 22 '13 at 12:33
    
@JeffO And what if a user did get a hold of the database user which you use? Suppose he reverse engineers your code and spots the administrator username and password as well as a link to the database itself? You literally give access to your data as well as the data of your users to a complete stranger. Part of good security is never assuming that one failsafe is enough. It's good practice anyway, since you can also know who accessed what and when. –  Neil Apr 22 '13 at 12:37
add comment

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'.

share|improve this answer
2  
Worth mentioning that EAV can be a major resource hog, as there's no real way to rely on the DBMS's ability to index data. I've seen good implementations and terrible implementations, but even the good ones tended towards being painfully slow. –  Stephen Orr Apr 22 '13 at 12:31
add comment

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.

share|improve this answer
add comment

You can create tables by users like which user create tables append her name at start of the tables e.g john_emp; john is the user who created the table and emp is the actual table name by this approach you can figure out the who created the table or you can say you develop the group by users' table.

share|improve this answer
    
this post is rather hard to read (wall of text). Would you mind editing it into a better shape? –  gnat May 18 '13 at 21:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.