0

I'm in a planning phase of developing a web site where public users will be able to store their documents, by nature it could be anything like documents, pdf, audio, video or images.

We are expecting large number of files stored in the database like numbers could be a few millions on average file size of 10 Mbs of one file.

I want to know a direction the way i should start learning/thinking on, for example in above case how many files one table should contain and how many such tables should be contained by one database.

What are the best practices while dealing with such scenario. What are the major things i should be taking care of while working on such project, I can use MS SQL Server database, My SQL is not a priority unless it has some advantages (its all based on available resources).

5
  • 6
    Generally files are stored on a file system, not in the database. The database holds information about those files such as ownership, visibility, and metadata. This allows the file system to do what it’s good at, and the database to do what it’s good at. This also makes backups and scalability much, much simpler to manage.
    – matigo
    Sep 2 at 22:13
  • As users will be restricted to access only those files they have access to, if i put files in folders do you think its safe and file system will allow millions of folders containing files? thought it looks simpler approach to save on file system.
    – sairfan
    Sep 2 at 22:25
  • 1
    Ideally people would not have direct access to the file system, otherwise you may as well just set up a really big SFTP system. You may be interested in looking at how services like Dropbox operate, as the files are stored outside the database and have a multi-layer permissions system.
    – matigo
    Sep 2 at 22:51
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Sep 3 at 5:59
  • I wonder, why web application/content management like SharePoint and Sitecore store media in database (at least what I understand)
    – sairfan
    Sep 3 at 15:55
2

Matigo is correct. A file system is designed to handle storing files best, a database system is designed for storing raw data best, not files. You should use the right tool for the right job.

There are multiple issues you can run into with storing files directly in the database, such as bloated backups which waste space and take much longer to recover with when needed. Also database performance issues can occur either at the query level (because your rows are split across multiple pages to support large objects like files) or even at the database level as a result of contention by the constant growth of the database data file.

There are a multitude of other reasons why it's generally not recommended to store files directly in the database either. One good alternative recommendation is store the files directly on a file system somewhere, with the link to their file path stored in a meta-data table about the files in the database.

Finally, you may find this Brent Ozar article Store Files in a File System, Not a Relational Database. informative on the matter as well.

1
1

(In addition to J.D's answer.)

A file bigger than 16MB is difficult to put into a database table; a file bigger than 4GB (a long video) is almost impossible to store -- you must chop it up and be able to put it back together; that is a big mess.

If the files will be fetched from a web page, then the mechanism for <img src=path-to-fil> is really tuned to reaching for a file, not a database entry. (Yes a db can be made to work, but it is clumsy.)

I recommend you store the path to the file in some db table. Don't store the full path; instead, start after some common starting point in the path. This lets you more easily move the documents to another filesystem or even a different server.

For that matter, put all the documents on one server; put the database on another. Again, <img> is quite happy to handle that.

1

As said, file system is for storing files. Name your files for example with a 16 char pseudorandom (use any hashing algorithm) hex string, then make a directory structure where you have 256 directories, 00 to FF, and under each, again 00 to FF, then place files in this hierarchy using file name's first 4 characters. This way, file named 0123456789ABCDEF.jpg will be stored in root/01/23/0123456789ABCDEF.jpg

This way, you can mount more partitions later to hold part of the files and directory size stays reasonable. If number of files grow, add a 3rd level of directories. Your web server can serve needed file from right directory with a simple rewrite rule.

You can add table or two in your database describing those files: original filename, user who uploaded it, copyright data, descriptive text, keywords, media type etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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