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