|
|
I need to store and retrive hierarchial data. The data represents categories
for a product catalog that can have an undetermind number of subcategories. i
andt to use a recursive table but am not sure if that's right. I have the
recursive idea down but my problem is finding the lowest level category so i
can store it in a new product reecord without knowing that it is lowest level
because i enterded it.
How can I build a querry or form that will displat the data to where i can
select a "node" and have it give me the childern of thet node, then again
until i am at the lowest or "leaf" level? i need this so i can store the leaf
level in the product table. but i cant figure out how to make an interface or
querry that makes this "user" friendly.
|
|
Will this work for you ..........
TblCategory
CategoryID
Category
TblSubcategory
SubcategoryID
CategoryID
Subcategory
TblProduct
ProductID
ProductName
<Other product fields>
CategoryID
SubcategoryID
Subcategories of each Category are recorded in TblSubcategory. When you
record a Product, it is assigned to a Category(CategoryID) and then on your
form you have cascading comboboxes that limit the Product to being assigned
to ONLY a subcategory of the assigned Category.
Steve
"Mailmanny" <Mailmanny[ at ]discussions.microsoft.com> wrote in message
news:0875E91B-0558-4130-AE63-D80098B0C669[ at ]microsoft.com...
[Quoted Text] >I need to store and retrive hierarchial data. The data represents
>categories
> for a product catalog that can have an undetermind number of
> subcategories. i
> andt to use a recursive table but am not sure if that's right. I have the
> recursive idea down but my problem is finding the lowest level category so
> i
> can store it in a new product reecord without knowing that it is lowest
> level
> because i enterded it.
>
> How can I build a querry or form that will displat the data to where i can
> select a "node" and have it give me the childern of thet node, then again
> until i am at the lowest or "leaf" level? i need this so i can store the
> leaf
> level in the product table. but i cant figure out how to make an interface
> or
> querry that makes this "user" friendly.
|
|
hi,
Mailmanny wrote:
[Quoted Text] > I need to store and retrive hierarchial data. The data represents categories
> for a product catalog that can have an undetermind number of subcategories.
Use two tables:
Product: ID (PK, Autonumber) etc.
ProductCategory: ProductID (PK, FK, Long),
(Sub-)CategoryID (PK, FK, Long)
In the table ProductCategory you have a combined primary key (PK) of the
two fields. FK mean you have a foreign key relationship to the
corresponding tables.
> andt to use a recursive table but am not sure if that's right. I have the
> recursive idea down but my problem is finding the lowest level category so i
> can store it in a new product reecord without knowing that it is lowest level
> because i enterded it.
You may give us a concrete example?
> How can I build a querry or form that will displat the data to where i can
> select a "node" and have it give me the childern of thet node, then again
> until i am at the lowest or "leaf" level?
Okay, what does your categories/subcategories look like:
a)
Category 1
Subcategory 11
Subcategory 12
Category 2
Category 3
Subcategory 31
Subcategory 32
Subcategory 33
b)
Category 1
Category 11
Category 111
Category 112
Category 2
Category 21
Category 22
Category 221
Category 3
In the first case you have a table layout like Steve posted consisting
of two tables. In the second case also two tables, but with a different
structure:
Category: ID (PK, Autonumber), Denomination (Text, not NULL) etc.
CategoryStructure: ID (PK, FK, Long), ParentID (FK, Long, not NULL)
There is a 1:1 relationship between Category.ID and
CategoryStructure.ID. Root (top) categories are
SELECT *
FROM Category
WHERE ID NOT IN (SELECT ID FROM CategoryStructure)
This layout should be used, when your categories are changing often.
If your categories are invariant, then you may merge these two tables:
Category: ID (PK, Autonumber), ParentID (FK, Long),
Denomination (Text, not NULL) etc.
ParentID should be NULL for root/top level categories:
SELECT *
FROM Category
WHERE IsNull(ParentID)
mfG
--> stefan <--
|
|
|
[Quoted Text] >Okay, what does your categories/subcategories look like:
>b)
> Category 1
> Category 11
> Category 111
> Category 112
> Category 2
> Category 21
> Category 22
> Category 221
> Category 3
> Category 31
> Category 311
> Category 3111
My categories look like option b
they do not change often but i have a varying number of subcategories form 1
to 4.
The table that I have now looks like
tblCategories:
ID (PK, AutoNumber)
CatName ( text, Not Null)
ParentCatID (Int, FK)
Kind of like what you posted.
>If your categories are invariant, then you may merge these two tables:
> Category: ID (PK, Autonumber), ParentID (FK, Long),
> Denomination (Text, not NULL) etc.
>ParentID should be NULL for root/top level categories:
> SELECT *
> FROM Category
> WHERE IsNull(ParentID)
My only reamaing question is how to take that flat table and us it in a
hierachial way with like a treeview maybee.
I have done it in ASP.NET with C# for a website to view products by
selecting the categories and opening nodes, and when a child node is selected
it populates a view with the products that fit that subcategory. But i cant
figure out how to use that to add products. i want to be able to select the
lowest level of any given category and have that sub(sub(sub))category ID
stored in the products table.
|
|
hi,
mailmanny wrote:
[Quoted Text] > I have done it in ASP.NET with C# for a website to view products by
> selecting the categories and opening nodes, and when a child node is selected
> it populates a view with the products that fit that subcategory. But i cant
> figure out how to use that to add products. i want to be able to select the
> lowest level of any given category and have that sub(sub(sub))category ID
> stored in the products table.
So, when you select a category, you want to select all products assigned
to this category or assigned to its subcategories?
In this case, you need either a helper function, as Access/Jet has no
recursive type of query or you need another way of storing your tree:
Nested Sets
http://www.mvps.org/access/queries/qry0023.htm
mfG
--> stefan <--
|
|
|