Thread: Storing Documents

i'm trying to store documents or links to documents
(excel, word, jpgs, etc...  everyone uses windoze)
that will need to be referenced (eg, written
instructions that may need pictures to better explain
the information).  can anybody share their experience
and suggest a best way to do this?

right now, i'm thinking that storing a link to the
file's server location is probably better than storing
the document in the db itself.  i'm also thinking that
i could request that the only the file name be entered
into the db and then i could append the file name to
the directory link as required?

is this the best way?  is there a hidden *gotcha*
involved that i might need to be aware of?

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Hey operationsengineer1,

On Wed, 9 Mar 2005 09:39:03 -0800 (PST), operationsengineer1@yahoo.com
<operationsengineer1@yahoo.com> wrote:
>
> is this the best way?  is there a hidden *gotcha*
> involved that i might need to be aware of?

Here is a trick I have learned over the years:

Step 1:
CREATE TABLE filebase (
  id serial NOT NULL PRIMARY KEY,
  filename varchar(128) NOT NULL
)

Step 2:
Have your scripts save the uploaded file as /usr/share/filebase/(id)

Step 3:
Always reference your 'filename' throughout application. That way you
don't have to worry about duplicate filenames overwriting each other
(unpleasant)... If you wanted simple version control, you could also
create a second table that stored versions for each row in the
filebase table.

-- Mitch

-- Mitch

On Mar 9, 2005, at 12:39 PM, <operationsengineer1@yahoo.com> wrote:

> right now, i'm thinking that storing a link to the
> file's server location is probably better than storing
> the document in the db itself.  i'm also thinking that
> i could request that the only the file name be entered
> into the db and then i could append the file name to
> the directory link as required?
>
> is this the best way?  is there a hidden *gotcha*
> involved that i might need to be aware of?

Do some searching in the list archives -- you'll find plenty of
previous discussion on this.

Your approach will work, but the down side is that documents are not
under transaction control. Further, they don't benefit from other
database facilities such as searching and backup.

The right approach for this depends highly on the nature of the
application, runtime environment, and size of the documents.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL