Re: Picture with Postgres and Delphi - Mailing list pgsql-general

Network Administrator wrote:
> I'd actually like to get some comments on this too because for compatibility and
> throughput issues, I would think that storing the file path in the database
> instead of the actually file would be "better".  I've done one application like
> this in the past that very worked well.  I'm getting ready to do the final
> e-commerce integration on a new site and if there is an advantage to storing the
> files in the database (in this case about 300 jpeg images for a t-shirt site)
> I'll try that out.  I'll have to research that base64 encoding part because I'll
> only every do text dumps.

Storing the file path on the database can work well, but with modern
databases, there shouldn't be any issue with performance with storing
BLOB data. If you find there is an issue, then you should just set up a
table holding this information along with a key field. This key would be
a foreign key on your table holding the other information (in the case
of the t-shirt site, perhaps the other table holds information relating
to the style, size, etc).

In effect, what you are doing by storing the path is the path is your
"foreign key", to the filesystem "database". That is, you are using the
filesystem as a database.

One of the issues with storing the path on the database, instead of
storing the BLOB, is that the data on the filesystem can change or be
moved, without the database knowing about it. You then have "broken
keys", keys that refer to data that is no longer the same as it was when
the data was inserted into the database (the data is different, or it
doesn't exist, or it is corrupted in some manner). There is also the
issue of backups and restoring the state of the database. If you take a
snapshot of the database at any point in time for a backup, you need to
do the same with the pictures as well, and when you restore, remember to
restore both. If you kept the data in the database, you just need to
restore it alone.

I know there are other reasons as well - hopefully others on here will
point them out, as my memory is a bit fuzzy right now...

Andrew Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.