1

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

Improve this question
1
  • 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. Commented Oct 8, 2012 at 0:00

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.