I'm including the script for my ProductCategories table but I think it might be very beneficial for you to understand what I'm doing first:
As the name implies, the ProducttCategories table is where I store all product categories for an ecommerce site. Furthermore, the CategoryHierarchy column uses the HierarchyID datatype in SQL Server 2008 so that I can have a hierarchy and make suggestions
to users who are looking for a product in a category along with all its child categories.
Also, in order to make the site as user friendly as possible, I've implemented friendly URLs. Therefore, I need to do a reverse look up when someone requests:
Electronics-DigitalCameras-DigitalSLRs to figure out the CategoryID (int) for the Digital SLR's category.
I wanted to create an index in the database to make sure these look ups would have pretty good performance and that's where I got stuck. First, I got a message that told me that I couldn't create an index because my view was not bound to schema. And when
I tried to do that, I got another error saying "Cannot schema bind view 'dbo.vwProductCategoriesAndPathsOrdered2' because name 'ProductCategories' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
The view simply creates a category path that looks like this: Electronics > Digital Cameras > Digital SLRs
View Complete Post