3

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
Improve this question
3
  • 10
    Easier solution - don't use the filename as a key and expect it to be unique. Use a UUID to store and access the file, use HTTP headers to set the name while sending it.
    – Sean McSomething
    Oct 30, 2013 at 0:31
  • Yeah, that's a better solution all around. You can even keep the original file names in a separate field, if you want to.
    – Robert Harvey
    Oct 30, 2013 at 0:58
  • 3
    OP, Note that your proposed solution would require locking the file between checking and adding. Otherwise there'd be a race condition when 2 files with the same name are uploaded simultaneously.
    – Yam Marcovic
    Oct 30, 2013 at 4:54

1 Answer 1

Reset to default
2

If the original filename is not significant then do as others have suggested and generate a synthetic name -- a UUID would be a good candidate or use a sequence like your primary key. If the name is significant, i.e. you want to store it, then simply add a column to make it possible for the user to distinguish between duplicates possible and meaningful, and to reduce/eliminate duplicates. For instance, a timestamp indicating when the file was uploaded.

It's not entirely clear to me what you're asking -- your title has a question, but your post does not. If you're looking to update the column name of a table look into alter table -- it's simple. If you're looking for how to massage existing data then see above. Otherwise you'll need to rephrase your request so it's a bit clearer.

Anyway, hope this helps!

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.