Solved

SQL Categories within Categories with Categories etc

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

22 Experts available now in Live!

Get 1:1 Help Now