The Product Catalog Database Schema - Updated November 05, 2001


	BEA Home \| Events \| Solutions \| Partners \| Products \| Services \| Download \| Developer Center \| WebSUPPORT
	http://www.oracle.com/technology/documentation/index.html \| Search \| PDF Files \| Contact \| Glossary

WLCS Documentation \| Product Catalog \| Previous Topic \| Next Topic \| Contents \| Index

The Product Catalog Database Schema

This topic documents the database schema for the WebLogic Commerce Server Product Catalog. This topic includes the following sections:

The Entity-Relation Diagram

Figure 2-1 shows the logical Entity-Relation diagram for the WebLogic Commerce Server core product catalog tables in the Commerce database. See the subsequent sections in this chapter for information about the data type syntax.

Figure 2-1 Entity-Relation Diagram for the Core Product Catalog Tables

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 on defining custom attributes, see the section Defining Custom Attributes for Items.

Figure 2-2 Custom Attribute Tables for Catalog

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:

Simplicity
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.
Semantic interoperability
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.
International consensus
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.
Extensibility
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
Metadata modularity on the Web
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 Catalog Schema Metadata Tables

This section covers the following database tables:

The WLCS_CATEGORY Database Table

The WLCS_PRODUCT Database Table

The WLCS_PRODUCT_CATEGORY Database Table

The WLCS_PRODUCT_KEYWORD Database Table

The WLCS_SCHEMA Database Table

The WLCS_CATEGORY Database Table

Table 8-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.

The Primary Key is CATEGORY_ID.

Table 2-1 WLCS_CATEGORY Table Metadata

Column Name	Data Type	Description and Recommendations
CATEGORY_ID	VARCHAR(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.
NAME	VARCHAR(50)	The name of the category in the product catalog.
SOURCE	VARCHAR(30)	A reference to a category from which the present category is derived.
LANG	VARCHAR(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)	A reference to a related category.
COVERAGE	VARCHAR(30)	The extent or scope of the content of the category.
RIGHTS	VARCHAR(30)	Information about rights held in and over the category.
CREATOR	VARCHAR(50)	An entity primarily responsible for making the content of the category.
PUBLISHER	VARCHAR(50)	An entity responsible for making the category available.
CONTRIBUTOR	VARCHAR(50)	An entity responsible for making contributions to the content of the category.
CREATION_DATE	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	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	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)	The language of the thumbnail image for the category. For related information, see the description of the LANG column.
SMALL_IMG_NAME	VARCHAR(50)	The name of the thumbnail image for the category.
SMALL_IMG_URL	VARCHAR(254)	The URL of the thumbnail image for the category.
SMALL_IMG_ALT_TEXT	VARCHAR(254)	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	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)	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)	The name of the full-size image for the category.
LARGE_IMG_URL	VARCHAR(254)	The URL of the full-size image for the category.
LARGE_IMG_ALT_TEXT	VARCHAR(254)	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(254)	AThe URL to the JSP used to display the category. For example: /commerce/catalog/includes/ category.jsp
SHORT_DESC	VARCHAR(50)	A short description of the content of the category.
LONG_DESC	VARCHAR(254)	A long description of the content of the category.
PARENT_ID	VARCHAR(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.

See the section The SQL Scripts Used to Create the Database for information about the constraint defined for this table.

The WLCS_PRODUCT Database Table

Table 8-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.

The Primary Key is SKU.

Table 2-2 WLCS_PRODUCT Table Metadata

Column Name	Data Type	Description and Recommendations
SKU	VARCHAR(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	VARCHAR(1)	Provide 1 if in stock, or 0 if out of stock.
VISIBLE	VARCHAR(1)	Indicates whether the item should be displayed to the user. Enter 1 if visible or 0 if not visible. 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)	The code used by the TAXWARE system to identify the specific tax category to which this item belongs.
SHIPPING_CODE	VARCHAR(10)	The code used by the shipping company for this item.
NAME	VARCHAR(100)	A name given to the product item.
SOURCE	VARCHAR(30)	A reference to another product item from which the present item is derived.
LANG	VARCHAR(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)	A reference to a related product item.
COVERAGE	VARCHAR(30)	The extent or scope of the content of the product item.
RIGHTS	VARCHAR(30)	Information about rights held in and over the item.
FORMAT	VARCHAR(30)	The physical or digital manifestation of the item.
TYPE	VARCHAR(30)	The nature or genre of the content of the item.
MSRP_CURRENCY	VARCHAR(30)	The currency type of the manufacturer's recommended price.
MSRP_AMOUNT	NUMBER(16,4)	The manufacturer's recommended price.
PRICE_CURRENCY	VARCHAR(30)	The currency type of our catalog price for this item.
PRICE_AMOUNT	NUMBER(16,4)	Our current price for this item in the catalog.
ESTIMATE_SHIP_TIME	VARCHAR(100)	Inventory: number of days/weeks before the item can be shipped.
SPECIAL_NOTES	VARCHAR(100)	Inventory related message to display with the item.
CREATOR	VARCHAR(50)	An entity primarily responsible for making the content of the product item.
PUBLISHER	VARCHAR(50)	An entity responsible for making the product item available.
CONTRIBUTOR	VARCHAR(50)	An entity responsible for making contributions to the content of the product item.
CREATION_DATE	DATE	A date associated with an event in the life cycle of the product item. 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	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	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)	The language of the thumbnail image for the item. For related information, see the description of the LANG column.
SMALL_IMG_NAME	VARCHAR(50)	The name of the thumbnail image for the item.
SMALL_IMG_URL	VARCHAR(254)	The URL of the thumbnail image for the category.
SMALL_IMG_ALT_TEXT	VARCHAR(254)	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	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)	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)	The name of the full-size image for the item.
LARGE_IMG_URL	VARCHAR(254)	The URL of the full-size image for the item.
LARGE_IMG_ALT_TEXT	VARCHAR(254)	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_URL	VARCHAR(254)	The URL to the JSP used to display the item in summary form. For example: /commerce/catalog/includes/ itemsummary.jsp
DET_DISPLAY_JSP_URL	VARCHAR(254)	The URL to the JSP used to display the item in detailed form. For example: /commerce/catalog/includes/ itemdetails.jsp
SHORT_DESC	VARCHAR(254)	A short description of the content of the product item.
LONG_DESC	VARCHAR(2000)	A long description of the content of the product item.

