What is the best way to store media files on a database?

Every system I know of that stores large numbers of big files stores them externally to the database. You store all of the queryable data for the file (title, artist, length, etc) in the database, along with a partial path to the file. When it's time to retrieve the file, you extract the file's path, prepend some file root (or URL) to it, and return that.

So, you'd have a "location" column, with a partial path in it, like "a/b/c/1000", which you then map to: "http://myserver/files/a/b/c/1000.mp3"

Make sure that you have an easy way to point the media database at a different server/directory, in case you need that for data recovery. Also, you might need a routine that re-syncs the database with the contents of the file archive.

Also, if you're going to have thousands of media files, don't store them all in one giant directory - that's a performance bottleneck on some file systems. Instead,break them up into multiple balanced sub-trees.

I think storing them in the database is ok, as long as you use a good implementation. You can read this older but good article for ideas on how to keep the larger amounts of data in the database from affecting performance.

http://www.dreamwerx.net/phpforum/?id=1

I've had literally 100's of gigs loaded in mysql databases without any issues. The design and implementation is key, do it wrong and you'll suffer.

More DB Advantages (not already mentioned):

• Works better in a load balanced environment
• You can build in more backend storage scalability

Advantages of using a database:

• Easy to join sound files with other data bits.
• Avoiding file i/o operations that bypass database security.
• No need for separation operations to delete sound files when database records are deleted.

Disadvantages of using a database:

• Database bloat
• Databases can be more expensive than file systems