Optimal media storage solution on directory/database level

I'm developing a website that is going to have lots of images in different sections. For example there will be user photos and article photos. I was thinking of combining all media into a single media database table and store the开发者_如何转开发 images based on the date they are uploaded:

id: 1564
name: 55821325202772db75de084b2919cabb (hashed)
path: images/2011/07/01/
date: 01-07-2011
ext: jpg
type: image
rtype: news (relation type)
rid: 153 (relation parent id)

If I then need all images linked to news post 153 I need to query the database for the specific rtype and rid.

Would this be a good way to store the website's media?

edit: If I would combine rtype and rid into 1 (hashed) column, would this result in faster queries?

---

I think that would work, and it's flexible. One minor downside is that you can't have a foreign key from media to news post, so you don't get referential integrity. (You could end up with orphaned media files.) If that's a problem, you can have nullable links from media to all the tables that might use it, and a constraint that says one of those links must be non-null.

---

This is the way it used to be in Ruby on Rails framework. I think you can follow this way also in php.

You can see a RoR example on how to associate comments to more than a object: http://asciicasts.com/episodes/154-polymorphic-association

You can also take a look at this article from cakePHP bakery: http://bakery.cakephp.org/articles/AD7six/2008/03/13/polymorphic-behavior

---

That seems like a pretty functional way to do it.

---

If you need really HIGH performance, you can try Redis.

For this issue you can try hash:

add new item:

hset id:1564 name 55821325202772db75de084b2919cabb
hset id:1564 path images/2011/07/01/
hset id:1564 date 01-07-2011
hset id:1564 ext jpg
hset id:1564 type image
hset id:1564 rtype news
hset id:1564 rid 153

Read path for id 1564:

hget id:1564 path

Read all keys:

hgetall id:1564

Redis works VERY fast (more than 100 times faster than memchached), and all data will be backed up to disk (asynchronously), so your data will not be lost even after reboot. Size of RAM should be enough to store your DB, but be sure - Redis works with memory very effectively.