See the section The SQL Scripts Used to Create the Database for information about the constraint defined for this table.

The WLCS_PRODUCT_CATEGORY Database Table

Table 8-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.

The Primary Keys are SKU and CATEGORY_ID.

Table 2-3 WLCS_PRODUCT_CATEGORY Table Metadata

Column Name	Data Type	Description and Recommendations
SKU	VARCHAR(40)	A unique identifier (the "Stock Keeping Unit," or SKU) for an item. NOT NULL.
CATEGORY_ID	VARCHAR(20)	A unique identifier for a category. NOT NULL.

See the section The SQL Scripts Used to Create the Database for information about the constraint defined for this table.

The WLCS_PRODUCT_KEYWORD Database Table

Table 8-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.

The Primary Keys are KEYWORD and SKU.

Table 2-4 WLCS_PRODUCT_KEYWORD Table Metadata

Column Name	Data Type	Description and Recommendations
KEYWORD	VARCHAR(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)	A unique identifier (the "Stock Keeping Unit," or SKU) for an item. NOT NULL.

See the section The SQL Scripts Used to Create the Database for information about the two constraints defined for this table.

The WLCS_SCHEMA Database Table

Table 2-5 describes the WLCS_SCHEMA table. This table stores property set definitions.

The Primary Keys are SCHEMA_GROUP_NAME and SCOPE_NAME.

Table 2-5 WLCS_SCHEMA Table Metadata

Column Name	Data Type	Description and Recommendations
SCHEMA_GROUP_NAME	VARCHAR(100)	The type of object this schema is used for.
SCOPE_NAME	VARCHAR(100)	The application name since it is defining names for the application.
DESCRIPTION	VARCHAR(254)	A description of the schema.
SCHEMA_ID	NUMBER(15)	A system generated number used throughout the application.

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 Defining Custom Attributes for Items.

In this section, the database tables for custom attributes are listed alphabetically, as a data dictionary.

The WLCS_CAT_ENTITY_ID Database Table

Table 8-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.

The Primary Keys are JNDI_HOME_NAME and PK_STRING.

Table 2-6 WLCS_CAT_ENTITY_ID Table Metadata

Column Name	Data Type	Description and Recommendations
JNDI_HOME_NAME	VARCHAR(254)	The class name for the configurable entity. Either: com.beasys.ebusiness.catalog.ProductCategory or com.beasys.ebusiness.catalog.ProductItem
PK_STRING	VARCHAR(480)	The primary key string for the category or item.
ENTITY_ID	NUMBER(15)	NOT NULL

