IBM WebSphere Commerce 中的关于catalog 的表设计

haishi

在学习IBM WebSphere Commerce的时候，看到关于catalog的表设计，觉得非常灵活强大。
一个store可以对应多个目录，一个目录可以属于多个store，不同子目录在不同store中可以显示，也可以不显示。
Commerce是做大型B2B,B2C网站，基本概念包括：
store  -- 具体的一个网站，实际网站下可以有多个子网站，参考连锁店之类的经营模式。
catalog -- store的主目录
catalog group -- 分组，子目录
catalog entry -- 一类产品，或一个产品，或一个产品包。

one store can have multiple catalogs, on catalog can belong to multiple stores.

TABLES
1. catalog(CATALOG)
PK:catalog_id
(identifier + member_id) 组成index

next: catalog catalog group relation objects

2. catalogdsc(CATALOGDSC)
if catalog without catalogdsc , maybe cannot be viewed in browser, but can ve viewed in accelerator.
for catalogdsc has language_id, so it contains language-dependent imformation.

3. catalog group(CATGROUP)
Used to organize a catalog and help the shopper navigate to the products.

catalog catalog group relation object - links the foremost catalog groups to a catalog
catalog group relation object - links child category groups with their parent group
catalog group catalog entry relation object - links catalog entries with their parent catalog group

4. catalog group attribute(CATGRPATTR)
catagrpattr_id(PK),
language_id(PK),
catgroup_id,
...

It present a view that the shopper uses to compare similar catalog groups.
It includes a sequence column to be used to order the attribute names.

5. Catalog group description(CATGRPDESC)
It contains language-dependent information and an image suitable for viewing.
and a required column: published is important!

thumbnail for gif  -- path?
fullimage for gif  -- path?

6. Catalog catalog group relation(CATTOGRP)
Used catalog_id to find the root categories.
catalog_id(PK),
catgroup_id(PK),
sequence,
catalog_id_link
...
here there is a catalog_id_link bigInt nullable, what purpose?

7. Catalog group relation(CATGRPREL)
relation about group to group
catgroup_id_parent(PK)
catgroup_id_child(PK)
catalog_id(PK)
rule
sequence
catalog_id_link
...
here there is a catalog_id_link bigInt nullable, what purpose?

8. Catalog entry(CATENTRY)
four types:
Item - a tangible unit of merchandise that can be purchased and be shipped.
Product - represents a collection of items that have identical attributes but can be distinguished by their attribute values.
Package - is a collection of items that must be purchased as a single atomic unit, even if its items can be ordered separately.
Bundle - is a collection of items that can be added to the shopping cart with a single selection while its items can be ordered separately.

catentry_id(PK)
member_id
itemspc_id
catenttype_id
mfpartnumber
mfname
markfordelete
url
baseitem_id
...

9. Catalog entry attribute(CATENTATTR)
contains descriptive attributes for catalog entries.
catentattr_id(PK)
language_id
catentry_id
name
value
....

10. Catalog entry description(CATENTDESC)
A catalog entry description object contains language-dependent information and an image suitable for viewing by a shopper.
catentry_id CDATA #REQUIRED
language_id CDATA #REQUIRED
name CDATA #IMPLIED
shortdescription CDATA #IMPLIED
longdescription CDATA #IMPLIED
thumbnail CDATA #IMPLIED
auxdescription1 CDATA #IMPLIED
fullimage CDATA #IMPLIED
auxdescription2 CDATA #IMPLIED
xmldetail CDATA #IMPLIED
available CDATA #REQUIRED
published CDATA #REQUIRED
availabilitydate CDATA #IMPLIED
keyword CDATA #IMPLIED

11. Attribute(ATTRIBUTE)
defines the name, qualified by language, of each attribute used to resolve a product catalog entry into an item that can be added to the shopping cart.
attribute_id CDATA #REQUIRED        (PK)
language_id CDATA #REQUIRED(PK)
attrtype_id CDATA #REQUIRED
name CDATA #IMPLIED
sequence CDATA "0"
description CDATA #IMPLIED
catentry_id CDATA #IMPLIED  -- product's catentry_id


   description2CDATA#IMPLIED
   field1CDATA#IMPLIED
   oidCDATA#IMPLIED
   usageCDATA#IMPLIED    --------Default is "1"----- NULL or "1" indicates SKU-resolution. "2" indicates a descriptive attribute and should not be used to resolve SKUs.
   qtyunit_idCDATA#IMPLIED
   groupnameCDATA#IMPLIED
   noteinfoCDATA#IMPLIED
   multitypeCDATA#IMPLIED
   optcounterCDATA#IMPLIED

      
