In an extended sites model you usually start by publishing a store asset store and catalog asset store and then create a logical store which points to store asset store for application code and catalog asset store for catalog information.
Once a logical store has been created the first task is to design a sales catalog which can be used for display purpose on the site.
Master Catalog - Master catalog contains item, relationship between them, price etc.. Master catalog can be shared across multiple sites. Master catalog have some design restrictions which can be overcome by defining a Sales catalog which will be specific to a store.
Sales Catalog - A store could be associated with more than one sales catalog.
In a sales catalog a product can belong to more than one category, Sales catalog is usually used as taxanomy for the site
and serves to display the front end category navigation for an ecommerce site.
Display sequence of categories in a sales catalog is maintained using sequence field, Sales Catalog can be managed by a business user by using Management Center.
STORECAT - This table stores information between store and catalog, this table will have information on both master and sales catalog associated to a store.
STORECAT.MASTERCATALOG = 1 indicates a master catalog.
// Use following SQL to retrieve master and sales catalog associated to a store.
* from catalog where catalog_id in
catalog_id from storecat where storeent_id
(select storeent_id from storeent where identifier='Madisons'))
CATOTOGRP - This table consists of relationship between store catalog (master and Sales) and root categories (AKA Top categories)
// Use following SQL to retrieve top categories associated with a catalog, in this case sales catalog.
select * from catgrpdesc where catgroup_id in
(select catgroup_id from CATTOGRP where catalog_id in
(select catalog_id from catalog where identifier='Madisons')
) and language_id=-1
CATGRPREL - This table consists of relationship between parent category and sub categories, this defines the category level navigation of the site.
// Use following SQL to retrieve sub categories of Root categories (Top level Categories) of a store Master catalog, you may replace the identifier with the name of master or specific sales catalog.
Select * from catgrpdesc where catgroup_id in
(select catgroup_id_child from catgrprel where catgroup_id_parent
in (select catgroup_id from CATTOGRP where catalog_id in
(select catalog_id from catalog where identifier='Madisons')))
CATGPENREL - This table holds the relationship between a sales or master catalog category and products associated to that category, usually you will associated both Product and Item bean of a catentry with the category
//Following SQL retrieves all ProductBean associated with "Lounge Chairs" category
select * from catentdesc where catentry_id in
(select catentry_id from catentry where catentry_id in
(select catentry_id from CATGPENREL where catgroup_id = (Select catgroup_id from catgrpdesc where name='Lounge Chairs'))
Displaying the Sales / Master Catalog on Store
You may extend OOB CategoryDataBean to add custom logic or use it as is to fetch site navigation information in JSP pages.
StoreCatalogDisplay.jsp has been provided as an example to display the store master / sales catalog
<wcbase:useBean id="categoryBean" classname="com.ibm.commerce.catalog.beans.CategoryDataBean" />