Is it appropriate to store the image files in the database? Or it would be better to store only the path of the file in the database, while keeping the file itself on the server?
Are there any other methods for doing this right?
Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.Sign up to join this community
I strongly advise you to store the images in the filesystem and not in the database.
Storing images in the database has several disadvantages:
The database might grow unexpectedly large. Sometimes space is an issue. For example with SQLServer express you have a 4GB limit.
Data migrations can become a pain, for example if you switch from SQLServer to Oracle
Queries can become very slow and you'll have a high database load
Interoperability with other applications is better if the images are on the filesystem and other applications use a different database. You can also access them directly and do not need database tools.
Worse performance in general
You'll probably have to create temporary files when retrieving the images from the database anyway. That's unnecessary.
These disadvantages far outweigh the cost of keeping the paths to the images stored in the database synchronized with the filesystem. There're only few special cases in which it's better to store the images in the database.
Research on SQL Server 2005 and the NTFS file system (Microsoft) to compare CRUD performance: To BLOB or not to BLOB. This study was also done in a web application. You listed another database (MySQL) and I'll assume you're not using Windows Server for your PHP site, so it would be interesting if someone has done a similar study on different technologies.
Turns out that it depends on the size of the files. SQL Server favors (2x better) blobs of 256K or less and the file system favors files of 1MB+. File systems in this study handle fragmentation better than the database, so if you are constantly updating these files or this system grows over time, fragmentation is going to be a bigger factor which the file system will do a better job.
You need to determine how responsible your site is for maintaining these files. If you're buidling a site for insurance claims agents to upload photos of an accident, you better have transaction control and make sure those files are on the server. Good databases do this for you, so as the developer, you have added pressure.
Replication, backup, disaster recovery, fragmentation, remaining disk capacity, and performance over time should be considered during design. This is just one study on the previous version of SQL Server and like other database manufacturers, I'm guessing handling large files and binaries is a major competitive arena.
If you are going to have a massive number of images, storing them in a database might remove problems with running out of inodes at the file system level.
However this problem would best be solved by using a more appropriate filesystem and whatever measures are advised with that filesystem on organisation.
Otherwise, storing images in a database is a complete waste of resources. Store the path as you said.