|
|
|
|
|
|
The Product Catalog Schema
This chapter documents the database schema for the WebLogic Commerce Server product catalog. The following topics are covered:
The Entity-Relation Diagram
Figure 2-1 shows the Entity-Relation diagram for the WebLogic Commerce Server core product catalog tables in the Commerce database. The data types shown are for an Oracle database. The data types have different syntax for Cloudscape databases, but the type of storage is the same. See the subsequent sections in this chapter for information about the data type syntax for both Oracle and Cloudscape databases.
Figure 2-1 Entity-Relation Diagram for the Core Product Catalog Tables (Shown with Oracle Data Types)
Figure 2-2 shows the database tables that are used to store different types of custom attributes for product items. These custom attributes are optionally defined by a Web site administrator and are implemented as Property Sets. For more information about defining custom attributes, see the section Define Custom Attributes for Items.
Figure 2-2 Custom Attribute Tables for Catalog
Explanations for the columns in the tables are provided in the remainder of this chapter.
The Catalog Schema Is Based on Dublin Core Standard
The metadata for items in WebLogic Commerce Server product catalog are based on the Dublin Core Metadata Open Standard. This standard offers a number of advantages for a Web-based catalog:
The Dublin Core is intended to be usable by non-catalogers as well as resource description specialists. Most of the elements have commonly understood semantics that is roughly the complexity of a library catalog card.
In an Internet environment, disparate description models interfere with the ability to search across discipline boundaries. Promoting a commonly understood set of descriptors that helps to unify other data content standards increases the possibility of semantic interoperability across disciplines.
Recognition of the international scope of resource discovery on the Web is critical to the development of effective discovery infrastructure. The Dublin Core benefits from active participation and promotion in some 20 countries in North America, Europe, Australia, and Asia.
The Dublin Core provides an economical alternative to more elaborate description models such as the full MARC cataloging of the library world. Additionally, Dublin Core includes sufficient flexibility and extensibility to encode the structure and more elaborate semantics inherent in richer description standards
The diversity of metadata needs on the Web requires an infrastructure that supports the coexistence of complementary, independently maintained metadata packages. The World Wide Web Consortium (W3C) has begun implementing an architecture for metadata for the Web. The Resource Description Framework, or RDF, is designed to support the many different metadata needs of vendors and information providers. Representatives of the Dublin Core effort are actively involved in the development of this architecture, bringing the digital library perspective to bear on this important component of the Web infrastructure.
For more information about the Dublin Core Metadata Open Standard, please see http://purl.org/dc.
The WLCS_CATEGORY Database Table
Table 2-1 describes the metadata for the WebLogic Commerce Server WLCS_CATEGORY table. This table is used to store categories in the Commerce database. The descriptions shown in the table reflect the "recommended best practice" for the use of that field by the Dublin Core standard.
|
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
|
CATEGORY_ID |
VARCHAR(20) |
VARCHAR2(20) |
A unique identifier for a category; the primary key for this table. This field cannot be NULL. All other fields in the WLCS_CATEGORY table can be NULL. |
|
PARENT_ID |
VARCHAR(20) |
VARCHAR2(20) |
The value of the CATEGORY_ID of the parent category in the hierarchy of categories that comprise your product catalog. If this is a top-level user-defined category, the PARENT_ID will be com.beasys.ROOT. |
|
NAME |
VARCHAR(50) |
VARCHAR2(50) |
The name of the category in the product catalog. |
|
SOURCE |
VARCHAR(30) |
VARCHAR2(30) |
A reference to a category from which the present category is derived. |
|
LANG |
VARCHAR(30) |
VARCHAR2(30) |
A language of the intellectual content of the category. The recommended best practice for the values of the language element is defined by RFC 1766, which includes a two-letter Language Code (taken from the ISO 639 standard), such as: en for English; fr for French, or de for German. The language code can, optionally, be followed by a two-letter Country Code (taken from the ISO 3166 standard [ISO3166]). For example, en-uk for English used in the United Kingdom. |
|
RELATION |
VARCHAR(30) |
VARCHAR2(30) |
A reference to a related category. |
|
COVERAGE |
VARCHAR(30) |
VARCHAR2(30) |
The extent or scope of the content of the category. |
|
RIGHTS |
VARCHAR(30) |
VARCHAR2(30) |
Information about rights held in and over the category. |
|
CREATOR |
VARCHAR(50) |
VARCHAR2(50) |
An entity primarily responsible for making the content of the category. |
|
PUBLISHER |
VARCHAR(50) |
VARCHAR2(50) |
An entity responsible for making the category available. |
|
CONTRIBUTOR |
VARCHAR(50) |
VARCHAR2(50) |
An entity responsible for making contributions to the content of the category. |
|
CREATION_DATE |
TIMESTAMP |
DATE |
A date associated with an event in the life cycle of the category. Recommended best practice for encoding the date value is defined in a profile of ISO 8601 and follows the YYYY-MM-DD format. |
|
MODIFIED_DATE |
TIMESTAMP |
DATE |
A date associated with an event in the life cycle of the category, such as an update or insert by the DBLoader program that is provided with WebLogic Commerce Server. The recommended best practice for encoding the date value is defined in a profile of ISO 8601 and follows the YYYY-MM-DD format. |
|
SMALL_IMG_TYPE |
INT |
NUMBER(3) |
A type field of your own design that relates to the graphic. For example, you can implement your own numbering scheme, such as: 0 = display a low resolution graphic for users with low bandwidth 1 = display a high resolution graphic for users with high bandwidth |
|
SMALL_IMG_LANG |
VARCHAR(30) |
VARCHAR2(30) |
The language of the thumbnail image for the category. For related information, see the description of the LANG column. |
|
SMALL_IMG_NAME |
VARCHAR(50) |
VARCHAR2(50) |
The name of the thumbnail image for the category. |
|
SMALL_IMG_URL |
VARCHAR(256) |
VARCHAR2(256) |
The URL of the thumbnail image for the category. |
|
SMALL_IMG_ALT_TEXT |
VARCHAR(256) |
VARCHAR2(256) |
The alternate text to display when the user has their cursor over the thumbnail image for the category, or if they have disabled the display of graphics in their browser settings. |
|
LARGE_IMG_TYPE |
INT |
NUMBER(3) |
A type field of your own design that relates to the graphic. For example, you can implement your own numbering scheme, such as: 0 = display a low resolution graphic for users with low bandwidth 1 = display a high resolution graphic for users with high bandwidth |
|
LARGE_IMG_LANG |
VARCHAR(30) |
VARCHAR2(30) |
The language of the full-size image for the category. For related information, see the description of the LANG column. |
|
LARGE_IMG_NAME |
VARCHAR(50) |
VARCHAR2(50) |
The name of the full-size image for the category. |
|
LARGE_IMG_URL |
VARCHAR(256) |
VARCHAR2(256) |
The URL of the full-size image for the category. |
|
LARGE_IMG_ALT_TEXT |
VARCHAR(256) |
VARCHAR2(256) |
The alternate text to display when the user has their cursor over the full-size image for the category, or if they have disabled the display of graphics in their browser settings. |
|
DISPLAY_JSP_URL |
VARCHAR(256) |
VARCHAR2(256) |
The URL to the JSP used to display the category. For example: /commerce/catalog/includes/category.jsp |
|
SHORT_DESC |
VARCHAR(50) |
VARCHAR2(50) |
A short description of the content of the category. |
|
LONG_DESC |
VARCHAR(2000) |
VARCHAR2(256) |
A long description of the content of the category. |
See the section The SQL Files and Defined Constraints for information about the constraint defined for this table.
The WLCS_PRODUCT Database Table
Table 2-2 describes the metadata for the WebLogic Commerce Server WLCS_PRODUCT table. This table is used to store item records in the Commerce database. The descriptions shown in the table reflect the "recommended best practice" for the use of that field by the Dublin Core standard.
Table 2-2 WLCS_PRODUCT Database Table
|
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
|
SKU |
VARCHAR(40) |
VARCHAR2(40) |
A unique identifier (the "Stock Keeping Unit," or SKU) for a product item. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_PRODUCT table can be NULL. |
|
IN_STOCK |
CHAR(1) |
VARCHAR2(1) |
Provide 1 if in stock, or 0 if out of stock. |
|
VISIBLE |
CHAR(1) |
VARCHAR2(1) |
Indicates whether the item should be displayed to the user. Enter 1 if visible or 0. If not specified in the database, the default is 1. See the section Controlling the Visibility of Items in the Catalog for important information about this field. |
|
TAX_CODE |
VARCHAR(10) |
VARCHAR2(10) |
The code used by the Taxware® system to identify the specific tax category to which this item belongs. |
|
SHIPPING_CODE |
VARCHAR(10) |
VARCHAR2(10) |
The code used by the shipping company for this item. |
|
NAME |
VARCHAR(100) |
VARCHAR2(100) |
A name given to the product item. |
|
SOURCE |
VARCHAR(30) |
VARCHAR2(30) |
A reference to another product item from which the present item is derived. |
|
LANG |
VARCHAR(30) |
VARCHAR2(30) |
A language of the intellectual content of the product item. The recommended best practice for the values of the language element is defined by RFC 1766, which includes a two-letter Language Code (taken from the ISO 639 standard), such as: en for English; fr for French, or de for German. The language code can, optionally, be followed by a two-letter Country Code (taken from the ISO 3166 standard). For example, en-uk for English used in the United Kingdom. |
|
RELATION |
VARCHAR(30) |
VARCHAR2(30) |
A reference to a related product item. |
|
COVERAGE |
VARCHAR(30) |
VARCHAR2(30) |
The extent or scope of the content of the product item. |
|
RIGHTS |
VARCHAR(30) |
VARCHAR2(30) |
Information about rights held in and over the item. |
|
FORMAT |
VARCHAR(30) |
VARCHAR2(30) |
The physical or digital manifestation of the item. |
|
TYPE |
VARCHAR(30) |
VARCHAR2(30) |
The nature or genre of the content of the item. |
|
MSRP_CURRENCY |
VARCHAR(30) |
VARCHAR2(30) |
The currency type of the manufacturer's recommended price. |
|
MSRP_AMOUNT |
DOUBLE PRECISION |
NUMBER(16,4) |
The manufacturer's recommended price. |
|
PRICE_CURRENCY |
VARCHAR(30) |
VARCHAR2(30) |
The currency type of our catalog price for this item. |
|
PRICE_AMOUNT |
DOUBLE PRECISION |
NUMBER(16,4) |
Our current price for this item in the catalog. |
|
ESTIMATED_SHIP |
VARCHAR(100) |
VARCHAR2(100) |
Inventory: number of days/weeks before the item can be shipped. |
|
SPECIAL_NOTES |
VARCHAR(100) |
VARCHAR2(100) |
Inventory related message to display with the item. |
|
CREATOR |
VARCHAR(50) |
VARCHAR2(50) |
An entity primarily responsible for making the content of the product item. |
|
PUBLISHER |
VARCHAR(50) |
VARCHAR2(50) |
An entity responsible for making the product item available. |
|
CONTRIBUTOR |
VARCHAR(50) |
VARCHAR2(50) |
An entity responsible for making contributions to the content of the product item. |
|
CREATION_DATE |
TIMESTAMP |
DATE |
A date associated with an event in the life cycle of the product item. The recommended best practice for encoding the date value is defined in a profile of ISO 8601 and follows the YYYY-MM-DD format. |
|
MODIFIED_DATE |
TIMESTAMP |
DATE |
A date associated with an event in the life cycle of the item, such as an update or insert by the DBLoader program that is provided with WebLogic Commerce Server. The recommended best practice for encoding the date value is defined in a profile of ISO 8601 and follows the YYYY-MM-DD format. |
|
SMALL_IMG_TYPE |
INT |
NUMBER(3) |
A type field of your own design that relates to the graphic. For example, you can implement your own numbering scheme, such as: 0 = display a low resolution graphic for users with low bandwidth 1 = display a high resolution graphic for users with high bandwidth |
|
SMALL_IMG_LANG |
VARCHAR(30) |
VARCHAR2(30) |
The language of the thumbnail image for the item. For related information, see the description of the LANG column. |
|
SMALL_IMG_NAME |
VARCHAR(50) |
VARCHAR2(50) |
The name of the thumbnail image for the item. |
|
SMALL_IMG_URL |
VARCHAR(256) |
VARCHAR2(256) |
The URL of the thumbnail image for the item. |
|
SMALL_IMG_ALT |
VARCHAR(256) |
VARCHAR2(256) |
The alternate text to display when the user has their cursor over the thumbnail image for the item, or if they have disabled the display of graphics in their browser settings. |
|
LARGE_IMG_TYPE |
INT |
NUMBER(3) |
A type field of your own design that relates to the graphic. For example, you can implement your own numbering scheme, such as: 0 = display a low resolution graphic for users with low bandwidth 1 = display a high resolution graphic for users with high bandwidth |
|
LARGE_IMG_LANG |
VARCHAR(30) |
VARCHAR2(30) |
The language of the full-size image for the item. For related information, see the description of the LANG column. |
|
LARGE_IMG_NAME |
VARCHAR(50) |
VARCHAR2(50) |
The name of the full-size image for the item. |
|
LARGE_IMG_URL |
VARCHAR(256) |
VARCHAR2(256) |
The URL of the full-size image for the item. |
|
LARGE_IMG_ALT |
VARCHAR(256) |
VARCHAR2(256) |
The alternate text to display when the user has their cursor over the full-size image of the item, or if they have disabled the display of graphics in their browser settings. |
|
SUM_DISPLAY_JSP |
VARCHAR(256) |
VARCHAR2(256) |
The URL to the JSP used to display the item in summary form. For example: /commerce/catalog/includes/itemsummary.jsp |
|
DET_DISPLAY_JSP |
VARCHAR(256) |
VARCHAR2(256) |
The URL to the JSP used to display the item in detailed form. For example: /commerce/catalog/includes/itemdetails.jsp |
|
SHORT_DESC |
VARCHAR(256) |
VARCHAR2(256) |
A short description of the content of the product item. |
|
LONG_DESC |
VARCHAR(2000) |
VARCHAR2(2000) |
A long description of the content of the product item. |
See the section The SQL Files and Defined Constraints for information about the constraint defined for this table.
The WLCS_PRODUCT_CATEGORY Database Table
Table 2-3 describes the metadata for the WebLogic Commerce Server WLCS_PRODUCT_CATEGORY table in the Commerce database. This table is used to join categories and items.
|
Column Name |
Cloudscape Type |
Oracle Type |
Description |
|---|---|---|---|
|
SKU |
VARCHAR(40) |
VARCHAR2(40) |
A unique identifier (the "Stock Keeping Unit," or SKU) for an item. NOT NULL. |
|
CATEGORY_ID |
VARCHAR(20) |
VARCHAR2(20) |
A unique identifier for a category. NOT NULL. |
See the section The SQL Files and Defined Constraints for information about the constraint defined for this table.
The WLCS_PRODUCT_KEYWORD Database Table
Table 2-4 describes the metadata for the WebLogic Commerce Server WLCS_PRODUCT_KEYWORD table in the Commerce database. This table stores the keywords that you associate with each product item. The keywords enable rapid retrieval of item records via the Search functions on the Web site's pages or Administration pages.
|
Column Name |
Cloudscape Type |
Oracle Type |
Description |
|---|---|---|---|
|
KEYWORD |
VARCHAR(30) |
VARCHAR2(30) |
Contains a keyword that you associate with the product item assigned to the unique SKU. NOT NULL. Recommendation: for a given item, select a value from a controlled vocabulary or formal classification scheme implemented in your company. |
|
SKU |
VARCHAR(40) |
VARCHAR2(40) |
A unique identifier (the "Stock Keeping Unit," or SKU) for an item. NOT NULL. |
See the section The SQL Files and Defined Constraints for information about the two constraints defined for this table.
The WLCS_CAT_PROP_* Database Tables for Custom Attributes
This section describes several database tables that are used to store different types of custom attributes for product items. These custom attributes are optionally defined by a Web site administrator and are implemented as Property Sets. For more information about defining custom attributes, see the section Define Custom Attributes for Items.
The WLCS_CAT_ENTITY_ID Database Table
Table 2-5 describes the metadata for the WebLogic Commerce Server WLCS_CAT_ENTITY_ID table in the Commerce database. This table stores unique identification numbers for configurable entities.
|
Column Name |
Cloudscape Type |
Oracle Type |
Description |
|---|---|---|---|
|
JNDI_HOME_NAME |
VARCHAR(256) |
VARCHAR2(256) |
The class name for the configurable entity. Either: com.beasys.ebusiness.catalog.ProductCategory or com.beasys.ebusiness.catalog.ProductItem |
|
PK_STRING |
VARCHAR(480) |
VARCHAR2(480) |
The primary key string for the category or item. |
|
ENTITY_ID |
LONGINT |
NUMBER |
NOT NULL |
The WLCS_CAT_PROP_ID Database Table
Table 2-6 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_ID table in the Commerce database. This table stores unique identification numbers for scoped property names that are associated with configurable entities.
|
Column Name |
Cloudscape Type |
Oracle Type |
|---|---|---|
|
ENTITY_ID |
LONGINT |
NUMBER |
|
SCOPE_NAME |
VARCHAR(100) |
VARCHAR2(100) |
|
PROPERTY_NAME |
VARCHAR(100) |
VARCHAR2(100) |
|
PROPERTY_TYPE |
INT |
NUMBER |
|
PROPERTY_META_DATA |
LONGINT |
NUMBER |
|
SCHEMA_HAS_CHANGED |
INT |
NUMBER |
|
PROPERTY_ID |
LONGINT |
NUMBER |
The WLCS_CAT_PROP_BOOLEAN Database Table
Table 2-7 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_BOOLEAN table in the Commerce database. This table stores Boolean property values that are associated with configurable entities.
|
Column Name |
Cloudscape Type |
Oracle Type |
|---|---|---|
|
PROPERTY_ID |
LONGINT |
NUMBER |
|
VALUE |
INT |
NUMBER |
The WLCS_CAT_PROP_INTEGER Database Table
Table 2-8 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_INTEGER table in the Commerce database. This table stores integer property values that are associated with configurable entities.
|
Column Name |
Cloudscape Type |
Oracle Type |
|---|---|---|
|
PROPERTY_ID |
LONGINT |
NUMBER |
|
VALUE |
INT |
NUMBER |
The WLCS_CAT_PROP_FLOAT Database Table
Table 2-9 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_FLOAT table in the Commerce database. This table stores integer property values that are associated with configurable entities.
|
Column Name |
Cloudscape Type |
Oracle Type |
|---|---|---|
|
PROPERTY_ID |
LONGINT |
NUMBER |
|
VALUE |
INT |
NUMBER |
The WLCS_CAT_PROP_TEXT Database Table
Table 2-10 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_TEXT table in the Commerce database. This table stores text property values that are associated with configurable entities.
|
Column Name |
Cloudscape Type |
Oracle Type |
|---|---|---|
|
PROPERTY_ID |
LONGINT |
NUMBER |
|
VALUE |
VARCHAR(256) |
VARCHAR(256) |
The WLCS_CAT_PROP_DATETIME Database Table
Table 2-11 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_DATETIME table in the Commerce database. This table stores timestamp property values that are associated with configurable entities.
|
Column Name |
Cloudscape Type |
Oracle Type |
|---|---|---|
|
PROPERTY_ID |
LONGINT |
NUMBER |
|
VALUE |
TIMESTAMP |
DATE |
The WLCS_CAT_PROP_USER_DEFINED Database Table
Table 2-12 describes the metadata for the WebLogic Commerce Server WLCS_CAT_PROP_USER_DEFINED table in the Commerce database. This table stores user-defined (object) property values that are associated with configurable entities.
|
Column Name |
Cloudscape Type |
Oracle Type |
|---|---|---|
|
PROPERTY_ID |
LONGINT |
NUMBER |
|
VALUE |
LONG BIT VARYING |
LONG RAW |
The SQL Files and Defined Constraints
WebLogic Commerce Server provides four SQL files to create and populate the Cloudscape and Oracle versions of the Commerce database. The SQL files are in the WL_COMMERCE_HOME\db\<database-vendor>\wlcs\ directories. WL_COMMERCE_HOME is the directory in which you installed the WebLogic Commerce Server software, and the <database-vendor> directory is either cloudscape or oracle. The files are:
You can run the create-* and insert-* procedures for the desired database vendor type by invoking one of the following procedures in the WL_COMMERCE_HOME\db\ directory:
In each create-catalog-*.sql file, the database tables described earlier in this chapter are created. In addition, the SQL files define constraints. Table 2-13 shows the table name and describes the constraint(s) defined for it.
Note: The sample SQL statements in the attached document table are from the create-catalog-oracle.sql file. The syntax is different for Cloudscape. Except where noted, the index and the effect of each constraint is the same.
|
Table Name |
Constraints as Defined in create-catalog-oracle.sql |
|---|---|
|
WLCS_CATEGORY |
The WLCS_CATEGORY_PARENT_FK constraint enforces the deletion of subcategories when the current category is deleted (Cascading deletes are not supported in Cloudscape, however.) The constraint for the schema in Oracle is: ALTER TABLE WLCS_CATEGORY ADD CONSTRAINT WLCS_CATEGORY_PARENT_FK1 FOREIGN KEY (PARENT_ID) REFERENCES WLCS_CATEGORY (CATEGORY_ID) ON DELETE CASCADE; |
|
WLCS_PRODUCT |
The WLCS_PROD_ITEM_PK constraint sets the SKU as the primary key, which must be unique and cannot be null. The constraint is: CONSTRAINT WLCS_PROD_ITEM_PK PRIMARY KEY (SKU) |
|
WLCS_PRODUCT_CATEGORY |
The WLCS_PRODUCT_CATEGORY_PK constraint sets the CATEGORY_ID and SKU fields as the primary keys in the mapping table. For example: CONSTRAINT WLCS_PRODUCT_CATEGORY_PK PRIMARY KEY (CATEGORY_ID,SKU) This table is used to join categories with items. |
|
WLCS_PRODUCT_KEYWORD |
Two constraints are defined for this table. The first constraint, WLCS_PRODUCT_KEYWORD_PK, sets the KEYWORD and SKU fields as the primary keys. For example: CONSTRAINT WLCS_PRODUCT_KEYWORD_PK PRIMARY KEY (KEYWORD,SKU) The second constraint, WLCS_PRODUCT_KEYWORD_FK2, sets the SKU field as a foreign key that references the SKU in the WLCS_PRODUCT table. For example: CONSTRAINT WLCS_PRODUCT_KEYWORD_FK2 FOREIGN KEY (SKU) REFERENCES WLCS_PRODUCT(SKU) This table stores the keywords that you associate with each product item. The keywords enable rapid retrieval of item records from the database (or cache) via the Search functions on the Web site's pages or Administration pages. |
|
|
|
|
|
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|