• Post Reply Bookmark Topic Watch Topic
  • New Topic

Storing different type of file formats in my SQL  RSS feed

 
priyanshi bhardwaj
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
  • 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?
 
Pete Letkeman
Ranch Foreman
Posts: 667
18
Android Chrome IntelliJ IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • 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.
 
Tim Moores
Saloon Keeper
Posts: 3892
91
  • Mark post as helpful
  • send pies
  • 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.
 
Rob Spoor
Sheriff
Posts: 21092
85
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • 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: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess I should go for the first option in large projects. Thanks!! Everyone
 
Quintin Stephenson
Ranch Hand
Posts: 44
1
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • 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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!