I have a simple table:
Categories
----
CategoryID
ParentID
Name
I want to associate my Products to a category so that I can search by category...ok. If I just have a simlpe table:
CategoryProducts
------
CategoryId
ProductId
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if Iclick on Self Help while browsing, I want to see all items underneathPersonal Development, Spiritual and Meditiation. So my questionis is this a good way to store the product-category relationships, orshould I put many entries into CategoryProducts to keep the queriessimlpe and faster? Are they faster doing it this way? In this way therewould be 4 entries for a product in meditation. My personal ideais that adding all entries up a tree arm of a category path will becumbersome to manage, but it does solve the problem of clicking on SelfHelp and seeing all products that exist within sub-categories. Iam sure an SQL query would be able to work this out, but I dont know ifperformance would be something to consider on an ecommerce site? Arethere any patterns fo rthis stuff - seems a reasonably repeatablepattern for business sites?
Thanks,
jr.
It's a pretty standard hierarchical navigation issue. You can adda 'ParentCategory' column to your categories table in order to easilycreate an adjacency hierarchy. This article does a good job ofexplaining how to maintain and navigate those: http://www.windowsitpro.com/Article/ArticleID/8826/8826.html?Ad=1
If querying the adjacecy list gets too difficult, there are some othertechniques that might work. One such technique is thematerialized path, whereby each row maintains a string-basedrepresentation of its role in the hierarchy... so if you had three rows:
Category_Id Category_Name Parent_Category_Id
1 Development 1
2 Spiritual 1
3 Meditation 2
(Note that Development is its own parent)
You could add a materialized path column (which you would also have to maintain):
Category_Id Category_Name Parent_Category_Id Path
1 Development 1 1.
2 Spiritual 1 1.2.
3 Meditation 2 1.2.3.
Now, to get everything under Spiritual:
SELECT *
FROM Tbl
WHERE Path LIKE '1.2.%'
This can work quite well in situations where the hierarchies are wide,but not too deep -- depth causes a problem, since you'd probably wantto index that path, and the maximum rowsize for an index is 900bytes. However, in your case I doubt the hierarchy will be verydeep.
Other options also exist, with increasing levels of complexity. Google for Nested Sets and Nested Intervals. Both of thesetechniques could also apply, but are probably overkill for thisscenario.
|||Super! Thanks Adam.
|||Adam,
I just wish to go over this again so I am clear I have understood whatyou mean. If I want to find out items that exist underneath acurrent level in the category hierarchy, using the path will allow meto do this. I can see that if the hierarchies are large andcomplicated, doing successive recursive queries just isntpractical. I'd like to take this one step further, to see thistopic in the light of two big sites, Amazon.com and eBooks.com. At Amazon, clicking Books and then Biographies and Memoirs from thebrowse option(http://www.amazon.com/exec/obidos/tg/browse/-/2/ref=br_bh_1_bi/103-6368774-8672644)produces a page with a large Browse box, showing a bunch of subcategories. Question 1: does amazon category its products undermore than one category? I think it does, since it would offer greateraccessiblity. Question 2: on this page there is a RelatedCategories section within the Browse box - do you think these relatedcategories can be computed, or are they set up in the database andsimply queried for? Similarly when I browse through the catalogat eBooks.com, when I get to an actual product detail page, there is aRelated Subjects section. For eBooks I cannot work out if theRelated Subjects are based on the categorisation of the product beingviewed, or whether it is decided based on other metadata for the itemthat would allow a category that isnt directly linked to be chosen -such as keywords, or a list of associated categories.
I am trying to work out how to implement a Related Categories piece offunctionality, and have never thought about the problem before. Ican see I could manually add associated subjects/categories to aproduct by using a simple string field:
Products
---
ProductID int
...
CategoryID int
AssociatedCategories varchar(50)
Putting this column in the Products table will work, but I am not sureit is ideal. Then I have the issue of Related Products - do I doit based on category, or some other simple technique such as
AssociatedProducts varchar(50)
I would add a comma separated list of products that are associated tothe Product entry : 1, 5, 56. This again will work but issimplistic. I see the question comes down to "Why is Product Arelated to Product B?". It could be due to some categoryassociation, keywords that are similar, or that other customerspurchased these items having purchased this product. How this isrepresented in the DB I dont know...
Any advice or links to articles would be really appreciated,
thanks
jr.
|||jr, I have been trying to implement adatabase scheme very similar to the one you are describing. Sofar, I have no idea if I'm doing this the most efficient way, so Iwouldn't say my advice is sound. However, after reading over somematerial regarding your question on related products, this is what Ihave come up with in my design:
Product
------
PK ProductID
{fields omitted}
Pro_Related
-------
FK ProductID_A
FK ProductID_B
RelationID
Products.ProductID is the primary key in Products table, whereasProducts_Related.ProductIDA/B are the foreign keys in theProducts_Related table. RelationID is just an arbitrary fieldthat relates to what kind of relationship exists. For example,RelationID=1 could define a similar product, but with a differentproperty such as size. S,M,L, XL could all have entries inPro_Related and have RelationID=1)
Here's a pic to represent what I amdescribing:
On another issue, I am trying to setup the hiearchies forproducts/categories and would be interested to see if you come up witha solid design.
Thanks,
Chris
|||Chris,
So far I havent linked products to products like this. What I dois have a SalesIndex per product. Each product iscategorised. I then look up the top 5 items in the currentcategory (assuming category is enough of a link between items in thatcategory). I did come up with something similar to your designinitially, and I have left my structure in, but it is dormant fornow.
Since I got much of my ideas from good people on this forum, I am happyto share them with you in a more focused way. I am not able togain access to my code relating to categories/product links, but I amhappy to send you some stuff when I can. From memory, I have a categorytable:
Category
======
ID
Title
Linkage
ID Title Linkage
= ============== =======
1 Business & Finance 1.1
2 Personal Finance 1.1.1
3 Personal Investments 1.1.1.1
4 Child Savings 1.1.1.2
Maps to:
Business & Finance 1.1
Personal Finance 1.1.1
Personal Invst. 1.1.1.1
Child Savings 1.1.1.2
I also have a CategoryProducts table:
CategoryProducts
================
CategoryID
ProductID
I can therefore include a product in morethan one category. I can search for all products underneathBusiness & Finance, and page the results too. I can providethis routine later if you wish.
Hope this is helpful Chris,
jr.
No comments:
Post a Comment