• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Storing different type of file formats in my SQL

 
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am creating an application and in it, I want to store profile picture of my user :

Storing different type of file formats in my SQL is concerned there can be two ways.

#1.  Create the files physically and store the path in DB.

or

#2.  Use LOB/BLOB data types to store the raw file data in the database itself.

Which one is efficient for a big project?
 
Bartender
Posts: 1868
81
Android IntelliJ IDE MySQL Database Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

priyanshi bhardwaj wrote:Which one is efficient for a big project?


I vote for

priyanshi bhardwaj wrote:Create the files physically and store the path in DB.


However there can be some drawbacks to this method, two of which could be file overwriting and file deletion.
 
Saloon Keeper
Posts: 7585
176
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Without knowing the particulars, that would be my reaction as well. A (large) number of (large) BLOBs in a DB is generally not a recipe for speed.

There are other considerations, though. Does more than one computer need to access this DB/FS? In that case there would be some synchronization issues to solve with regards to the FS. And those computers would need access to the FS in the first place, which may or may not be an easy issue to solve.
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There's one more drawback of storing file paths - you lose proper transactional behaviour. If an error occurs you can rollback your database transaction, but you can't easily roll back your file system changes, especially when you're deleting or overwriting files.
 
priyanshi bhardwaj
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I guess I should go for the first option in large projects. Thanks!! Everyone
 
Ranch Hand
Posts: 44
1
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

priyanshi bhardwaj wrote:I guess I should go for the first option in large projects. Thanks!! Everyone



Are you sure you want to go with point 1 on a large project?  Here are a few questions and points to think about:
1. Who is going to administer the folders where your pictures will reside?  You may need more people involved to administer you project.  If you go with the separate images folder no matter where it resides, and depending on the size of your org, the application, you could be needing a small army to support the app when it is ready to golive. (DBA's, Web server admin, file storage admin, network admin).  
2. Where are these pictures going to reside if you go with the file store option?  If they reside on the same server they could bring you server down by filling space up with the images (unnecessary risk).  If they go on another server, and that server goes down, you loose your images. Also if this is a web application, and you have the images on a different server you could have cross domain issues.  
3. Transnational issues have already been mentioned already by Rob Spoor.
4. People who should not have access to you images will now have access to them, which could offend people and affect peoples privacy (As daft it may sound the image can be seen as data, depending on the nature of you application and business could therefore be subject to data protection).

Will it really be quicker to get the image from a file store, I don't thinks so, here's why, you still have to go to the database to find out where the image is stored before you can get the image.  You could base64 the image and store it in a clob or varchar column (depending on the image size), and therefore not have to worry about the file format after it is saved.

cheers
Q
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic