2

Background information:

This application is .NET 4/C# Windows Forms using SQLite as it's backend. There is only one user using the database and in no way does it interact through a network.

My software needs to save images associated to a Project record. Should I save the image as binary information in the database itself; or should I save the path to the picture on the file system and use that to retrieve it.

My concerns when saving as path is that someone might change the filename of a picture and that would essentially break my applications use.

Can anyone give some suggestions?interact through a network.

  • Your concern seems to answer your question. Bear in mind also that stored on a FS, the images won't get saved/restored with database backups – smirkingman Nov 03 '10 at 12:49
  • I don't understand what you mean with saved/restored. Are you implying that images saved on the database DO get saved/restored? I don't think SQLite supports database backups. –  Nov 03 '10 at 12:50
  • 1
    possible duplicate of [Storing Images in DB - Yea or Nay?](http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay) – ChrisF Nov 03 '10 at 12:52

3 Answers3

5

"It depends". If there are a lot of images, then all that BLOB weight may make backups increasingly painful (and indeed, may preclude some database implementations that only support limited sizes). But it works, and works well. The file system is fine as long as you only store the path relative to some unknown root, i.e. you store "foo/blah/blip.png", which is combined with configuration data to get the full path - then you can relocate the path easily. File systems have simpler backup options in some cases, but you need to marry the file-system and database backups.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Gravel - This is very good point. I actually skip storing file names in DB all together, whenever I can by using other DB fields to automatically construct the filename (e.g. using PK for filename) – zam6ak Nov 03 '10 at 13:17
  • @zam6ak - which is fine until somebody changes the data ;p – Marc Gravell Nov 03 '10 at 13:36
  • Gravel - "...whenever I can..." meaning if the file name is based on autogenerated Primary Key or "insert once" piece of data...and, of course, when file name does not have to be meaningful to end user – zam6ak Nov 03 '10 at 13:55
  • Gravel I think both ways have the disadvantages:) if you store the file name in DB someone can rename it on FS. If you autogenerate the file name from DB data, someone can change DB data (as you pointed out)...Pick you poison :) – zam6ak Nov 03 '10 at 14:00
3

In general, it is better to store them on the filesystem, with a path stored in the DB.

However, Microsoft published a white paper some time ago with research showing that files up to 150K can benefit from being put inside the DB (of course, this only pertains to SQL Server).

The question has been asked here many many times before:

Exact Duplicate: User Images: Database or filesystem storage?
Exact Duplicate: Storing images in database: Yea or nay?
Exact Duplicate: Should I store my images in the database or folders?
Exact Duplicate: Would you store binary data in database or folders?
Exact Duplicate: Store pictures as files or or the database for a web app?
Exact Duplicate: Storing a small number of images: blob or fs?
Exact Duplicate: store image in filesystem or database?

Community
  • 1
  • 1
Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • Please describe why it is better to put files on the filesystem. Don't just state it to be true. – Robert Jeppesen Nov 03 '10 at 12:57
  • @Robert Jeppensen - http://stackoverflow.com/questions/561447/store-pictures-as-files-or-in-the-database-for-a-web-app/561475#561475 – Oded Nov 03 '10 at 12:59
0

First of all have you checked the SQLite limits? If this is of no concern for you application, I would still chose the FS for storage needs simply due to overhead from getting large BLOBS from DB vs. reading a file from FS. You can mark the files as read only and hidden to lessen the chance of them being renamed... You can also store the file hash (like MD5) of a file in the DB so you can have secondary lookup option in case someone does rename the file (of course, they could move it as well in which case this would not help much)...

zam6ak
  • 7,229
  • 11
  • 46
  • 84
  • Isn't reading the bytes from the FS the same as reading the bytes from the database? Isn't it all bytes? –  Nov 03 '10 at 12:58
  • 1
    @Serg it certainly is, but there is *almost always* additional overhead associated when reading it from DB – zam6ak Nov 03 '10 at 13:14