handling subcategories

[Francis]

hello,

i would like to know the easier way to implement in a database, a
category, sub category structure.

like i have products, that are categorized like this:
category>subcategory>subcategory>product

How do we handle this situations (data structure, form structure and vb
structure)
Thanks
Francis

 

[Allen Browne]

Hmm. Big question. Not sure there's a one-size-fits-all solution, so
hopefully you will get some diverse answers.

For starters, one of the basic rules of normalization is that you must not
store dependent data. So, if you have relationships like this:
1 Category > many Subcategory
1subcategory > many Product
you need store only the Product. The subcategory and category can be derived
from that. If necessary, you can *interface* it with (unbound) combos that
filter the Product down by category, by restricting its RowSource as
described here:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

Things get a bit more involved if you have any of these situations:
- A product can belong to multiple categories (e.g. a particular drill might
be categorized under Handyman, and also under Professional.)
- The categories can be considered products in their own right (Bill of
Materials structure.)
- The number of nested categories vary (e.g. sub subcategories may have
further sub-sub categories.)

I suspect you had something more specific in mind. Feel free to post more
details, and hopefully others will join in.

 

[Francis]

Ok lets make it simple, goint to b epractical and not trying to
comlicate, only need is a way to put in a form viewable and editable,
the following:

Category:
IDCategory (autonum-required-primary key)
Category
cat_desc

Subcategory:
IDSubcategory (autonum-required-primary key)
Subcategory
subcat_desc

Choose:
IDChoose (autonum-required-primary key)
IDCategory
IDsubcategory
choose_desc

Gracias

Allen Browne escreveu:

 

[Francis]

Ok lets make it simple, goint to b epractical and not trying to
comlicate, only need is a way to put in a form viewable and editable,
the following:

Category:
IDCategory (autonum-required-primary key)
Category
cat_desc

Subcategory:
IDSubcategory (autonum-required-primary key)
Subcategory
subcat_desc

Choose:
IDChoose (autonum-required-primary key)
IDCategory
IDsubcategory
choose_desc

Gracias

Allen Browne escreveu:

 

[Allen Browne]

Perhaps I am being confused by the language, but I would expect a
subcategory to be a subset of a category.

Therefore:

Category table:
IDCategory (autonum-required-primary key)
Category

Subcategory:
IDSubcategory (autonum-required-primary key)
Subcategory
IDCategory relates to Category.IDCategory

Choose:
IDChoose (autonum-required-primary key)
IDsubcategory relates so Subcategory.IDSubcategory

Now when you choose an item, you know the subcategory it belongs to (so need
to store that again), and you know what Category that subcategory belongs to
(so no need to store that either.)

Or perhaps I have not understood you.

 

[Francis]

Indeed, your structure is correct, i was using a different one, bcose i
didnt pay attention, that one SubCat can only be related to one and
only one Category (hence your approach pretty does the job).

Now i have t put this in a form... i will give the feedback when i have
it... any suggestions post them here

thanks Allen.
Francis.




Allen Browne escreveu:

 

[Francis]

Ok Thanks a Lot Allen,
with your idea and another contributor, i manage to resolve my
database, not after a lot of effort, but if it wasn't you i certainly
would not have done it so quickly.

Basically i changed the data structure, from what i had initially,
mainly because one subcategory only has another category.

Thanks again,

Francis (Évora Portugal)

Francis escreveu: