create sql database to store documents

[teksecrets]

Hi,
I've been tasked with creating a SQL database to store a bunch of documents. We will be using a website to access these documents. How would I go about creating the tables structure and what settings will I need to use to create an index or a searchable table. Any help would be appreciated.

Thanks.

 

[gusset]

that's a very big question, which suggests that you don't yet know enough about databases to do it unassisted (no offence intended).

i do quite a bit of what you have been tasked with doing.

the table structure will obviously depend on the arrangement of your data in the real world. have a look at a book on relational databases and normalisation before you start, to avoid trouble later.

an interesting point is the document part; i assume that they are scanned in or in electronic form, eg word or txt files. you will probably want to use a field to store the URL (path) of the documents, opening them from within your front end. for the smaller database, in access, the hyperlink field may be a useful one. for the larger database, or if you reject access for one of the many reasons i do, you could store scanned images as BLOB data (i have no experience of doing this).

best of luck

g

 

[SQLSister]

Whether to use the image datatype to store the documents depends in part on whether you want to be able to search the documents directly or if you can enter only key words in a separte field somewhere and search on that. If you need to search the actual text of the documents you need to set up full-text search. Be aware also that importing and updating documents stored as blobs require special handling which is why it is preferred to store just the path for the document unless you need to search them directly. These are complex subjects and I suggest you read about them in Books online and any other resource books you have handy before attempting to set this up. It sounds like you also need to do better requirements definition to determine what goes in the tables and read some basic information on setting up and populating tables.