Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Categories within Categories with Categories etc

Posted on 2007-03-23
4
Medium Priority
?
731 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

618 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