Activar
asked on
How to manage subcategories?
Hello -
I need some help/advice on how to handle subcategories in my application without creating some maintenance nightmare.
I currently have the following tables, docs, MainCategories, SubCategories
Each document today, in the Docs table, is assigned to a MainCategory. The application lists the MainCategories which the end user can then select to see the assigned documents.
We would like to implement SubCategories into the mix for applicable MainCategories. This means that some docs would be assigned to a MainCategory and associated SubCategories if one or more is available. In many instances a single document would be assigned to the safety MainCategory, for example, and then the SubCategories of Warehouse, Order Pullers, Order Processors, etc. The safety MainCategory may have 20 *number from thin air* SubCategories associated with it.
Docs Table
------------
ID (AutoNumber)
Title (Text)
Detail (Text)
MainCategory (ID from MainCategories)
SubCategory (yet to be defined)
MainCategories Table
-------------------------
ID (AutoNumber)
Category (Text)
subCategory (Yes/No - determines if this category has sub categories associated with it)
SubCategories Table
------------------------
ID (AutoNumber)
MainCategory (ID from MainCategories)
SubCategory (Text)
Status (Yes/No - determines whether or not the subcategory is available to be assigned to)
Database is SQL 2000 and CF production version is 6.1 with 7 in testing
If I were storing only one main and sub category this would be no problem. I'm having trouble grasping what to do with multiple subcategories without adding additional fields to the Docs table. Is it possible for me to write a list of subcategory ID's to the Docs table (1,2,3,4,5) and then return this value and parse it so I can list the associated subcategories thus leading to the documents assigned to the subcategory?
I have done very little where lists and arrays are concerned so if you can offer some code along with any suggestions on how else this may/could be handled it would be greatly appreciated.
TIA
I need some help/advice on how to handle subcategories in my application without creating some maintenance nightmare.
I currently have the following tables, docs, MainCategories, SubCategories
Each document today, in the Docs table, is assigned to a MainCategory. The application lists the MainCategories which the end user can then select to see the assigned documents.
We would like to implement SubCategories into the mix for applicable MainCategories. This means that some docs would be assigned to a MainCategory and associated SubCategories if one or more is available. In many instances a single document would be assigned to the safety MainCategory, for example, and then the SubCategories of Warehouse, Order Pullers, Order Processors, etc. The safety MainCategory may have 20 *number from thin air* SubCategories associated with it.
Docs Table
------------
ID (AutoNumber)
Title (Text)
Detail (Text)
MainCategory (ID from MainCategories)
SubCategory (yet to be defined)
MainCategories Table
-------------------------
ID (AutoNumber)
Category (Text)
subCategory (Yes/No - determines if this category has sub categories associated with it)
SubCategories Table
------------------------
ID (AutoNumber)
MainCategory (ID from MainCategories)
SubCategory (Text)
Status (Yes/No - determines whether or not the subcategory is available to be assigned to)
Database is SQL 2000 and CF production version is 6.1 with 7 in testing
If I were storing only one main and sub category this would be no problem. I'm having trouble grasping what to do with multiple subcategories without adding additional fields to the Docs table. Is it possible for me to write a list of subcategory ID's to the Docs table (1,2,3,4,5) and then return this value and parse it so I can list the associated subcategories thus leading to the documents assigned to the subcategory?
I have done very little where lists and arrays are concerned so if you can offer some code along with any suggestions on how else this may/could be handled it would be greatly appreciated.
TIA
ASKER
dgrafx,
Thanks for the comment.
>The concept would be this:
>First, you have mainCategories.
>Even though you may have mainCategories that contain no subCategories
>You can Never have a subCategory that is not in a mainCategory
True
>Then you have subCategories.
>Even though you may have subCategories that contain no docs
>You can Never have a doc that is not in a subCategory
I'm not understanding this
>Then you have docs - which are assigned to subCategories - not mainCategories.
Why? I'm guessing this is based on the table layout you are recommending.
Thanks for the comment.
>The concept would be this:
>First, you have mainCategories.
>Even though you may have mainCategories that contain no subCategories
>You can Never have a subCategory that is not in a mainCategory
True
>Then you have subCategories.
>Even though you may have subCategories that contain no docs
>You can Never have a doc that is not in a subCategory
I'm not understanding this
>Then you have docs - which are assigned to subCategories - not mainCategories.
Why? I'm guessing this is based on the table layout you are recommending.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ExpertAdmin - that is looking pretty good. I'm gonna chew on it for a minute or two and try a few things.
I'll be posting back.
Thanks for the response.
I'll be posting back.
Thanks for the response.
as expertadmin says you could combine tables or all 3 for that matter.
this is a design preference.
To answer the question you asked in your last post:
This is database design 101
The concept is the entire reason for categories, subcategories, etc being "categorized"
Otherwise you could just throw everything into the db and let users have at it!
It is a system of order.
Docs Table
ID TITLE Subcategory
==========================
1 Doc1 5
2 Doc2 5
3 Doc3 6
4 Doc4 7
SubCats Table
ID TITLE Category
==========================
5 SubCat5 3
6 SubCat6 3
7 SubCat7 4
Cats Table
ID TITLE
========================
3 Cat3
4 Cat4
this is a design preference.
To answer the question you asked in your last post:
This is database design 101
The concept is the entire reason for categories, subcategories, etc being "categorized"
Otherwise you could just throw everything into the db and let users have at it!
It is a system of order.
Docs Table
ID TITLE Subcategory
==========================
1 Doc1 5
2 Doc2 5
3 Doc3 6
4 Doc4 7
SubCats Table
ID TITLE Category
==========================
5 SubCat5 3
6 SubCat6 3
7 SubCat7 4
Cats Table
ID TITLE
========================
3 Cat3
4 Cat4
ASKER
dgrafx,
Thanks again for the response. Apparently I need DB 101, no argument there.
I also need the time and resources which are not available this is why I look often to the Experts on this site.
Thanks again for the response. Apparently I need DB 101, no argument there.
I also need the time and resources which are not available this is why I look often to the Experts on this site.
good luck ...
ASKER
ExpertAdmin -
I've accepted your answer. Thank you very much for breaking it down for me.
I've accepted your answer. Thank you very much for breaking it down for me.
Anytime.
M@
M@
First, you have mainCategories.
Even though you may have mainCategories that contain no subCategories
You can Never have a subCategory that is not in a mainCategory
Then you have subCategories.
Even though you may have subCategories that contain no docs
You can Never have a doc that is not in a subCategory
Then you have docs - which are assigned to subCategories - not mainCategories.
Setup your relationships via diagrams from sql server enterprise mgr
a relationship enforcing db integrity will make your life easier.
Your tables in brief - I removed some irrelevant "stuff"
MainCategories Table
-------------------------
ID (AutoNumber)
Category (Text)
Sort (numeric value you set to define display order - a value of 0(zero) will "hide")
SubCategories Table
------------------------
ID (AutoNumber)
MainCategory (ID from MainCategories)
SubCategory (Text)
Sort (numeric value you set to define display order - a value of 0(zero) will "hide")
Docs Table
------------
ID (AutoNumber)
Title (Text)
Detail (Text)
SubCategory (ID from SubCategories)
Sort (numeric value you set to define display order - a value of 0(zero) will "hide")
ex. of how Sort would be used
Select fields
From MainCategories
Where Sort > 0
order by Sort
this could also be used for your "if available to assign subCategories to".
those will value of 0 could not be assigned subCategories
et cetera
good luck ...