SQL Categories within Categories with Categories etc

Hi guys, looking for some advice...

Database: MS ACCESS

I'm writting a simple shopping cart and i'm looking to have Categories within Categories within Categories etc. I'm a bit stuck on what the table design and query would be. So far i have this:

tblCategories:
>CatID
>CategoryName

tblProducts:
>ProductID
>ParentCatID
>ChildCatID
>ProductName

Am i going in the right direction with this problem?

Thanks in advance for any help
LVL 3
reiss20Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KelvinYCommented:
Hi reiss20

You've made a reasonable start. Why would you need a parent and child category field in your Products table? To my mind a product should only need to know about the category immediately above it that it falls into. That category needs to know only its immediate parent and so on. You can make the category table self-referencing so that you can have as many levels as you like.  A top level category is simply one that has no parent.

So I would recommend that you have tables like this:

tblCategories:
>CatID
>ParentCatID
>CategoryName

tblProducts:
>ProductID
>CatID
>ProductName

A typical category chain might look like:

Category:
CatID: 1 ParentCatID: <null> CategoryName: Sports
CatID: 2 ParentCatID: 1 CategoryName: Baseball

Product:
ID: 1  CatID: 2 ProductName: Bat

Just one thing to be careful of; If you have an unlimited number of levels for your categories it can lead to some quite scary queries. You may be better off deciding on a fixed number of category levels and then having a separate table for each level. It's less flexible but it will make things simpler.

Regards
  Kelvin
0
reiss20Author Commented:
Thanks for your help so far, much appreciated.

I've modified my table design to reflect your advice and i can now query the db to retrieve the product and its category. But as with my previous table design i can't figure out how to return all categories with  sub categories and products, can't seem to get my head around it e.g.

Category Sports:
        Inner Category Football:
                Product Football Boots
                Product Goal Posts
                Inner Inner Category Footballs:
                        Product Size 4 Ball
                        Product Size 5 Ball
        Inner Category Basketball:
                Product Spike Air Trainers
                Inner Inner Category Basketballs:
                        Product Outdoor ball
                        Product Indoor Ball
Category Movies:
        Inner Category Films beginning with R:
                Product Rambo 1
                Product Rambo 2
                Product Rambo 3
etc....


Can this be done witha query or will i have to use some logic in my server side code?

Thanks again




0
KelvinYCommented:
From the sample data that you have supplied I can see that one of the problems you are going to encounter is handling products with a different number of category levels. For instance footbal boots has 2 catergories above it, but size 4 ball has 3. Provide that there is a reasonable limit to the number of category levels you can handle it with multiple joins. Your actual query will depend of whether you are trying to retrieve just one product or a list of products.

For a single product, if you know the number of category levels, it's simple. (You may need a little bit of code to establish the number of levels)
e.g. A 3 category level product like a size 4 ball

SELECT tblCategories_2.CategoryName, tblCategories_1.CategoryName, tblCategories.CategoryName, tblProducts.ProductName
FROM ((tblCategories INNER JOIN tblProducts ON tblCategories.CatID = tblProducts.CatID) INNER JOIN tblCategories AS tblCategories_1 ON tblCategories.ParentCatID = tblCategories_1.CatID) INNER JOIN tblCategories AS tblCategories_2 ON tblCategories_1.ParentCatID = tblCategories_2.CatID
WHERE (((tblProducts.ProductID)=[ProductID]));

A 2 level product like goal posts is a bit simpler:

SELECT tblCategories_1.CategoryName, tblCategories.CategoryName, tblProducts.ProductName
FROM (tblCategories INNER JOIN tblProducts ON tblCategories.CatID = tblProducts.CatID) INNER JOIN tblCategories AS tblCategories_1 ON tblCategories.ParentCatID = tblCategories_1.CatID
WHERE (((tblProducts.ProductID)=[ProductID]));

If you want a list of products you will need to resort to a union query. e.g. here is a query that will list all 2 and 3 level products

SELECT tblCategories_2.CategoryName, tblCategories_1.CategoryName, tblCategories.CategoryName, tblProducts.ProductName
FROM ((tblCategories INNER JOIN tblProducts ON tblCategories.CatID = tblProducts.CatID) INNER JOIN tblCategories AS tblCategories_1 ON tblCategories.ParentCatID = tblCategories_1.CatID) INNER JOIN tblCategories AS tblCategories_2 ON tblCategories_1.ParentCatID = tblCategories_2.CatID
WHERE tblCategories_2.ParentCatID = 0
UNION ALL
SELECT tblCategories_1.CategoryName, tblCategories.CategoryName, NULL, tblProducts.ProductName
FROM (tblCategories INNER JOIN tblProducts ON tblCategories.CatID = tblProducts.CatID) INNER JOIN tblCategories AS tblCategories_1 ON tblCategories.ParentCatID = tblCategories_1.CatID
WHERE tblCategories_1.ParentCatID = 0

Note that the second query in the union has a null column to pad it out to the same number of columns as the first one. Also note the WHERE clauses to filter out top level categories, to prevent duplicate records being output.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
reiss20Author Commented:
Hi KelvinY,

Thanks again for the advice. Decided just to have 3 category levels to keep things simple.

Sorry for the delay in getting back to you, was away on business.

Cheers,

Reiss
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.