Solved

SQL Categories within Categories with Categories etc

Posted on 2007-03-23
4
721 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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now