Category Table Setup (1 Viewer)

[gucci]

Hello

I've being struggling with designing a MS Access Database Schema. It is for a Category Tree with 4 Category Levels. What is making it difficult is that and Category Level can be linked to any Category one level higher.

For example;

Computing, Electronics, IT & AVI >> Networking >> Physical Security >> IP Cameras
Computing, Electronics, IT & AVI >> Audio, Video & Image >> Digital Cameras & Video >> IP Cameras

See how IP Cameras is linked to both Physical Security and Digital Cameras & Video.

What I have done is setup a table for Levels, then a table for Categories and finally a table for Category Groups.

The fields in table for Levels are;
Level_ID
Name
Description

The fields in table for Categories are;
Cat_ID
Name
Description
Level_ID

The fields in table for Category Groups are;
Cat_Group_ID
Cat_Level1_ID
Cat_Level2_ID
Cat_Level3_ID
Cat_Level4_ID

So just in case it is not clear the Cat_Level1_ID, Cat_Level2_ID, Cat_Level3_ID, Cat_Level4_ID field are bound to Cat_ID

My first question is the following. Is this the correct table structure to use or would there be a better method. Like having different Category Level Link Groups? Level 1 to Level 2, Level 2 to Level 3 and so on.

Thanks

 

[jdraw]

Can you provide a definition of your levels in plain English?

Is it your rule that Items at level 4 are unique and can only be associated with 1 category tree?

Can an IP Camera be hardwired or wireless? If a camera supports both/either, couldn't it be in more than 1 category?

Do you have rules for classifying/categorizing and item? Have you tested these with other people?
Unless you are working with some standardized classification/coding structure, you will likely find that people may not classify things consistently.
If you take 10-20 items, can you and a colleague agree on the classification?

 

[CJ_London]

really depends on how the data is going to be used and managed - what happens if you have a 5th level for example, it will require changes to one or more of tables, queries, forms, reports and code. And it will get quite complex to manage situations where there are less than 4 levels.

An alternative is some form of recursive data set - slightly more complex in your case since you can arrive at the 'ultimate' child from the same initial parent using two different routes. Access does not provide a method of running a recursive query, but it can be done with the use of VBA.

As I said, your solution may be the only workable solution depending on what you are trying to do, but I would also research online for different relationship structures - binary trees and nested set trees come to mind although they may need a bit of tweaking to cope with the 'ultimate' child.

 

[MarkK]

I would do the tables like...

tCategory
CategoryID (Primary Key)
Name
Description

tCategoryJoin 'defines the entire category hierarchy
CategoryJoinID (PK)
ParentID (Foreign Key, CategoryID of Parent Category)
ChildID (FK, CategoryID of Child Category)

I think that would be sufficient to model what you've described.

 

[gucci]

Can you provide a definition of your levels in plain English?

Level 1 = Department
Level 2 = Parent Category
Level 3 = SubCategory
Level 4 = Sub-SubCategory

Is it your rule that Items at level 4 are unique and can only be associated with 1 category tree?

A 4 level category tier is the MAX anyone should go with a category system but it is possible to go further. I woudl use filters after level 4. A level 4 category can be linked to any number of level 3 categories.

Can an IP Camera be hardwired or wireless? If a camera supports both/either, couldn't it be in more than 1 category?

Of course an IP camera can be hardwired or wireless but that destintion will be narrowed by filters.

Do you have rules for classifying/categorizing and item? Have you tested these with other people?
Unless you are working with some standardized classification/coding structure, you will likely find that people may not classify things consistently.
If you take 10-20 items, can you and a colleague agree on the classification?

I already know my category groups and yes others will have different classification structures. When they do I'll map their categories to mine so products get listed where I want.

My question is in relation to how best to setup the tables for my category structure so that categories in one level can be linked to any number of categories in a level higher. my Level naming may be confusing as Level 4 is the lowest level and Level 1 is the higest.

Thanks

 

[gucci]

really depends on how the data is going to be used and managed - what happens if you have a 5th level for example, it will require changes to one or more of tables, queries, forms, reports and code. And it will get quite complex to manage situations where there are less than 4 levels.

I won't be ever using 5 Levels but less than 4 is possible. I can't see why less than 4 would be and issue.

Thanks

 

[gucci]

I would do the tables like...

I think that would be sufficient to model what you've described.

So not use Levels at all, just link any category to any other category, would that not get messy when you have thousands of categories? I mean not having them spearated in to levels.

Thanks

 

[jdraw]

One of the best resources for SQL is Rudy Limebank(r937).
Here is a post he made regarding categories/subcategories.

 

[MarkK]

So not use Levels at all, just link any category to any other category, would that not get messy when you have thousands of categories? I mean not having them spearated in to levels.

Of course there are levels. The parent and child are related in the join table, and it is very simple and tidy.

jdraw, that link is very good, but the OP wants a category to potentially have more than one parent, and that, minimally, is a many-to-many relationship between categories, so he needs a join table, IMO.

 

[CJ_London]

I can't see why less than 4 would be and issue.

As I said, it depends on what you are doing - I'm assuming your bottom category would be the final category before listing products.

So how would you list products by category? using the level 4 category - fine, but if there are only 3 categories, you need to link to level 3, ditto for a level 2 category - that query starts to get messy and slow. Or perhaps you have different queries and code to decide which one to use?

Try thinking of the problem from the other end - here is a product - which category(s) does it belong to? and work back from there

edit: to add to Markks comment, think of the product as being the parent with categories being one or more children

 

[jdraw]

Markk,

