Prefixing MySQL Tables or Many MySQL databases?

Question

So, first things first, I'm a student. I'm developing an application where other students can have access to a MySQL database. Basically, I wanted to spare the students the need to search for hosting or even installing MySQL on their computers. Another plus is the fact that they can present their works to the class just by browsing a website. So, my idea was to use the same database for everyone, and add a login system for the students. This way, I can associate a prefix to every student, and they can execute any type of query without worrying if it will clash with someone's table, because the system would prefix their queries tables automatically. My idea was to limit how much tables and rows each user can have, which shouldn't be hard with a parser. It doesn't necessarily need to be a parser in PHP, it could be in perl or python. PHP is just more convenient. .NET would be more troublesome because of Windows

By the way, each class of "introduction to database systems" has around 50 students and there are 3 classes, so it could reach about 150 students...

For example, SELECT * FROM employees has to become SELECT * FROM prefix_employees I do not know how the query will look like, it could get fairly complex so I'd probably need a well written parser, which I haven't found yet for PHP.

Thanks guys, I hope I have made myself clear

Answer 1

Unfortunately, MySQL does not (AFAIK) have schemas as some other databases (e.g. PostgreSQL) have them (for seperating content (tables, etc...) logically within one database).

But I would definitely go for the seperate databases-scenario.

Your parser (with the 'prefixing sheme') will be broken (unwillingly and also possibly willingly) unless you are willing to put an extreme amount of time into making this work.

Answer 2

I'd rather go with the "one database per user" approach. This solution requires some administration (you can either create the users/databases manually using a tool like phpMyAdmin, or simply create your own little administration panel in which you allow the students to register), but will require far less amount of work from you than filtering all requests.

This way, each student has his login/password, with preferably a database of the same name on which he has all rights (this can be done automatically with phpMyAdmin), and is able to work without interferring with other students. You can be sure that some will try to break your security, no matter how hard you try and how good-willing you are. Clustering them in different databases will leave them no choice than trying to gain admin access of your DB, which will be pretty hard if you maintain an up to date server and complex enough passwords (and you don't store them in clear on a "readable by all" .txt file on your university server.

Plus, you will be able to monitor the disk space, usage, etc... of each database individually, which is easier than having to look at tables separately.

Answer 3

Depending on your exact requirements, you may be able to use table permissions to prevent one student from modifying (or viewing) data from another student. You would still need a process to allow students to create a new table with their assigned prefix (and create an appropriate permissions entry), but once created, the DB would control access through all queries so you would not have to (just don't allow student accounts to directly create/alter tables).

As for quota, I'm not aware of MySQL directly supporting a quota system but you could create the files that back the tables for each user on a separate directory and use OS level quota systems to limit disk space usage.