We've got two tables set up: search_table :id :metadata1 :metadata2 :metadataN :file_type :file_size :file_id
file_storage :id :image everything is stored in the database. The tables could have been combined into one, but we thought it might slow down performance if your having to deal with a blob object in all of your queries. The way we have it set up, we can run queries against the lightweight search_table and then retrieve the document from the file_id => file_storage.id relation. Also, given this technique, the file_storage table could easily be moved to another database, another server, or spread out to different databases. On 2/10/06, Rick Faircloth <[EMAIL PROTECTED]> wrote: > Haven't worked with document storage....so...if I understand correctly, > Zaphod, you're storing the pointer to a file on the server, and storing > the file as BLOB code...right? > > Rick > > > > -----Original Message----- > > From: Zaphod Beeblebrox [mailto:[EMAIL PROTECTED] > > Sent: Friday, February 10, 2006 2:32 PM > > To: CF-Talk > > Subject: Re: Storing Documents > > > > > > We just recently set up a document management system using MS > > SqlServer. We ended up storing the documents inside the db. The way > > we structured it was to set up a table that had all of the meta data > > about the document along with a file id (int) that linked up with a > > file storage table that consisted of an id and a blob column. This > > way, we can run queries against the meta data without slowing down the > > system with large blob columns. > > > > So far, the performance has been suprisingly snappy. Also, security > > has been a lot easier to work into as we only have to secure one > > resource instead of both a database and a file system. Another > > additional benefit is that we've been able to share some documents on > > our extranet site without having to open another port for file sharing > > as all documents come from the db. > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231998 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
