How would I structure database the best?

How to structure file server to work with database?

• I need advise on makeing a good site structure. I have folders that contain unique images uploaded by users that are referenced to database entries. Was told this can cause the server to crash as the number of folders grows. Is there a better solution to this? (it's a windows 2003 server) and mysql server. I believe the DB server is separate from the one hosting the site. Not sure how youtube is structured but it's kinda similiar. there are say 1mln users and so there'd be 1 mln folders on the server each named with the unique user id (as referenced in the users table in DB) so for example tehre are entries Userid 1 2 3 ... And on the server tehre are folders 1 2 3 ... Each folder referencing to the user id. And contains files uploaded by that user. Anyway the questions is that i was told this can cause the server to crash as the number of folders increases. Something about permissions being reset for all the folders once a new one is added. Any opinions? is this a bad structure? how can it be improved? I go with what i can afford atm. So i might switch to a dedicated server but that stil would not solve the problem (if there's any with this) I currently ahve 4000 such folders. Each one has couple of folders inside it and the support staff from the Hosting company said this is causing problems. Please advise.

• Answer:

  I don't believe that server crash is an issue here. NTFS file system can store millions of files in a folder, so you can ignore this. There will however be a performance issuce if your file naming is more than 8 characters long, simply because NTFS by default needs to regenerate a 8.3 filename everytime you create a file. Performance decreases as the number of files increases in a directory. Why are you storing files per user? This is not really necessary. Option 1 is to place all your files in 1 directory, as long as you have a way to guarantee that each file name will be unique. Option 2 is to create a folder per date ("2009-07-30"), then all files uploaded on that day will be stored there. Either way, performance should not be an issue. You should base your choice on flexibility for archiving data and migrating the information to another drive in case you run out of filespace. So, in your code, you could say "all files created after 2010-01-01 will be placed on drive F:". This is pretty simple to code and to implement. Hope this helps! Good luck!