Best Practice SQL2005 table design for document images
Posted on 2009-04-23
We have been developing a number of in-house document management solutions over the last couple of years and are very comfortable with the storing of documents in the SQL2005 image field type.
However we are about to extend our solution to a department that recevies / generates documents at a volume that is at least a magnitude above what we have been accomodating to date.
I am interested to see if someone can recomend some best practice table design guidelines for the storing of large volumes of records with image data.
For each document record we will have a single image field and a number of fields used to categorise and index the document (doc title, refenernce number, source, notes, etc).
My question is; anticipating that we will have high volumes of documents should I look to design my table structures to split the document table into two seperate tables with a one to one relationship, one with the document attributes, and one with just the document image?
Would this help with index maintenance and storage if I were to place the "Document Image" table on a seperate file group?
Is the TEXTIMAGE_ON option on the Create Table statement good enough for achieving the same thing?
Is there perhaps a better design approach that someone is aware of?
We are utilising SQL2005 Standard, there are no immediate plans to move to SQL2008.
Thanks in advance!