The WLCS_CAT_PROP_BOOLEAN Database Table

Table 8-6 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.

Table 2-7 WLCS_CAT_PROP_BOOLEAN Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier for each Boolean property.
VALUE	NUMBER(3)	The value for each Boolean property identifier.

The WLCS_CAT_PROP_DATETIME Database Table

Table 8-7 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.

Table 2-8 WLCS_CAT_PROP_DATETIME Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier for each date and time property.
VALUE	DATE	The value for each data and time property identifier.

The WLCS_CAT_PROP_FLOAT Database Table

Table 8-8 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.

Table 2-9 WLCS_CAT_PROP_FLOAT Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier for each float property.
VALUE	NUMBER	The value associated with each float property identifier.

The WLCS_CAT_PROP_ID Database Table

Table 8-9 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.

Table 2-10 WLCS_CAT_PROP_ID Table Metadata

Column Name	Data Type	Description and Recommendations
ENTITY_ID	NUMBER(15)	A system generated value and foreign key to the WLCS_ENTITY_ID column.
SCOPE_NAME	VARCHAR(100)	This column may be null. If this property is defined in a property set, then the SCOPE_NAME will match the SCHEMA_NAME for that property set in the WLCS_SCHEMA table.
PROPERTY_NAME	VARCHAR(100)	The name of the property.
PROPERTY_TYPE	NUMBER(3)	This column identifies the type of property we are dealing with (for example, boolean, integer, float, text, and so on).
PROPERTY_META_DATA_ID	NUMBER(15)	The identifier for the Property Meta Data information. Again, we use the PROPERTY_TYPE column to identify which type of Property Meta Data we are looking at (for example, boolean, integer, and so on).
SCHEMA_HAS_CHANGED	NUMBER(3)	A flag informing to identify whether anything in the WLCS_SCHEMA or WLCS_PROP_MD_xxx tables has changed. If so, then certain cleanup activities must be performed prior to using this property next time
PROPERTY_ID	NUMBER(15)	The property identifier is a unique system generated number.

The WLCS_CAT_PROP_INTEGER Database Table

Table 8-10 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.

Table 2-11 WLCS_CAT_PROP_INTEGER Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier of the integer property.
VALUE	NUMBER(20)	The value associated with the integer property.

The WLCS_CAT_PROP_TEXT Database Table

Table 8-11 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.

Table 2-12 WLCS_CAT_PROP_TEXT Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier of the text property.
VALUE	VARCHAR(254)	The value associated with the text property.

The WLCS_CAT_PROP_USER_DEFINED Database Table

Table 2-13 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.

Table 2-13 WLCS_CAT_PROP_USER_DEFINED Table Metadata

Column Name	Data Type	Description and Recommendations
PROPERTY_ID	NUMBER(15)	The identifier of the user-defined property.
VALUE	BLOB	The value associated with the user-defined property.

The WLCS_PROP_MD_* Database Tables

The WLCS_PROP_MD Database Table

Table 2-14 describes the WLCS_PROP_MD table. This table stores information about defined properties in a property set.

The Primary Keys are SCHEMA_ID and PROPERTY_NAME.

Table 2-14 WLCS_PROP_MD Table Metadata

Column Name	Data Type	Description and Recommendations
SCHEMA_ID	NUMBER(15)	A foreign key to the WLCS_SCHEMA table.
PROPERTY_NAME	VARCHAR(100)	The name of a property.
DESCRIPTION	VARCHAR(254)	A description of the property.
IS_RESTRICTED	NUMBER(3)	If set TRUE, the value of the property is constrained to a set of values. 0 equates to FALSE and 1 equates to TRUE.
IS_EXPLICIT	NUMBER(3)	If set TRUE, the property value may be coming from an external source. 0 equates to FALSE and 1 equates to TRUE.
IS_MULTIVALUED	NUMBER(3)	Some properties may have more than one value. 0 equates to FALSE and 1 equates to TRUE.
PROPERTY_TYPE	NUMBER(3)	Defines the property type (boolean, text and so on).
PROPERTY_META_DATA_ID	NUMBER(15)	The primary key is a unique, system-generated value.

The WLCS_PROP_MD_BOOLEAN Database Table

Table 2-15 describes the WLCS_PROP_MD_BOOLEAN table. This table stores property set definitions for the boolean property type.