CODEWITHSUNDEEP
phpmysqlmedia
Jens
Jens

Reputation: 1507

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?

Upvotes: 3

Views: 255

Answers (4)

user680786
user680786

Reputation:

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.

Upvotes: 0

Matteo Alessani
Matteo Alessani

Reputation: 10412

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

Upvotes: 1

Don Kirkby
Don Kirkby

Reputation: 56620

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.

Upvotes: 1

Paul Sonier
Paul Sonier

Reputation: 39480

That seems like a pretty functional way to do it.

Upvotes: 0

Related Questions