?
Solved

SQL Categories within Categories with Categories etc

Posted on 2007-03-23
4
Medium Priority
?
733 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:reiss20
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:KelvinY
ID: 18783570
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
 
LVL 3

Author Comment

by:reiss20
ID: 18783974
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
 
LVL 8

Accepted Solution

by:
KelvinY earned 2000 total points
ID: 18785123
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
 
LVL 3

Author Comment

by:reiss20
ID: 18800525
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question