I have a database table that contains id, filename, userId

• id is unique identifier
• filename should also be unique
• table may contain >10000 records

When a user uploads a file it should be entered in database with given rules:

1. If there is no record with same filename, it should be added as it is (Ex. foobar.pdf)
2. If there is record with same filename, it should be added as uploadedName(2).ext (foobar(2).pdf)
3. If there are n records with same base filename (foobar), it should be added as uploadedName(n+1).ext (foobar(20).pdf)
4. Now if foobar(2).pdf is uploaded, it should be added as foobar(2)(2).pdf & so on

This pattern needs to be followed because the file is already being uploaded at client side using ajax before sending the details to server and the file hosting service follows the above rules to name the files.

My solution:

1. maintain a file that contains all the names and the number of times it has occurred.
2. if a filename that exists in file is entered, increase occurrence count and new name is generated, else add to it to file
3. if the new name generated is in database, add it to file and generate new name