filing system

[Guest]

Hello,

I am trying to build a filing system that allows a user to enter and search
for documents. Here's where it gets complicated: there are multiple
documents to a file, there are multiple files to a category, there are
multiple categories to an item, and there are multiple items in a group (so
all one to many relationships). The file which holds the documents is also
assigned a location. This is how the layout looks:

A. Group1
1. Item1
1.1 Category1
1.1.1 File1 <---- location
1.1.1.1 Document1
2. Item 2
....
B. Group2
1. ........

Hope this makes things a little clearer. We're having trouble setting up
these tables. Any ideas?? Thanks!

 

[James Hahn]

You will need to describe exactly what trouble you are having setting up the
tables for this database. I can't see that the structure you have described
presents any particular difficulty, so your design might be inappropriate
for what you are trying to achieve.

 

[Guest]

I guess what my dilema is whether to have a seperate table for each level or
just have them all in one table. And if i were to have seperate tables for
each level, would something like this work:

tbItem(Group, Item)
tbCategory(Item, Category)
tbFile(Category, File)
tbDocument(File, Document)

Or am i just out to lunch? Thanks for your help!

 

[Joseph Meehan]

I guess what my dilema is whether to have a seperate table for each
level or just have them all in one table. And if i were to have
seperate tables for each level, would something like this work:

tbItem(Group, Item)
tbCategory(Item, Category)
tbFile(Category, File)
tbDocument(File, Document)

Or am i just out to lunch? Thanks for your help!

IF (big if) I understand correctly, I believe you are on the right
track.

 

[James Hahn]

You can emulate a simple hierarchical database such as you have described
using compound keys.

This structure would have groups, items, categories, locations and files as
simple lookup tables for the document. So the first five tables consist
simply of a code (as the key) and a description (which could be one or
several fields).

The document table records are constructed with a compound key that consists
of the group, item, category, location and file it belongs to, and the
document code.

If you listed the documents in order of their keys, you would generate the
hierarchical structure you have described. To find a document you would
identify the group, item, category, location and document that it belonged
to. That would take you to a part of the hierarchy, and you would search
within that region.

But this may not be the best way to go. An alternative would be to build
the database with the same structure for the first five files, but make the
key for the document simply the document code. This would have to be
unique, which might be an issue. The document record then includes the
group, item, category, location and document that it belongs to as foreign
keys, allowing the document to be completely categorised according to these
five lookup tables. You would identify the document by selecting any set of
criteria covering group, item, category, location and document, and use a
query to give you a subset of the documents to search in.

Your actual solution will fall somewhere in the middle, probably dictated by
what coding is effective (or practical) for the documents. For instance,
you may not be able to ensure that filenames are unique outside their
category. Therefore, you could set group, item and location as simple
lookups that would be a foreign key in the document, while the document
itself might be keyed on category, file and document code. Eg:
Group(code,description)
Item(code,description)
Location(code,description)
Category(code,description)
File(Category/code,description)
Document(File/code,description)
|-->Group
|-->Item
|-->Location

Your original description implied a slightly different arrangement that
meant that each group has its own private set of items, and each item has
its own private set of categories, etc:
Group(code,description)
Item(Group/code,description)
Category(Item/code,description)
File(Category/code,description)
Location(code,description)
Document(File/code,description)
|-->Location