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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.


Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • MelissaG
    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
    20 pointsBadges:
  • Ptbarnum
    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)
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: