Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Oct 8 '12 at 11:50

This question came from our site for professional and enthusiast programmers.

    
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. –  iouri Oct 8 '12 at 0:00

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

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.