Help with building an archive
Hi!
I'm trying to build an archive of files (documents, media, etc.) and need some help with the table structure and to make the archive searchable.
This is what I have:
File
FileID
FileName
FilePages
FileDate
FileAuthor
FileDescription
Classification ? essay, article, joke, quote, etc.
Types
FileID
FileType - doc, URL
FileIcon
FileSize
Topic/Category
FileID
FileCategory
Keyword
FileID
FileKeyword
My question now is how do deal with all the sub categories. Do I create multiple tables for them?
Category Level One (main)
FileID
FileCategory
Category Level Two (sub)
FileID
FileCategory
Category Level Three (sub sub)
FileID
FileCategory
Also, when searching through drop down menus, how does that work with catgories?
Another table I realised that I need is one for themes. Instead of the user receiving 100 results and having to wade through them. It would display results according to theme. i.e. Independece Day - History, Independece Day - Meaning of Freedom, Independece Day - Celebration, etc.
Theme
FileID
Theme
Is that it?
How do I set up the drop down menu search for that component as well?
Software/Hardware used:
Software/Hardware used:
ASKED:
June 26, 2005 6:21 PM
UPDATED:
June 27, 2005 2:14 PM
Answer Wiki:
I assume you are using SQL of some sort. If I understand your goals correctly, you want to be able to search (through drop-down boxes) by two criteria: category and theme. This would involve creating three tables: the master file, one for three levels of category, and one for themes. The category file could be done in two ways. The first way would be to have all subcategories in a single row (Category1, Category2, Category3, FileID). The second way would be to place each category in a separate row (Category, FileID).
Hm. It seems to me, after some reflection, that you want to search by category in the drop-down box, and that you want to see a list of files under each category. Is that true? If so, you would a) build the list-box (a tree would be better, if you are using a Microsoft product, or if you system provides tree controls) containing the file reference under the categories and subcategories, or b) allow the user to (double-)click on a category/subcategory to get another box containing the list of file references.
Hopes this helps a little.
Phil
To see all answers submitted to the Answer Wiki: View Answer History.
Hi!
I’ll try to answer your question about subcategories. I suggest to use a master-detail scheme, so you can have only two tables to define as many categories as you want. In a master table, you define the list of categories you’d like to add, and the detail table will be useful to store the file categories themselves.
I’ll explain it with an example. Suppose you ‘d like to have three categories, Cat1, Cat2, and Cat3. Besides, you classify your files, so File1 has Cat1, File2 Cat3, and so on.
You should have a master table like this:
ID Name
1 Cat1
2 Cat2
3 Cat3
And your detail table will be like this:
ID File Category
1 File1 1
2 File2 3
The ‘Category’ Row at the detail table has foreign keys, related to the primary keys in a master table.
I hope it helps
. Good luck!
MelissaG
VenPhil’s solution has your current file structure and is the correct solution only if your categories are not predefined.
MelissaG’s solution is correct if your categories are predefined and especially if you might want to Change the name of a category at a later date. This solution requires you to have a table in between your File table and your Category table. This is a way of saying a category can be assigned to one or more files and a file can have one or more categories.
I would also use this approach for your FileType table and move the FileSize column into the File table.
For selection you will need to use a multi-select listbox converted to an IN clause.
(select FileName from File, FileCategory, Category
where File.FileID = FileCategory.FileID and Category.CategoryID = FileCategory.CategoryID and
Category.CategoryName IN (:CategoryList)