Where to store pictures, in DB or in files?

[romayankin]

I'm making some kind of news-board site on PHP (tthat's why I guess
it's the right place to post the question here). Each entry would
have a small picture (less then 100kb). Taking into consideration all
pros and cons the question is where to store these pictures in DB or in
files?

[Richard Levasseur]

ro********@gmail.com wrote:

I'm making some kind of news-board site on PHP (tthat's why I guess
it's the right place to post the question here). Each entry would
have a small picture (less then 100kb). Taking into consideration all
pros and cons the question is where to store these pictures in DB or in
files?

Files. You can link to them in an <imgtag easier, don't have to
write code to extract the image and send the appropriate headers, and
can easily view/modify/ them using ftp/samba/etc. Store a path to the
file in the database instead.

[David T. Ashley]

"Richard Levasseur" <ri********@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

>
ro********@gmail.com wrote:

>I'm making some kind of news-board site on PHP (tthat's why I guess
it's the right place to post the question here). Each entry would
have a small picture (less then 100kb). Taking into consideration all
pros and cons the question is where to store these pictures in DB or in
files?

Files. You can link to them in an <imgtag easier, don't have to
write code to extract the image and send the appropriate headers, and
can easily view/modify/ them using ftp/samba/etc. Store a path to the
file in the database instead.

Depending on how many files you plan to accumulate, it is traditional to use
a directory structure where you keep the file in a directory whose path
components are the file index moduli of different prime numbers. For
example, choose 2, 3, and 5 as the prime numbers, then

File #1 goes in 1/1/1/1/filename.
File #2 goes in 0/2/2/2/filename.
File #3 goes in 1/0/3/3/filename.
File #4 goes in 0/1/4/4/filename.
File #5 goes in 1/2/0/5/filename.
File #n goes in (n mod 2)/(n mod 3)/(n mod 5/n/filename.

As long as you choose different prime numbers, you won't get the same hashed
directory until a period which is the product of the primes.

The moduli are cheap to compute for a computer.

Files are the superior way. In addition to all other advantages, if
something goes monstrously wrong on the server or in the database product,
the files can be recovered separate from the database.

Dave.

[Chung Leong]

ro********@gmail.com wrote:

I'm making some kind of news-board site on PHP (tthat's why I guess
it's the right place to post the question here). Each entry would
have a small picture (less then 100kb). Taking into consideration all
pros and cons the question is where to store these pictures in DB or in
files?

Keeping images in the database makes them easier to manage. No worry
about duplicate filename. If your database supports cascade delete then
the images are purged automatically when the article is removed. And a
foreign key constraint could guarantee that the images won't go
missing. Keeping them in the database also makes backup easier.

The downside is performance and resource usage. The web server is
extremely efficient at serving static files. Loading an image from the
database is relatively slow, on the other hand, and uses a good chunk
of memory.

[Richard Levasseur]

David T. Ashley wrote:

"Richard Levasseur" <ri********@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

ro********@gmail.com wrote:

I'm making some kind of news-board site on PHP (tthat's why I guess
it's the right place to post the question here). Each entry would
have a small picture (less then 100kb). Taking into consideration all
pros and cons the question is where to store these pictures in DB or in
files?

Files. You can link to them in an <imgtag easier, don't have to
write code to extract the image and send the appropriate headers, and
can easily view/modify/ them using ftp/samba/etc. Store a path to the
file in the database instead.

Depending on how many files you plan to accumulate, it is traditional to use
a directory structure where you keep the file in a directory whose path
components are the file index moduli of different prime numbers. For
example, choose 2, 3, and 5 as the prime numbers, then

File #1 goes in 1/1/1/1/filename.
File #2 goes in 0/2/2/2/filename.
File #3 goes in 1/0/3/3/filename.
File #4 goes in 0/1/4/4/filename.
File #5 goes in 1/2/0/5/filename.
File #n goes in (n mod 2)/(n mod 3)/(n mod 5/n/filename.