I agree given the multi-parent idea. (network structure)
I am wondering if the OP has tried some sample data and tried search and retrieval.
I guess I'm trying to see the business issue/requirement for which this is the solution, especially if a Product could be assigned to several categories, and possibly at different levels.

 

[MarkK]

jdraw, the OPs brain may be melted cheese right now, considering his solution was three tables, and--but for the multiple parent thing--the best solution is actually just one self-referencing table, as per your link. And a self-referencing table can melt your cheese the first time you try to wrap your brain around it.

So we'll see what the OP has to say. For me though, if I was going to make categories have multiple parents, I would want products to belong to only ONE category, otherwise the structure gets just a little too free-form to be useful.

 

[gucci]

Hello and thanks for your help. The product will only belong to one category but that category can be a Child (as Mark puts it) to more than one Parent Category.

The only issue with marks solution is I'm going to find it hard to figure out how to import the category tree from a Product Data Feed that already contains the structure I want. Well it contains it for my first Level 1 category which is 'Computing, Electronics, IT & AVI'. The data feed contains thousands of products but 3 columns represent categories. First I imported my Level 2 Categories removed the duplicates and then imported them in my category table, did the same for Level 3 and Level 4 Categories. The next step was to import all tree columns and remove the duplicate category trees and I was able to that. I suppose I could do what mark is suggesting and import Level 2 and Level 3 columns and remove the duplicates and then import that into the join table. Then do the same for Level 3 and Level 4 columns


Mark, using your method how would I list the categories as a category tree, how would I tell the difference between Level 1, 2, 3 parent categories? Would I still need a CatGroup table?


Thanks again,

 

[MarkK]

To navigate a tree you use 'recursion,' which is what it's called when a subroutine calls itself. To write the tree, you call a CreateNodes() routine with no parent, and open a recordset of all the nodes with no parent, which are your level 1 nodes.

public sub CreateNodes(ParentID as long)
   1) Open recordset of all nodes having "ParentID = " & ParentID
   2) Enumerate Records in said recordset
       a) Create current node
       b) Re-call CreateNodes() with current node's ID as the next level parent [COLOR="Green"]'recursion[/COLOR]
   3) Loop to 2 until .eof
end sub

Then, for each node in that recordset, you call CreateNodes() from itself, with the ID of the current node. In this way, you have one simple routine that creates child nodes, and given that every node that has child nodes is itself a child, and given that your whole tree is connected this way, your whole tree is rendered with one simple algorithm in one simple routine.

Makes sense?

 

[gucci]

I'm afraid Mark that this was alittle over my head. The category tree structure we use has upto 4 tiers so I can't just show 2 tier, like parent --> child. I need
Category --> Categoy --> category --> Category.

Thanks

 

[MarkK]

Yeah, no worries, all the best.

 

[jdraw]

????Can you give us some real data categorized as you need(foresee) it?
Typically, categories at the same level have some some feature that says this is the top level in this "category" eg. catA Hardware, catB Software. I'm not following Category-->Category-->Category, so a real example would be helpful.

 

[gucci]

????Can you give us some real data categorized as you need(foresee) it?
Typically, categories at the same level have some some feature that says this is the top level in this "category" eg. catA Hardware, catB Software. I'm not following Category-->Category-->Category, so a real example would be helpful.

OK, so I've got the following categories,
'Computing, Electronics, IT & AVI'
'Computing'
'Laptops'
'Rugged Notebooks'

Using Markk's join method I would do the following.

I link child category 'Computing' to parent category 'Computing, Electronics, IT & AVI'.
Then child category 'Laptops' to parent categoroy 'Computing'
Then child category 'Rugged Notebooks' to parent category 'Laptops'

My question is how to I show the category tier tree;
'Computing, Electronics, IT & AVI >> Computing >> Laptops >> Rugged Notebooks'
on the front end.

Paul

 

[jdraw]

Here is a method I found, but it may not meet your needs.

025 Information and Communications technology

025010 All communications equipment companies

025010080 Wireless communication
025010080001 Antennas
025010080002 Cellular/(PCS)Personal communications service
025010080004 Fixed Point-to-Multipoint 
025010080003 Fixed Point-to-Point
025010080007 Mobile
025010080008 Paging
025010080005 Telemetry
025010080006 Towers 
025010080009 Others 

025028 All Assistive Device Companies

025028005 Accessible Web Consultants
025028014 Accommodation Consultants  
025028012 Aids to Daily Living
025028003 Alternative and Augmentative Communications 
025028001 Blind 
025028002 Deaf 
025028009 Ergonomics 
025028011 Hard of Hearing 
025028010 Low Vision 
025028004 Mobility 
025028006 Multiple Format Companies 
025028008 Prosthetics and Orthotics 
025028015 Research Centres 
025028013 Training and Evaluation 

025031 Electronic Commerce Technology Suppliers

The codes represent a 4 level hierarchy (3 digits per level).
The classification was done by researchers in the IT field.
The level/tags were used to identify Companies in this line of business.

MainTopic-------025...........Information and Communications technology
Level       1---025010.........All communications equipment companies
Level       2---025010080.......Wireless Communication
Level       3---025010080001.......Antennas
Level       3---025010080002.......Cellular/(PCS)Personal communications service
Level       3---025010080005.......Telemetry
Level       1---025028.........All Assistive Device Companies
Level       2---025028008.........Prosthetics and Orthotics
Level       2---025028005.........Accessible Web Consultants
etc.

 

[MarkK]

I posted a pseudo-code algorithm to navigate a tree earlier in this thread. You can also post your Db if you have those tables set up, and I might get time to look at it, or point you in a useful direction. If the tables are right, the code is not complicated.