Best structure for user-level versioning of data in mysql

Question

I have an application I'm building that allows users to upload data files and I read the contents of the data file into a database. All the data files have the same fields, but there may be slight differences between the data elements depending on the original source and\or user (differences in the values, not in the data types).

To respect this, I need every user to have a personal copy of the data in the database, so no user can read the data that another user uploaded. The files are translated into a number of tables with foreign keys relationships, etc..., so its not trivial.

I have thought of the following solutions:

• A single table structure with a user_id column in every table to keep the data specific to the user - The current solution, but I'm scared it may be a nightmare to maintain.

• A different copy of the table structure per user (so datatable_1 and datatable_2 for users 1 and 2) - simpler to maintain and segment, but could be a lot of tables and I'll need to create seperate views for each table strucure... just alot of objects. Also, table-level locking would prevent access issues when one user is uploading and another is reading.

• A different database for each user (db_1.datatable, db_2.datatable) - Even better structuring and segmentation, and fewer tables per database, but most pricing for web-supported applications is per database, so this may be a non-starter...

Any thoughts or other suggestions would be much appreciated...

• iouri: Number 1 is the proper way of doing it. Which parts would be hard to maintain? Tricky part is ensuring that there are no ways of accessing other users data, sometimes searches, or things of that nature create some security holes.

Answer 1

It is possible to use one-database-per-user or one-table-per-user models... But these will start to become unwieldy as the number of users in your application grows... MySQL (and, really, no RDBMS) handles hundreds of thousands of tables or databases very well.

Having an user_id (or similar) column on each table that contains user data is totally standard, and the "right" way to do what you're describing.