As long as you choose different prime numbers, you won't get the same hashed
directory until a period which is the product of the primes.

The moduli are cheap to compute for a computer.

Files are the superior way. In addition to all other advantages, if
something goes monstrously wrong on the server or in the database product,
the files can be recovered separate from the database.

Dave.

Another option would be to name the file after the primary key, or
other unique index, of the table.

[David T. Ashley]

"Richard Levasseur" <ri********@gmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...

Another option would be to name the file after the primary key, or
other unique index, of the table.

The reason for the prime moduli directory scheme is that Unix directory
operations become inefficient beyond a few hundred entries in a directory.

The prime moduli scheme is designed to avoid this by filling up the
lowest-level directories evenly.

It isn't clear that naming the file after the primary key would have the
same advantage.

The example I gave was with three primes. In practice, one should use
primes not larger than 200 and enough of them so that their product exceeds
2^32.

[TW]

David T. Ashley wrote:

"Richard Levasseur" <ri********@gmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...

Another option would be to name the file after the primary key, or
other unique index, of the table.

The reason for the prime moduli directory scheme is that Unix directory
operations become inefficient beyond a few hundred entries in a directory.

The prime moduli scheme is designed to avoid this by filling up the
lowest-level directories evenly.

It isn't clear that naming the file after the primary key would have the
same advantage.

The example I gave was with three primes. In practice, one should use
primes not larger than 200 and enough of them so that their product exceeds
2^32.

That is justa maintenance nightmare for the average person...Someone's
poor bastard's gonna go implement this technique to only realize that
s/he shot himself in the foot because they'll have to figure out what
folder to place an image into. What about when it's time to delete a
file? Now they'll have to script it out (if feasible). That's just too
much work for something that should simply be an intelligible file
structure.

Use that method if you find that you must, but 200 entries? That's on
the low side. I know a site right now with major traffic and about 4000
images in a single folder...it's hardly slow to serve them up.

Perhaps read operations don't suffer as much (or at all) as writes.
I've seen qmail fill a folder with 100,000+ files. Read access times
were just fine.

I'm all for efficient design, but practicality rules.

[Surfer!]

In message <jQ*******************@fe67.usenetserver.com>, David T.
Ashley <dt*@e3ft.comwrites

>"Richard Levasseur" <ri********@gmail.comwrote in message
news:11*********************@b28g2000cwb.googlegr oups.com...

>Another option would be to name the file after the primary key, or
other unique index, of the table.

The reason for the prime moduli directory scheme is that Unix directory
operations become inefficient beyond a few hundred entries in a directory.

Indeed, AFAIK it starts at the beginning of the directory and searches
sequentially until it finds a hit.. There is no structure of any kind,
unlike the old System 10 / System 25 computers, where the directories
were tree structures and so much more efficient for large numbers of
files.

However there are schemes that people find easier to understand... For
example, Demon's home directory for a user 'picture' will be similar to
/home/p/i/c/picture. What makes it a good scheme IMHO is that the path
can be derived from the user's name..

At present I don't have enough images anywhere for this to be a problem
but I would adopt a similar approach:

$IMGPATH/0 holds images 0 to 99
$IMGPATH/1 holds images 100 to 199

and so on.

So, the database doesn't even need to hold the full path to the file -
the code can easily derive it from $IMGPATH and the file name. Moving
$IMGPATH doesn't involve altering the database contents, and (obviously
I hope) the code to derive the path would be a function or class so that
if you ever re-organise the structure there is only one place to have to
change the code.
--
Surfer!
Email to: ramwater at uk2 dot net

[Chung Leong]

TW wrote:

Perhaps read operations don't suffer as much (or at all) as writes.
I've seen qmail fill a folder with 100,000+ files. Read access times
were just fine.

That folder might be mounted on something more advanced like ReiserFS.

In any event, it's worth remember that at the end of the day, the image
file has to be sent across the Internet. Network latency will eat up
what little you gain.