Ok, I have something else I need to get my head around.
In this application, we have the following hierarchy:
Category -> Subcategory -> Document.
Normally, each subcategory would have one parent category, but in this case, each subcategory has the potential to appear in more than one category. For instance, the subcategory Recreational Trails appears under both the Design and the Planning categories.
In the database I have two options. Option 1 is to have a parent category columns in the subcategories table, and list each category more than once. Option 2 is to have each subcategory once in its table, then have a relational table to join the subcategories table to a categories table (which is how I have it now).
Normally, I would use option 2, except that as I build the front end for this thing (a web-based form used to add documents and assign them to categories and subcategories) I'm noticing that duplicates are showing up, which is not surprising. For instance, when I look at the above document, the Recreational Trails subcategory appears twice - once for each category it's in.
I suppose I could just group the subcategories, but I'm wondering if I'm going about this the right way. Currently I have a table that joins subcategories to their parent categories, but maybe I don't need that at all, and I should be joining the documents themselves to their main categories?
I'm not sure which way I should be doing this!