Categories and subcategories

[webwizz]

I'm wondering how to design an Access database with the same type of structure as "Yahoo Shopping!". The Yahoo site allows search for women's clothing or men's clothing or all clothing...please explain how the relationships are structured.

Is it just one category table with queries that display results of a search of multiple categories? How do I structure it in Access queries and forms?

Thank you!

 

[Birdy]

Cats and SubCats

I work with Categories and SubCategories and these are usually in seperate tables.

If you look at how EBAY works, You search for an item and it displays the item in various subcats, select the relevant subcat and you get just what you want.

 

[The_Doc_Man]

It could be as simple as a single category table or could be pretty complex. In this forum, you can do a search on the subject called "Cascading Combo Boxes" as a way to see how to refine categories, sub-categories, sub-sub-... etc etc

Logically speaking (and NOT structurally speaking), you are dealing with a tree. One way to do this involves some number of cascading tables. The final question is what you are seeking as a result of this operation.

Suppose, for sake of argument, that you have 20 categories. You need a category table with 20 records. You find the category and get a code value.

Now you have some number of sub-categories. Arguendo[/] lets say each category has 20 subs. That's 400 records altogether. So using the cascading combo box method you do a dropdown in a SINGLE TABLE holding all subcategories. The Subcat table has the subcat name, the category code, and a subcat code.

OK, keeping up one more round, the third level lets say is also 20 subs per subcat. That's 8000 records at this level. So using the cascading combo box method you do a dropdown in this single table to a record holding the previously selected cat and subcat code to find the subsubcat code.

Eventually, you will search your target table of arbitrary size to find all records having the same cat, sub cat, and subsubcat codes.

Options at any level would be that if you can't drill down farther - say 'cause you've reached the end of the tree at the subcat level - your code comes back with something that says - ok, go straight to the table and use null for the subsubcat (or blank, perhaps preferable...)

NOW .... suppose that you have items that could be in multiple categories at once. What do you do? Here's where it gets tricky.

If this can happen EVEN ONE TIME, you need another table that has the cat, subcat, subsubcat, and item codes. So your final query finds all item codes that match the chosen cat, subcat, and subsubcat. In this table, it is ok to have the same item appear twice for different combinations of selector codes. And what you do then is link from the item code (part number, SKU number, whatever you call it) to the record describing the unique item.

So for example, you can have a quirt appearing under two categories:
sports >> equestrian >> accessories and
health >> sex >> toys

Did I suggest that?

 

[webwizz]

Thanks Doc man for your detailed reply!

I deleted my last post in error...I think I wrote "what if I have a many-to-many relationship and an item can fall in many categories and subcategories".

It's all so complicated. I'll have to look into " cascading combo box" as you suggested to see if it's really what I need. Thanks again.

 

[The_Doc_Man]

No problem.

Just to clarify, here is what the tables might look like for this search.

tblCat
CatName, text, name of category, PK
CatCode, long or short integer (your call), code number for this category

Note: This means that you can have synonyms for a code if you make the NAME the PK rather than the code.

tblSubCat
CatCode, long/short integer, major category for this subcategory, part of PK
SubCatName, text, name of category, part of PK
SubCatCode, long/short integer, secondary category code

Again, making the name part of the PK allows synonyms to get to the same subcat code.

tblSubSubCat
CatCode, long/short integer, major category, part of PK
SubCatCode, long/short integer, secondary category, part of PK
SubSubCatName, text, name of category, part of PK
SubSubCatCode, long/short integer, tertiary category code.

Yep, synonyms allowed here, too.

NOW, the many-to-many case you mentioned...

tblCatMapper
CatCode, long/short integer
SubCatCode, long/short integer
SubSubCatCode, long/short integer
ItemCode, long integer, FK to selected item.

You have one entry like this for every item that can be selected by the ItemCode. So a "SELECT ItemCode from tblCatMapper WHERE CatCode = X and SubCatCode = Y and SubSubCatCode = Z ; " selects all items that fall under your selection criteria. Then you have the pointers (ItemCode) to each item and can build your report/display/whatever.

NOTE: This is fixed-structure tree with depth 3. For variable-depth trees, you need a totally different type of selector mechanism that might be more than you want to try right away. Such algorithms exist but they ain't trivial.

 

[webwizz]

different problem

Hi Doc Man,

Thanks again for your help! However, I want something simpler than you described. It's more like the problem in this posting:

http://www.access-programmers.co.uk/forums/showthread.php?t=53715&highlight=categories+subcategories

My database structure would be similar to kcweir's but I don't quite understand the answer JMM gave...can you please describe it more in detail.
Ie. PK and FK and how would I write queries to search? I searched for answers in the forum that JMM provided but couldn't find a match.

Much appreciated.