4

Assumptions: Microsoft stack (ASP.NET; SQL Server).

Some content management systems handle user-generated content (images, file attachments) by storing it in the file system. Others store these items in the back end database.

Some examples of both:

  • In the filesystem: Community Server, Graffiti CMS
  • In the database: Microsoft Sharepoint

I can see pros and cons of each approach.

In the filesystem

  • Lightweight
  • Avoids bloating the database
  • Backup and restore potentially simpler

In the Database

  • All content together in one repository (the database)
  • Complete separation of concerns (content vs format)
  • Easier deployment of web site (e.g. directly from Subversion repository)

What's the best approach, and why? What are the pros and cons of keeping user files in the database? Is there another approach?

I'm making this question Community Wiki because it is somewhat subjective.

Tim Long
  • 13,508
  • 19
  • 79
  • 147
  • You pretty much answered your own question, and neither is ultimately better. It depends. – tdammers Oct 08 '10 at 15:56
  • For what version of SQL Server? 2008 added a third option, [FILESTREAM](http://technet.microsoft.com/en-us/library/bb933993.aspx), but I can't find a page comparing it to DB & file storage. – OMG Ponies Oct 08 '10 at 16:00
  • Let's assume SQL 2008 R2, so FILESTREAM is a valid comment, but I haven't had a chance to even look into what it does yet. So much new technology, so little time... – Tim Long Oct 09 '10 at 23:24

4 Answers4

1

If you are using SQL Server 2008 or higher, you can use the FileStream functionality to get the best of both worlds. That is, you can access documents from the database (for queries, etc), but still have access to the file via the file system (using SMB). More details here.

Erick

Erick T
  • 7,009
  • 9
  • 50
  • 85
0

I picked the file system because it made editing of documents in place easier, that is when the user edits a file or document it can be saved in the location it is loaded from with no intervention by the program or user.

Decker97
  • 1,643
  • 10
  • 11
0

IMO, as of right now with the current functionality available in databases, the file system is the better choice.

  1. The file system has no limit on the size of the files and with content this could easily be files larger than 2 GB.
  2. It makes the database size much smaller which means less pressure on memory.
  3. You can design your system to use UNCs and NASs or even cloud storage where as you cannot do this with FILESTREAM.

The biggest downside with using the file system is the potential for orphaning files and keeping the database information on files in sync with the actual files on disk. Admittedly, this is a huge issue but until solutions like FILESTREAM are more flexible, it is the price you have to pay.

Thomas
  • 63,911
  • 12
  • 95
  • 141
0

Actually its door #3 Chuck.

I think storing images in the database is bad news unless you need to keep them private, otherwise, just put them on a CDN and store the URLs of the images instead. I've built some huge sites for ecommerce and putting the load on a CDN like Akumai or Amazon Cloudfront is a real nice way to speed up your website dramatically. I'm not a big fan of burning your bandwidth, CPU and memory for serving up images. Seems a silly waste of resources now days since CDNs are so cheap. Also, it does allow deployment to not care because your stuff is already in a globally accessible region. You can take a look at my profile to see the sites I've done and see how they are using CDNs to offload static requests. Just makes sense and gets even better if you can gzip it.

King Friday
  • 25,132
  • 12
  • 90
  • 84