Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to manage subcategories?

Posted on 2006-06-14
9
695 Views
Last Modified: 2013-12-20
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
0
Comment
Question by:Activar
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Expert Comment

by:dgrafx
ID: 16903229
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

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 ...
 
0
 

Author Comment

by:Activar
ID: 16903407
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.
0
 
LVL 7

Accepted Solution

by:
ExpertAdmin earned 500 total points
ID: 16903467
I think you are making it more complicated than it needs to be. With relational databases, you don't have to do things like have a flag to tell if there are subcategories, and I think all of your categories (main and sub) can go in the same table. Consider this layout:


Docs Table
------------
ID (AutoNumber)
Title (varchar(100))
Detail (Text)

Categories Table
-------------------------
ID (AutoNumber)
Category (Text)

DocumentCategories
------------------------------
DocID (int)
CategoryID (int)
IsMain (bit)

The data for these tables will look like this:

>>>Docs Table                

ID        TITLE               DETAILS
===========================
1          TestDoc1     ...some details...
2          TestDoc2     ....details....


>>>Categories Table


ID        Category
=================
1         Category1
2         Category2
3         Category3

>>>DocumentCategories

DocID                 CategoryID       IsMain
===========================
1                                1                0
1                                2                1
2                                1                1
2                                2                0
2                                3                0


What the DocumentCategories does is tie documents to multiple categories through their ID feilds.

To get a list of documents and their categories, you could do this:

---------------------------------------------------
SELECT Documents.DocID,
           Documents.Title,
           Categories.ID AS CategoryID,
           Categories.Category,
           IsMain
FROM Documents
             INNER JOIN DocumentCategories
                       ON DocumentCategories.DocumentID = Documents.ID
             INNER JOIN Categories
                        ON Categories.ID = DocumentCategories.CategoryID
ORDER BY IsMain DESC
---------------------------------------------------

which would yeild this output:

DocID              Title             CategoryID     Category       IsMain
===========================================
1                TestDoc1             2                Category2          1
1                TestDoc1             1                Category1          0
2                TestDoc2             1                Category1          1
2                TestDoc2             2                Category2          0
2                TestDoc2             3                Category3          0

If you want to get the categories for a single document, just add the critieria:

---------------------------------------------------
SELECT Documents.DocID,
           Documents.Title,
           Categories.ID AS CategoryID,
           Categories.Category,
           IsMain
FROM Documents
             INNER JOIN DocumentCategories
                       ON DocumentCategories.DocumentID = Documents.ID
             INNER JOIN Categories
                        ON Categories.ID = DocumentCategories.CategoryID

WHERE Documents.ID = 2

ORDER BY IsMain DESC
---------------------------------------------------


Does this make sense?

M@
       




0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 

Author Comment

by:Activar
ID: 16903528
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.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16903618
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        
         

0
 

Author Comment

by:Activar
ID: 16903665
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.

0
 
LVL 25

Expert Comment

by:dgrafx
ID: 16903690
good luck ...
0
 

Author Comment

by:Activar
ID: 16904115
ExpertAdmin -

I've accepted your answer. Thank you very much for breaking it down for me.
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16904185
Anytime.

M@
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question