12. Attribute value(ATTRVALUE)
attrvalue_id CDATA #REQUIRED
language_id CDATA #REQUIRED
attribute_id CDATA #REQUIRED
attrtype_id CDATA #REQUIRED
sequence CDATA "0"
catentry_id CDATA #REQUIRED
name CDATA #IMPLIED

13. Catalog entry relation(CATENTREL)
is used to link a catalog entry to its parent.
--PRODUCT_ITEM: An item can be linked to its product.
--PACKAGE_COMPONENT: A catalog entry of any type can be linked to a
package.
--BUNDLE_COMPONENT: A catalog entry of any type can be linked to a
bundle.

catentry_id_parent CDATA #REQUIRED
catreltype_id CDATA #REQUIRED              -- "PRODUCT_ITEM"....
catentry_id_child CDATA #REQUIRED
sequence CDATA "0"
quantity CDATA #IMPLIED
groupname CDATA #IMPLIED

14. Catalog group catalog entry relation(CATGPENREL)
is used to link a catalog entry to a catalog group. Note that the relationship is qualified by a catalog_id.

catgroup_id CDATA #REQUIRED
catalog_id CDATA #REQUIRED
catentry_id CDATA #REQUIRED
rule CDATA #IMPLIED
sequence CDATA "0"

15. Store catalog(STORECAT)
link catalog objects to a store. The storeent_id is used to find the appropriate set of catalogs to display
catalog_id CDATA #REQUIRED
storeent_id CDATA #REQUIRED
mastercatalog CDATA #IMPLIED
lastupdate CDATA #IMPLIED

16. Store catalog group(STORECGRP)
is used to link catalog group objects to a store.
Typically, a store developer will use the information in these objects to filter the set of catalog groups that are displayed for a given store.
storeent_id CDATA #REQUIRED
catgroup_id CDATA #REQUIRED

17. Store catalog entry(STORECENT)
link catalog entry objects to a store.
Typically, a store developer will use the information in these objects to filter the set of catalog entries that are displayed for a given store.
storeent_id CDATA #REQUIRED
catentry_id CDATA #REQUIRED

18. Display catalog group relation(DISPCGPREL)
is used to link a JSP name to a catalog group. Usually, a default page is provided for an entire site and, when necessary,
an override is provided when the default page is inadequate. Overrides can be selected based on store, member group, device type,
catalog group, language or any combination of these properties.

catgroup_id CDATA #REQUIRED
dispcgprel_id CDATA #REQUIRED   (PK)
language_id CDATA #IMPLIED
devicefmt_id CDATA #REQUIRED
pagename CDATA #REQUIRED
storeent_id CDATA #REQUIRED
mbrgrp_id CDATA #REQUIRED
description CDATA #IMPLIED
rank CDATA #IMPLIED

FOR devicefmt:
XMLHTTP                                                         -10000-
ECSAXXMLHTTP                                                    -10001-
XMLMQ                                                           -20000-
SOAPHTTP                                                        -10003-
BROWSER                                                         -1Http browser
I_MODE                                                          -2I mode
E-mail                                                          -3E-mail display
MQXML                                                           -4XML message recieved via MQ
MQNC                                                            -5Legacy NC message recieved via MQ
SMS                                                             -7SMS messages
AlertPortlet                                                    -8MyAlert messages
webservices                                                     -10webservices

19. Display catalog entry relation (DISPENTREL)
used to link a JSP name to a catalog entry
Overrides can be selected based on store, member group, device type, catalog entry type, catalog entry, language or any combination of these properties.

catentry_id CDATA #REQUIRED
dispentrel_id CDATA #REQUIRED  (PK)
language_id CDATA #IMPLIED
devicefmt_id CDATA #REQUIRED
storeent_id CDATA #REQUIRED
pagename CDATA #REQUIRED
catenttype_id CDATA #REQUIRED
auctionstate CDATA "0"
mbrgrp_id CDATA #REQUIRED
description CDATA #IMPLIED
field1 CDATA #IMPLIED
rank CDATA #IMPLIED