Search code examples
phpmysqlmulti-user

Multiple user accessing single table

I am looking for some general advice. I currently have a database (using PHP, apache and MYSQL) that runs as such:

  1. User uploads data to the DB, stored in table 1.
  2. User uploads more data to the DB, stored in table 2.
  3. User runs a query for the two tables to be cross referenced and the result stored in table 3.
  4. Table 3 is processed and the resulting information is stored in table 4.
  5. Tables 1, 2 and 3 are deleted (ready for the next upload/cross reference).

The details of how the DB works is academic. The problem I have is that there can be multiple users of the DB logged in at any one time. This leads to the problem that if one user is currently processing data in table 3 (Step 4) and another user runs the cross referencing query (Step 3) the information being used by the first user will be overridden by the new data. Table 4 is a 'cache' of results that is constantly growing so shouldn't be affected by multiple users adding to it at once (the table uses a auto_increment field so there should be no issues of duplicate records).

Does anyone have any general ideas on how this could be accomplished? The site needs to allow for up to 50 users (ideally) with around 5-10 on at anyone time.

Creating a separate table for each user seems like a waste of space and effort (not to mentioned updating them all with new code!). I thought of creating 'one time use' tables (perhaps linked off the users login name?) that would be create as the files are uploaded, used and then deleted once it has all been complete. Is this good practice or would this lead to further issues.

This is the first website/database I have deployed so I'm not too sure about good practices/pitfalls. Any advice would be greatly appreciated.

Many thanks

Solution

  • Why dont you add a column for UserID or UserName that references users table and that way you will have multiple rows in that table but identified by users. Make sure to add index to userID column.

    Also you should be using InnoDB engine since it uses RowLevel locking instead of TableLocking which MyISAM does.