So I’m working on a personal project, a document library. Myself and several other people have tens of thousands of documents that we would like to archive online. So I started thinking about how I could build this. Basically I want a person to upload a document (pdf, text, etc.) and fill in info about it and then TAG it with anything relevant to the document such as people’s names, locations, etc.
So the first step for me, of course, was how to lay the database out. I started with the documents table which contains the following fields:
Doc_ID (primary key)
Title (title of the document)
Description (A semi-long description of the document)
Abstract (if the document is only text vs. a pdf, for example, that text would go here.)
filename (the name of the file uploaded)
filetype (the file type of the document)
document_date (the date of the actual document)
timestamp (date/time when the document was uploaded)
User_ID (id of the user who uploaded the document)
Then I figured I would need a user table
User_ID
Name
Password
timestamp
Now onto the tags. There were several things to consider here. I’d like to use ajax in the site to auto suggest tags to try to keep people from misspelling etc. However it is inevitable that people will still enter tags incorrectly. To maintain the integrity of the tagging system I want to make sure if a “new” tag is entered that it is marked as new so it can be reviewed and then either approved or corrected with the appropriate tag. So first I need a Tags table:
Very simple:
Tag_ID (Primary key)
Tag (the actual tag)
Approved (bit column 0/1 so when a new tag comes in it’s marked w/ a 0 until approved)
Next is the document tags table that links the document up with the tag.
Doc_ID
Tag_ID
In the next post I will go over the ColdFusion portion of entering the document and tags. Ultimately with it set up this way, it will create a way to click on a Tag and pull up all other documents that are related to that Tag, which is the ultimate goal here.
Kelly Matthews 