bea.com | products | dev2dev | support | askBEA

Download Docs | Site Map | Glossary
Search

Administration Guide

Database Schemas

This section describes the database schemas for WebLogic Portal. This information is provided to help you restructure your database to better customize or extend the technologies provided in WebLogic Portal.

This section includes information on the following subjects:

- Campaign Database Schemas

- Event Database Schemas

- Catalog Database Schemas

- Order and Discount Database Schemas

- Personalization Database Schemas

- Portal Database Schemas

Campaign Database Schemas

This section describes the database schema for the Campaign services.

- The Entity-Relation Diagram for Campaign Manager Database Tables

- List of Tables Composing the BEA Campaign Manager

- The Campaign Manager Data Dictionary

The Entity-Relation Diagram for Campaign Manager Database Tables

Figure A-1 shows the Entity-Relation diagram for the E-Business Control Center for the Campaign services database. See the subsequent sections in this chapter for information about the data type syntax.

Figure A-1 Entity-Relation Diagram for Campaign Manager Database Tables

List of Tables Composing the BEA Campaign Manager

The BEA Campaign Manager is composed of the following table:

Campaign and Scenarios

- The SCENARIO_END_STATE Database Table

The Campaign Manager Data Dictionary

At this time, there is only one database table pertaining to the Campaign Manager.

The SCENARIO_END_STATE Database Table

Table A-1 describes the metadata for the E-Business Control Center SCENARIO_END_STATE table. This table identifies when a user is no longer eligible to participate in a particular scenario.

The Primary Key is comprised of SCENARIO_XML_REF, USER_NAME, CONTAINER_REF, CONTAINER_TYPE and APPLICATION_NAME.

Table A-1 SCENARIO_END_STATE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
SCENARIO_XML_REF | VARCHAR(20) | NOT NULL | PK—The identifier for the XML-based scenario definition file.
USER_NAME | VARCHAR(200) | NOT NULL | PK—the user ID. (FK to WLCS_USER.IDENTIFIER)
CONTAINER_REF | VARCHAR(254) | NOT NULL | PK—the campaign unique identifier. (FK to CAMPAIGN.CAMPAIGN_UID)
CONTAINER_TYPE | VARCHAR(50) | NOT NULL | PK—At this time this column will always hold the string 'Campaign'.
APPLICATION_NAME | VARCHAR(100) | NOT NULL | PK—The deployed J2EE application name. This should match the name in the WebLogic Server console.)

Event Database Schemas

To record how online visitors are interacting with your Web site, you can record event information to a database. These kinds of events are called Behavior Tracking events. E-analytics and e-marketing systems can then analyze these events offline to evaluate visitor behavior and transactional data. You can use the knowledge gained from analysis to create and optimize personalization rules, set up product offers, and develop interactive marketing campaigns. This section describes the requirements and database schema needed to log event data for analytical use.

This section includes information on the following subjects:

- Data Storage

- Constraints and Indexes

Data Storage

This section provides an overview of relational databases and the database schemas and tables that are required for recording Behavior Tracking events.

Relational Databases

Relational databases have both logical and physical structures. Logically you may define one or more databases. Each database may contain one or more tables and indexes, and each table may have multiple columns and rows. The logical structure of databases is quite similar between vendors. However, the physical structure of a database is very vendor-specific. Essentially, the physical structure defines areas on disk drives where the data is stored. Each database environment uses its own terminology and implementation for storing data at the operating system level. For example, Oracle uses the term tablespace and the Microsoft SQL Server uses the term filegroup.

Recommendation When a database structure is defined by a database administrator, attention must be paid to the location of specific tables. Some tables are static in that they do not change much; some tables are dynamic in that many rows are being added and deleted; and some tables are read frequently and some rarely. Depending on their behavior, tables should be placed on different physical locations. Some of the most highly-used tables in WebLogic Portal are used for Behavior Tracking. The activity of a single visitor moving around your site may generate multiple table entries. Therefore, it is recommended that you place these tables on the fastest drives in the computer. Experienced database administrators are aware of many techniques for monitoring and configuring a database installation for optimal performance. If you do not have a database administrator working with your installation and you have a lot of activity on your site, you should bring in a well-qualified database administer for regular maintenance of your system.

Database Directory Paths

The default database directory paths are:

- %PORTAL_HOME%\db\<db vendor>\<db version>\... (Windows)

- $PORTAL_HOME/db/<db vendor>/<db version>/... (UNIX)

where PORTAL_HOME is the directory in which you installed WebLogic Portal.

For example, if you are using Oracle 8.1.7 on UNIX, the location would be $PORTAL_HOME/db/oracle/817/....

Scripts BEA provides scripts to help set up the database schema needed for recording Behavior Tracking events, as well as the schema needed for recording data associated with WebLogic Portal. This data includes information from orders, catalogs, products, portals, and portlets.

For Oracle databases, the tablespaces created for WebLogic Portal data are the WEBLOGIC_DATA and WEBLOGIC_INDEX.

Note: WEBLOGIC_DATA and WEBLOGIC_INDEX are tablespace names created by BEA scripts. If you use a particular naming convention, you can rename them.

Behavior tracking uses a tablespace called WEBLOGIC_EVENT_DATA. This tablespace stores all Behavior Tracking tables, indexes, and constraints. Because of the potential for high volumes of data, this tablespace should be monitored closely.

Behavior Tracking Database Schema

Three tables are provided for the Behavior Tracking data. The EVENT table stores all event data. The EVENT_ACTION table logs actions used by third-party vendors against the recorded event data, and the EVENT_TYPE table references event types and categories in the EVENT table. Figure A-2 shows a logical entity-relation diagram for the Behavior Tracking Database.

Figure A-2 Entity-Relation Diagram for the Behavior Tracking Database

The EVENT Database Table

Table A-2 describes the metadata for the EVENT table. This table stores all Behavior Tracking event data.

The Primary Key is EVENT_ID.

Table A-2 The EVENT Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
APPLICATION | VARCHAR (30) | NOT NULL | The application that created the event.
EVENT_ID | NUMBER | NOT NULL | A unique, system-generated number used as the record ID. This field is the table's primary key.
EVENT_TYPE | VARCHAR(30) | NOT NULL | A string identifier that shows which event was fired.
EVENT_DATE | DATE | NOT NULL | The date and time of the event.
WLS_SESSION_ID | VARCHAR(254) | NOT NULL | A unique, WebLogic Server-generated number assigned to the session.
XML_DEFINITION | CLOB | NULL | An XML document that contains the specific event information for each event type. It is stored as a CLOB (Character Large Object). See Table A-3.
USER_ID | VARCHAR(50) | NULL | The user ID associated with the session and event. If the user has not logged in this column will be null.

As shown in Table A-2, the EVENT table has six columns; each column corresponds to a specific event element. Five of the EVENT table's columns contain data common to every event type. The XML_DEFINITION column contains all information from these five columns plus event data that is unique to each event type. An XML document is created specifically for each event type. The data elements corresponding to each event type are captured in the XML_DEFINITION column of the EVENT table. These elements are listed in Table A-3.

Table A-3 XML_DEFINITION Data Elements

Event | Data Element
--- | ---
AddToCartEvent | application event-date event-type session-id user-id sku quantity unit-list-price currency application-name
BuyEvent | application event-date event-type session-id user-id sku quantity unit-price currency application-name order-line-id
CampaignUserActivityEvent | application event-date event-type session-id user-id campaign-id scenario-id
ClickCampaignEvent | application event-date event-type session-id user-id document-type document-id campaign-id scenario-id application-name placeholder-id
ClickContentEvent | application event-date event-type session-id user-id document-type document-id
ClickProductEvent | application event-date event-type session-id user-id document-type document-id sku category-id application-name
DisplayCampaignEvent | application event-date event-type session-id user-id document-type document-id campaign-id scenario-id application-name placeholder-id
DisplayContentEvent | application event-date event-type session-id user-id document-type document-id
DisplayProductEvent | application event-date event-type session-id user-id document-type document-id sku category-id application-name
PurchaseCartEvent | application event-date event-type session-id user-id total-price order-id currency application-name
RemoveFromCartEvent | application event-date event-type session-id user-id sku quantity unit-price currency application-name
RuleEvent | application event-date event-type session-id user-id ruleset-name rule-name
SessionBeginEvent | application event-date event-type session-id user-id
SessionEndEvent | application event-date event-type session-id user-id
SessionLoginEvent | application event-date event-type session-id user-id
UserRegistrationEvent | application event-date event-type session-id user-id

The EVENT_ACTION Database Table

Table A-4 describes the metadata for the EVENT_ACTION table. This table logs actions used by third-party vendors against the recorded event data. It is a fairly static.

The Primary Key is comprised of EVENT_ACTION and ACTION_DATE.

Table A-4 EVENT_ACTION Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
EVENT_ACTION | VARCHAR(30) | NOT NULL | The event action taken such as BEGIN EXPORT or END EXPORT. This field is one of the table's primary keys.
ACTION_DATE | DATE | NOT NULL | The date and time of the event. This field is one of the table's primary keys.
EVENT_ID | NUMBER | NULL | The ID of the event that corresponds with the event action taken.

The EVENT_TYPE Database Table

Table A-5 describes the metadata for the EVENT_TYPE table. This table references event types and categories in the EVENT table. This table is static.

The Primary Key is EVENT_TYPE.

Table A-5 EVENT_TYPE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
EVENT_TYPE | VARCHAR(30) | NOT NULL | A unique, system-generated number used as the record ID. This field is the table's primary key.
EVENT_GROUP | VARCHAR(10) | NOT NULL | The event category group associated with the event type.
DESCRIPTION | VARCHAR(50) | NULL | A description of the EVENT_TYPE.

Note: To record custom events, you must create an entry in this table. If a custom event does not have a record in this table, you cannot persist it to the EVENT table.

Constraints and Indexes

There is a single foreign key constraint between the EVENT_TYPE columns in the EVENT and EVENT_TYPE tables. As previously mentioned, if a custom event does not have a record in the EVENT_TYPE table, it cannot be persisted to the EVENT table.

Other than Primary Keys on each of the tables, there are only two indexes on the EVENT table. One index is on the EVENT.EVENT_DATE column and the other index is comprised of the EVENT.EVENT_TYPE and EVENT.EVENT_DATE columns.

Catalog Database Schemas

This section documents the database schema for the Commerce services Product Catalog.

This section includes information on the following subjects:

- The Entity-Relation Diagram for the Core Product Catalog Tables

- The Campaign Manager Data Dictionary

- Defined Constraints for Product Catalog Tables

- Defined Constraints for Product Catalog Tables

The metadata for items in Commerce services Product Catalog are based on the Dublin Core Metadata Open Standard. This standard offers a number of advantages for a Web-based catalog. For more information about the Dublin Core Metadata Open Standard, please see http://purl.org/dc.

The Entity-Relation Diagram for the Core Product Catalog Tables

Figure A-3 shows the logical Entity-Relation diagram for the Commerce services core Product Catalog tables in the Commerce database.

Figure A-3 Entity-Relation Diagram for the Core Product Catalog Tables

Product Catalog Tables

The following tables compose the product catalog database.

- The CATALOG_ENTITY Database Table

- The CATALOG_PROPERTY_KEY Database Table

- The CATALOG_PROPERTY_VALUE Database Table

- The WLCS_CATEGORY Database Table

- The WLCS_PRODUCT Database Table

- The WLCS_PRODUCT_CATEGORY Database Table

- The WLCS_PRODUCT_KEYWORD Database Table

The CATALOG_ENTITY Database Table

Contents: Unique identification numbers for configurable entities.

Primary key: ENTITY_ID.

Table A-6 CATALOG_ENTITY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
ENTITY_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as a record identifier.
ENTITY_NAME | VARCHAR(200) | NOT NULL | The name of the entity.
ENTITY_TYPE | VARCHAR(100) | NOT NULL | The type of entity (e.g., User, Group, etc.)
CREATION_DATE | DATE | NOT NULL | The time and date the record was created.
MODIFIED_DATE | DATE | NOT NULL | The time and date the record was last modified.

The CATALOG_PROPERTY_KEY Database Table

Contents: Unique identification numbers for scoped property names that are associated with configurable entities.

Primary key: PROPERTY_KEY_ID.

Table A-7 CATALOG_PROPERTY_KEY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PROPERTY_KEY_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as a record identifier.
PROPERTY_NAME | VARCHAR(100) | NOT NULL | The name of the property (formerly PROPERTY_NAME from the WLCS_PROP_ID table).
CREATION_DATE | DATE | NOT NULL | The time and date the record was created.
MODIFIED_DATE | DATE | NOT NULL | The time and date the record was last modified.
PROPERTY_SET_NAME | VARCHAR(100) | NULL | The name of the property set (formerly the SCOPE_NAME from WLCS_PROP_ID).
PROPERTY_SET_TYPE | VARCHAR(100) | NULL | The type of property set (for example, USER)

The CATALOG_PROPERTY_VALUE Database Table

Contents: Boolean, timestamp, float, integer, text, and user-defined (object) property values that are associated with configurable entities.

Primary key: PROPERTY_VALUE_ID.

Table A-8 CATALOG_PROPERTY_VALUE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PROPERTY_VALUE_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as a record identifier.
PROPERTY_KEY_ID | NUMBER(15) | NOT NULL | A system-generated value and foreign key to the PROPERTY_KEY column.
ENTITY_ID | NUMBER(15) | NOT NULL | A system-generated value and foreign key to the ENTITY column.
PROPERTY_TYPE | NUMBER(1) | NOT NULL | Valid entries are: 0=Boolean, 1=Integer, 2=Float, 3=Text, 4=Date and Time, 5=User-Defined (BLOB)
CREATION_DATE | DATE | NOT NULL | The time and date the record was created.
MODIFIED_DATE | DATE | NOT NULL | The time and date the record was last modified.
BOOLEAN_VALUE | NUMBER(1) | NULL | The value for each boolean property identifier.
DATETIME_VALUE | DATE | NULL | The value for each date and time property identifier.
DOUBLE_VALUE | NUMBER | NULL | The value associated with each float property identifier.
LONG_VALUE | NUMBER(20) | NULL | The value associated with the integer property.
TEXT_VALUE | VARCHAR(254) | NULL | The value associated with the text property.
BLOB_VALUE | BLOB | NULL | The value associated with the user-defined property.

The WLCS_CATEGORY Database Table

Contents: 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.

Primary key: CATEGORY_ID.

Table A-9 WLCS_CATEGORY Table Metadata

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

The WLCS_PRODUCT Database Table

Contents: Item records in the Commerce database.

Primary key: SKU.

Table A-10 WLCS_PRODUCT Table Metadata

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

The WLCS_PRODUCT_CATEGORY Database Table

Contents: Shows which product items are associated with product categories.

Primary key: SKU and CATEGORY_ID.

Table A-11 WLCS_PRODUCT_CATEGORY Table Metadata

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

The WLCS_PRODUCT_KEYWORD Database Table

Contents: 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.

Primary key: KEYWORD and SKU.

Table A-12 WLCS_PRODUCT_KEYWORD Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
KEYWORD | VARCHAR(30) | NOT NULL | Contains a keyword that you associate with the product item assigned to the unique SKU. Recommendation—for a given item, select a value from a controlled vocabulary or formal classification scheme implemented in your company.
SKU | VARCHAR(40) | NOT NULL | A unique identifier (the "Stock Keeping Unit," or SKU) for an item.

Defined Constraints for Product Catalog Tables

Various constraints are defined and used in the Product Catalog database schema. These constraints can be found in the following scripts:

wlcs_create_fkeys.sql—contains the Foreign Keys

wlcs_create_tables.sql—contains the Check Constraints

Table A-13 Constraints Defined on Product Catalog Database Tables

Table Name | Constraints
--- | ---
CATALOG_PROPERTY_VALUE | Column—ENTITY_ID Constraint—FK1_CAT_PROP_V Constraint Type—FOREIGN KEY Ensures that each CATALOG_PROPERTY_VALUE references an existing CATALOG_ENTITY via the ENTITY_ID column. Column—PROPERTY_KEY_ID Constraint—FK2_CAT_PROP_V Constraint Type—FOREIGN KEY Ensures that each CATALOG_PROPERTY_VALUE references an existing CATALOG_PROPERTY_KEY via the PROPERTY_KEY_ID column. Column—BOOLEAN_VALUE Constraint—CC1_CAT_PROP_V Constraint Type—CHECK Ensures the value of the BOOLEAN_VALUE column is either 0 (false) or 1 (true).
WLCS_CATEGORY | Column—CATEGORY_ID Constraint—FK1_CATEGORY Constraint Type—FOREIGN KEY Ensures that each PARENT_ID references an existing WLCS_CATEGORY via the CATEGORY_ID column.
WLCS_PRODUCT_CATEGORY | Column— CATEGORY_ID Constraint— FK1_PRODUCT_CAT Constraint Type—FOREIGN KEY Ensures that each CATEGORY_ID references an existing WLCS_CATEGORY via the CATEGORY_ID column. Column—SKU Constraint—FK2_PRODUCT_CAT Constraint Type—FOREIGN KEY Ensures that each SKU references an existing WLCS_PRODUCT via the SKU column.
WLCS_PRODUCT_KEYWORD | Column—SKU Constraint—FK1_PRODUCT_KEY Constraint Type—FOREIGN KEY Ensures that each SKU references an existing WLCS_PRODUCT via the SKU column.

Order and Discount Database Schemas

This section describes the database schema for Order services.

This section includes information on the following subjects:

- The Entity-Relation Diagram for Campaign Manager Database Tables

- The Order Processing Data Dictionary Tables

- Defined Constraints in the Order Database Schema

The Entity-Relation Diagram for the Order and Discount Tables

Figure A-3 shows the logical Entity-Relation diagram for the WebLogic Portal order and discount tables in the WebLogic Portal database. See the subsequent sections in this chapter for information about the data type syntax.

Figure A-4 Entity-Relation Diagram for the Order and Discount Tables

The Order Processing Data Dictionary Tables

Note: Even though the following documentation references "foreign keys" to various tables, these constraints do not currently exist in this release of Commerce services. However, they will be in place in future versions of Commerce services and we want you to be aware of these relationships now.

The Commerce services order management system has the following tables:

- The DISCOUNT Database Table

- The DISCOUNT_ASSOCIATION Database Table

- The ORDER_ADJUSTMENT Database Table

- The ORDER_LINE_ADJUSTMENT Database Table

- The WLCS_CREDIT_CARD Database Table

- The WLCS_CUSTOMER Database Table

- The WLCS_ORDER Database Table

- The WLCS_ORDER_LINE Database Table

- The WLCS_SAVED_ITEM_LIST Database Table

- The WLCS_SECURITY Database Table

- The WLCS_SHIPPING_ADDRESS Database Table

- The WLCS_SHIPPING_METHOD Database Table

- The WLCS_TRANSACTION Database Table

- The WLCS_TRANSACTION_ENTRY Database Table

The DISCOUNT Database Table

Contents: One or more discount records for every DISCOUNT_SET record.

Primary key: DISCOUNT_ID.

Table A-14 DISCOUNT

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
DISCOUNT_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number to be used as the record ID.
APPLICATION_NAME | VARCHAR(100) | NOT NULL | FK—foreign key to the DISCOUNT_SET table.
DISCOUNT_TYPE | VARCHAR(10) | NOT NULL | The type of discount offered. It is used for an order or for an order line item.
DISCOUNT_NAME | VARCHAR(254) | NOT NULL | The name of the discount.
IS_GLOBAL | NUMBER(1) | NOT NULL | A flag showing whether or not this discount can be used globally.
PRIORITY | NUMBER(3) | NOT NULL | The level of priority this discount has over other discounts.
ALLOWED_USERS | NUMBER(10) | NOT NULL | The number of times the discount may be used.
MODIFIER | VARCHAR(254) | NOT NULL | Describes the actual discount to be applied. This is XML.
DISCOUNT_RULE | CLOB | NOT NULL | The method used to select items for discount. This is XML.
START_DATE | DATE | NOT NULL | The starting date and time of the discount
END_DATE | DATE | NOT NULL | The ending date and time of the discount.
IS_ACTIVE | NUMBER(1) | NOT NULL | A flag that determines whether the discount is active or not. Active=1, Not active=0
DESCRIPTION | VARCHAR(254) | NULL | The discount description.
DISPLAY_DESCRIPTION | VARCHAR(254) | NULL | The discount description used for display purposes only.

The DISCOUNT_ASSOCIATION Database Table

Function: Associates each customer with a discount and maintains information regarding the times the customer has used each discount.

Primary key: DISCOUNT_ASSOCIATION_ID.

Table A-15 DISCOUNT_ASSOCIATION

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
DISCOUNT_ASSOCIATION_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number to be used as the record ID.
CUSTOMER_ID | VARCHAR(20) | NOT NULL | FK—foreign key to the DISCOUNT_SET table.
DISCOUNT_ID | NUMBER(15) | NOT NULL | FK—foreign key to the DISCOUNT_SET table.
USE_COUNT | NUMBER(10) | NOT NULL | The number of times the discount has been used.
DISPLAY_DESCRIPTION | VARCHAR(254) | NULL | The discount description used for display purposes only.

The ORDER_ADJUSTMENT Database Table

Contents: Information about a discount taken at the order level (for example, $20.00 off any order between 1/1/02 and 1/31/02.)

Primary key: ORDER_ADJUSTMENT_ID.

Table A-16 ORDER_ADJUSTMENT

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
ORDER_ADJUSTMENT_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number to be used as the record ID.
ORDER_ID | VARCHAR(20) | NOT NULL | FK—foreign key to the DISCOUNT_SET table.
ADJUSTMENT_TYPE | VARCHAR(20) | NULL | The type of adjustment being made to the order line item (e.g., order line discount, shipping discount, etc.)
COMPUTATION | VARCHAR(254) | NOT NULL | The number of times the discount has been used.
ADJUSTMENT_AMOUNT | NUMBER(16,4) | NOT NULL | The discount description used for display purposes only.
DISCOUNT_ID | NUMBER(15) | NULL | FK—foreign key to the DISCOUNT table.
DISPLAY_DESCRIPTION | VARCHAR(254) | NULL | The description used for display purposes only. Depending on the nature of the discount, the DISPLAY_DESCRIPTION is generated from either the Discount service or Campaign service.
CREATION_DATE | DATE | NOT NULL | The date and time the order adjustment was created.
MODIFIED_DATE | DATE | NULL | The date and time the order adjustment record was last modified.

The ORDER_LINE_ADJUSTMENT Database Table

Contents: Information about a discount taken at the order line item level (for example, 10% off SKU "Power Drill").

Primary key: ORDER_LINE_ADJUSTMENT_ID.

Table A-17 ORDER_LINE_ADJUSTMENT Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
ORDER_LINE_ADJUSTMENT_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number to be used as the record ID.
ORDER_LINE_ID | NUMBER(15) | NOT NULL | A unique identifier for each line in a customer's shopping cart. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ORDERLINE table can be NULL.
ADJUSTMENT_TYPE | VARCHAR(20) | NULL | The type of adjustment being made to the order line item (e.g., order line discount, shipping discount, etc.)
ADJUSTMENT_AMOUNT | NUMBER(16,4) | NOT NULL | The dollar amount of the adjustment.
ADJUSTMENT_QUANTITY | NUMBER(16,4) | NOT NULL | The quantity amount for the adjustment.
ADJUSTED_UNIT_PRICE | NUMBER(16,4) | NOT NULL | The adjusted unit price of the specific line item.
COMPUTATION | VARCHAR(254) | NOT NULL | The computation for determining ADJUSTED_UNIT_PRICE.
CREATION_DATE | DATE | NOT NULL | The date and time the adjustment record was created.
MODIFIED_DATE | DATE | NULL | The date and time the adjustment record was last modified.
DISCOUNT_ID | NUMBER(15) | NULL | FK—a foreign key to the discount used from the DISCOUNT table.
DISPLAY_DESCRIPTION | VARCHAR(254) | NULL | The adjustment description used for display purposes.

The WLCS_CREDIT_CARD Database Table

Contents: Information related to a customer's credit card(s) in the order processing database.

Primary key: CREDIT_CARD_ID.

Table A-18 WLCS_CREDIT_CARD Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
CREDIT_CARD_ID | NUMBER(15) | NOT NULL | A unique identifier for the credit card. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_CREDIT_CARD table can be NULL.
CC_NUMBER | VARCHAR(200) | NULL | The customer's credit card number. This is encrypted if is.encryption. enable is set to true in the weblogiccommerce. properties file.
CC_TYPE | VARCHAR(20) | NULL | The customer's credit card type, such as VISA or MasterCard.
CC_EXP_DATE | DATE | NULL | The expiration date on the customer's credit card.
CC_NAME | VARCHAR(50) | NULL | The credit card holder's name.
CC_DISPLAY_NUMBER | VARCHAR(20) | NULL | The version of the credit card number that is displayed (all Xs except last 4-digits).
CC_COMPANY | VARCHAR(50) | NULL | The name of the credit card company.
BILLING_GEOCODE | VARCHAR(2) | NULL | The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
BILLING_STREET1 | VARCHAR(30) | NULL | The first line in the customer's billing address.
BILLING_STREET2 | VARCHAR(30) | NULL | The second line in the customer's billing address.
BILLING_CITY | VARCHAR(30) | NULL | The city in the customer's billing address.
BILLING_STATE | VARCHAR(40) | NULL | The state in the customer's billing address.
BILLING_COUNTRY | VARCHAR(40) | NULL | The country in the customer's billing address.
BILLING_POBOX | VARCHAR(30) | NULL | The post office box in the customer's billing address.
BILLING_COUNTY | VARCHAR(50) | NULL | The county in the customer's billing address.
BILLING_POSTAL_CODE | VARCHAR(10) | NULL | The postal (ZIP) code in the customer's billing address.
BILLING_POSTAL_CODE_TYPE | VARCHAR(10) | NULL | Format or type of postal code, generally determined by country (such as ZIP code in the United States).
CUSTOMER_ID | VARCHAR(20) | NULL | A unique identifier for the customer.
MAP_KEY | VARCHAR(60) | NULL | Key that maps multiple credit cards with a single customer.

The WLCS_CUSTOMER Database Table

Contents: Information about the customer in the order processing database.

Primary key: CUSTOMER_ID.

Table A-19 WLCS_CUSTOMER Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
CUSTOMER_ID | VARCHAR(20) | NOT NULL | A unique identifier for the customer. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_CUSTOMER table can be NULL.
CUSTOMER_TYPE | VARCHAR(20) | NULL | A label for the customer (such as preferred, standard, or business).
FIRST_NAME | VARCHAR(30) | NULL | The customer's first name.
LAST_NAME | VARCHAR(30) | NULL | The customer's last name.
MIDDLE_NAME | VARCHAR(30) | NULL | The customer's middle name.
TITLE | VARCHAR(10) | NULL | The customer's preferred title, such as Mr., Mrs., or Ms.
SUFFIX | VARCHAR(10) | NULL | The customer's preferred suffix, such as Jr.or Sr.
EMAIL | VARCHAR(80) | NULL | The customer's email address.
HOME_PHONE | VARCHAR(15) | NULL | The customer's home phone number.
BUSINESS_PHONE | VARCHAR(20) | NULL | The customer's business phone number.
FAX | VARCHAR(15) | NULL | The customer's fax number.
MAILING_GEOCODE | VARCHAR(2) | NULL | The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
MAILING_STREET1 | VARCHAR(30) | NULL | The first line in the customer's street address.
MAILING_STREET2 | VARCHAR(30) | NULL | The second line in the customer's street address.
MAILING_CITY | VARCHAR(30) | NULL | The city in the customer's address.
MAILING_STATE | VARCHAR(40) | NULL | The state in the customer's address.
MAILING_COUNTRY | VARCHAR(40) | NULL | The country in the customer's address.
MAILING_POBOX | VARCHAR(30) | NULL | The post office box in the customer's address.
MAILING_COUNTY | VARCHAR(50) | NULL | The county in the customer's address.
MAILING_POSTAL_CODE | VARCHAR(10) | NULL | The postal (ZIP) code in the customer's address.
MAILING_POSTAL_CODE_TYPE | VARCHAR(10) | NULL | Format or type of postal code, generally determined by country (such as ZIP code in the United States).

The WLCS_ORDER Database Table

Contents: Information about a customer's specific order in the order-processing database.

Note: The Commerce services product does not populate the SHIPPING_AMOUNT, SHIPPING_CURRENCY, PRICE_AMOUNT, or PRICE_CURRENCY columns.

Primary key: ORDER_ID.

Table A-20 WLCS_ORDER Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
ORDER_ID | VARCHAR(20) | NOT NULL | A unique identifier for the order. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ORDER table can be NULL.
CUSTOMER_ID | VARCHAR(20) | NULL | A unique identifier for the customer.
TRANSACTION_ID | VARCHAR(25) | NULL | A unique identifier for the transaction.
STATUS | VARCHAR(20) | NULL | The status of the order.
ORDER_DATE | DATE | NULL | The date the order was placed.
SHIPPING_METHOD | VARCHAR(40) | NULL | The method by which the order is to be shipped.
SHIPPING_AMOUNT | NUMBER(16,4) | NULL | The shipping amount for the order.
SHIPPING_CURRENCY | VARCHAR(10) | NULL | The currency associated with the shipping amount.
PRICE_AMOUNT | NUMBER(16,4) | NULL | The price of the order.
PRICE_CURRENCY | VARCHAR(10) | NULL | The currency associated with the price.
SHIPPING_GEOGODE | VARCHAR(2) | NULL | The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
SHIPPING_STREET1 | VARCHAR(30) | NULL | The first line in the customer's shipping address.
SHIPPING_STREET2 | VARCHAR(30) | NULL | The second line in the customer's shipping address.
SHIPPING_CITY | VARCHAR(30) | NULL | The city in the customer's shipping address.
SHIPPING_STATE | VARCHAR(40) | NULL | The state in the customer's shipping address.
SHIPPING_COUNTRY | VARCHAR(40) | NULL | The country in the customer's shipping address.
SHIPPING_POBOX | VARCHAR(30) | NULL | The post office box in the customer's shipping address.
SHIPPING_COUNTY | VARCHAR(50) | NULL | The county in the customer's shipping address.
SHIPPING_POSTAL_CODE | VARCHAR(10) | NULL | The postal (ZIP) code in the customer's shipping address.
SHIPPING_POSTAL_CODE_TYPE | VARCHAR(10) | NULL | Format or type of postal code, generally determined by country, such as ZIP code in the United States.
SPECIAL_INSTRUCTIONS | VARCHAR(254) | NULL | Any special shipping instructions associated with the order.
SPLITTING_PREFERENCE | VARCHAR(254) | NULL | The splitting preferences for the customer's order.
ORDER_SUBTOTAL | NUMBER(16,4) | NULL | The sum of all the TOTAL_LINE_AMOUNT columns in the WLCS_ORDER_LINE table for that specific order.

The WLCS_ORDER_LINE Database Table

Contents: Information about each line of a customer's shopping cart in the order processing database.

Primary key: ORDER_LINE_ID.

Table A-21 WLCS_ORDER_LINE Table Metadata

Column Name

Data Type

Null Value

Description and Recommendations

ORDER_LINE_ID

NUMBER(15)

NOT NULL

A unique identifier for each line in a customer's shopping cart. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ORDERLINE table can be NULL.

QUANTITY

NUMBER(16,4)

NULL

The quantity of the item in the shopping cart.

PRODUCT_ID

VARCHAR(40)

NULL

An identification number for the item in the shopping cart.

TAX_AMOUNT

NUMBER(16,4)

NULL

The tax amount for the order.

TAX_CURRENCY

VARCHAR(10)

NULL

The currency associated with the tax amount.

SHIPPING_AMOUNT

NUMBER(16,4)

NULL

The shipping amount for the order.

SHIPPING_CURRENCY

VARCHAR(10)

NULL

The currency associated with the shipping amount.

UNIT_PRICE_AMOUNT

NUMBER(16,4)

NULL

The unit price amount for the item.

UNIT_PRICE_CURRENCY

VARCHAR(10)

NULL

The currency associated with the unit price.

MSRP_AMOUNT

NUMBER(16,4)

NULL

The MSRP amount for the item.

MSRP_CURRENCY

VARCHAR(10)

NULL

The currency associated with the MSRP amount.

DESCRIPTION

VARCHAR(254)

NULL

The name of the item that is part of the order.

ORDER_ID

VARCHAR(20)

NULL

A unique identifier for the order.

TOTAL_LINE_AMOUNT

NUMBER(16,4)

NULL

The total discounted price for the line item. UNIT_PRICE_AMOUNT (less any discount) times the QUANTITY.

The WLCS_SAVED_ITEM_LIST Database Table

Contents: Information about the customer's saved shopping cart items in the order processing database.

Primary key: None.

Table A-22 WLCS_SAVED_ITEM_LIST Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
CUSTOMER_ID | VARCHAR(20) | NULL | A unique identifier for the customer.
SKU | VARCHAR(40) | NULL | A unique identifier (the Stock Keeping Unit or SKU) for a product item.

The WLCS_SECURITY Database Table

Function: Persists public and private keys for encryption and decryption purposes in the order processing database. This table is meant for internal use by the Commerce services product.

Primary key: None.

Table A-23 WLCS_SECURITY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
ID | NUMBER(5) | NULL | A unique identifier for the key pair. This field is the table's primary key and cannot be NULL.
PUBLIC_KEY | VARCHAR(2000) | NULL | The public key to be used for encryption/decryption of credit cards.
PRIVATE_KEY | VARCHAR(2000) | NULL | The private key to be used for encryption/decryption of credit cards.

The WLCS_SHIPPING_ADDRESS Database Table

Contents: Information related to a customer's shipping address(es) in the order processing database.

Primary key: SHIPPING_ADDRESS_ID.

Table A-24 WLCS_SHIPPING_ADDRESS Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
SHIPPING_ADDRESS_ID | NUMBER(15) | NOT NULL | A unique identifier for the shipping address. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_SHIPPING_ADDRESS table can be NULL.
MAP_KEY | VARCHAR(60) | NULL | Key that maps multiple shipping addresses with a single customer.
SHIPPING_GEOCODE | VARCHAR(2) | NULL | The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
SHIPPING_STREET1 | VARCHAR(30) | NULL | The first line in the customer's shipping address.
SHIPPING_STREET2 | VARCHAR(30) | NULL | The second line in the customer's shipping address.
SHIPPING_CITY | VARCHAR(30) | NULL | The city in the customer's shipping address.
SHIPPING_STATE | VARCHAR(40) | NULL | The state in the customer's shipping address.
SHIPPING_COUNTRY | VARCHAR(40) | NULL | The country in the customer's shipping address.
SHIPPING_POBOX | VARCHAR(30) | NULL | The post office box in the customer's shipping address.
SHIPPING_COUNTY | VARCHAR(50) | NULL | The county in the customer's shipping address.
SHIPPING_POSTAL_CODE | VARCHAR(10) | NULL | The postal (zip) code in the customer's shipping address.
SHIPPING_POSTAL_CODE_TYPE | VARCHAR(10) | NULL | Format or type of postal code, generally determined by country, such as ZIP code in the United States.
CUSTOMER_ID | VARCHAR(20) | NULL | A unique identifier for the customer.

The WLCS_SHIPPING_METHOD Database Table

Contents: Information about the shipping method in the order processing database.

Primary key: PK_IDENTIFIER.

Table A-25 WLCS_SHIPPING_METHOD Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PK_IDENTIFIER | VARCHAR(20) | NOT NULL | A unique identifier for the shipping method. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_SHIPPING_ METHOD table can be NULL.
CARRIER | VARCHAR(40) | NULL | The carrier being used to ship the order, such as UPS or FedEx.
METHOD | VARCHAR(40) | NULL | The method by which the order is to be shipped, such as Air, 2nd Day Air, or Parcel Post.
AVERAGE_SHIPPING_TIME | NUMBER | NULL | The average number of days it will take the order to arrive.
PRICE_VALUE | NUMBER(16,4) | NULL | The amount it will cost to ship the order.
PRICE_CURRENCY | VARCHAR(10) | NULL | The currency associated with the PRICE_VALUE column, such as dollars, pounds, or lira.
WEIGHT_LIMIT | NUMBER(16,4) | NULL | The weight limit for the shipment.
RESTRICTIONS | VARCHAR(254) | NULL | Any restrictions associated with the shipment.
DESCRIPTION | VARCHAR(254) | NULL | A description of the shipping method, such as FedEx Overnight or Standard.
PO_BOX_ALLOWED | NUMBER | NULL | Specifies whether or not the shipment can be left at a post office box.
SIGNATURE_REQUIRED | NUMBER | NULL | Specifies whether or not a signature is required upon receipt of the shipment.
SATURDAY_DELIVERY | NUMBER | NULL | Specifies whether or not the shipment can be delivered on Saturday.
INTERNATIONAL_DELIVERY | NUMBER | NULL | Specifies whether or not international delivery is an option.
SIZE_LIMIT | NUMBER(16,4) | NULL | The size limit for the shipment.
PACKAGING_TYPE | VARCHAR(50) | NULL | The packaging type for the shipment.

The WLCS_TRANSACTION Database Table

Contents: Data for every payment transaction in the order processing database.

Primary key: TRANSACTION_ID.

Table A-26 WLCS_TRANSACTION Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
TRANSACTION_ID | VARCHAR(25) | NOT NULL | A unique identifier for the transaction. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ TRANSACTION table can be NULL.
BATCH_ID | VARCHAR(15) | NULL | A unique identifier of a batch submitted for settlement, as returned by the Payment Web service. This field need not be populated for other external payment services.
TRAN_DATE | DATE | NULL | The date of the transaction (that is, date on which the transaction was first started).
TRAN_STATUS | VARCHAR(20) | NULL | The current status of the transaction (Settled, Authorized, MarkedForSettle, PendingSettle, Retry, or Settled).
TRAN_AMOUNT | NUMBER(16,4) | NULL | The most recent amount applied to the transaction. MarkForSettle amounts can be different from the authorization amount.
TRAN_CURRENCY | VARCHAR(30) | NULL | The currency of the transaction.
CC_NUMBER | VARCHAR(200) | NULL | The customer's credit card number. This is encrypted if is.encryption.enable is set to true in the weblogiccommerce.properties file.
CC_TYPE | VARCHAR(20) | NULL | The customer's credit card type, such as VISA or MasterCard.
CC_EXP_DATE | DATE | NULL | The expiration date on the customer's credit card.
CC_NAME | VARCHAR(50) | NULL | The credit card holder's name.
CC_DISPLAY_NUMBER | VARCHAR(20) | NULL | The version of the credit card number that is displayed (displays all Xs except last 4-digits).
CC_COMPANY | VARCHAR(50) | NULL | The name of the credit card company.
GEOCODE | VARCHAR(2) | NULL | The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
STREET1 | VARCHAR(30) | NULL | The first line in the customer's street address.
STREET2 | VARCHAR(30) | NULL | The second line in the customer's street address.
CITY | VARCHAR(30) | NULL | The city in the customer's address.
STATE | VARCHAR(40) | NULL | The state in the customer's address.
COUNTRY | VARCHAR(40) | NULL | The country in the customer's address.
POBOX | VARCHAR(30) | NULL | The post office box in the customer's address.
DESCRIPTION | VARCHAR(30) | NULL | Any additional data. Can be NULL.
COUNTY | VARCHAR(50) | NULL | The county in the customer's address.
POSTAL_CODE | VARCHAR(10) | NULL | The postal (ZIP) code in the customer's address.
POSTAL_CODE_TYPE | VARCHAR(10) | NULL | Format or type of postal code, generally determined by country, such as Zip code in the United States.

The WLCS_TRANSACTION_ENTRY Database Table

Function: Logs the different states a payment transaction has passed through in the order processing database.

Primary key: TRANSACTION_ENTRY_ID.

Table A-27 WLCS_TRANSACTION_ENTRY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
TRANSACTION_ENTRY_ID | NUMBER(25) | NOT NULL | A unique identifier for the transaction entry. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_TRANSACTION_ENTRY table can be NULL.
TRAN_ENTRY_SEQUENCE | VARCHAR(30) | NULL | Represents the running count per transaction.
TRAN_ENTRY_DATE | DATE | NULL | The date of the log entry.
TRAN_ENTRY_STATUS | VARCHAR(20) | NULL | The status of the transaction when this entry was made.
TRAN_ENTRY_AMOUNT | NUMBER(16,4) | NULL | The amount of the transaction when the log entry was made.
TRAN_ENTRY_CURRENCY | VARCHAR(30) | NULL | The currency of the transaction.
TRANSACTION_ID | VARCHAR(25) | NULL | A unique identifier for the transaction.

Defined Constraints in the Order Database Schema

Various constraints are defined and used in the Order database schema. These constraints can be found in the following scripts:

wlcs_create_fkeys.sql - Contains the Foreign Keys

wlcs_create_tables.sql - Contains the Check Constraints

Table A-28 Constraints Defined on Order Database Tables

Table Name | Constraints
--- | ---
DISCOUNT_ASSOCIATION | Column—CUSTOMER_ID Constraint—FK1_DISC_ASSOC Constraint Type—FOREIGN KEY Ensures that each CUSTOMER_ID references an existing WLCS_CUSTOMER via the CUSTOMER_ID column. Column—DISCOUNT_ID Constraint—FK2_DISC_ASSOC Constraint Type—FOREIGN KEY Ensures that each DISCOUNT_ID references an existing DISCOUNT via the DISCOUNT_ID column.
WLCS_CREDIT_CARD | Column—CUSTOMER_ID Constraint—FK1_CREDIT_CARD Constraint Type—FOREIGN KEY Ensures that each CUSTOMER_ID references an existing WLCS_CUSTOMER via the CUSTOMER_ID column
WLCS_ORDER_LINE | Column—ORDER_ID Constraint—FK1_ORDER_LINE Constraint Type—FOREIGN KEY Ensures that each ORDER_ID references an existing WLCS_ORDER via the ORDER_ID column.
ORDER_ADJUSTMENT | Column—DISCOUNT_ID Constraint—FK1_ORDER_ADJ Constraint Type—FOREIGN KEY Ensures that each DISCOUNT_ID references an existing DISCOUNT via the DISCOUNT_ID column.
ORDER_LINE_ADJUSTMENT | Column—DISCOUNT_ID Constraint—FK1_ORDER_L_ADJ Constraint Type—FOREIGN KEY Ensures that each DISCOUNT_ID references an existing DISCOUNT via the DISCOUNT_ID column
WLCS_SHIPPING_ADDRESS | Column—CUSTOMER_ID Constraint— FK1_SHIP_ADDR Constraint Type—FOREIGN KEY Ensures that each CUSTOMER_ID references an existing WLCS_CUSTOMER via the CUSTOMER_ID column.
WLCS_TRANSACTION_ENTRY | Column—TRANSACTION_ID Constraint—FK1_TRANS_ENTRY Constraint Type—FOREIGN KEY Ensures that each TRANSACTION_ID references an existing WLCS_TRANSACTION via the TRANSACTION_ID column.
DISCOUNT | Column—IS_GLOBAL Constraint— CC1_DISCOUNT Constraint Type—CHECK Ensures the value of the IS_GLOBAL column is either 0 (false) or 1 (true). Column—IS_ACTIVE Constraint— CC2_DISCOUNT Constraint Type—CHECK Ensures the value of the IS_ACTIVE column is either 0 (false) or 1 (true).

Personalization Database Schemas

This section documents the database schema for WebLogic Portal personalization features.

This section includes information on the following subjects:

- The Entity-Relation Diagram for Campaign Manager Database Tables

- List of WebLogic Portal Personalization Tables

- The Campaign Manager Data Dictionary

- Defined Constraints

The Entity-Relation Diagram for WebLogic Portal Personalization

Figure 12-1 shows the logical Entity-Relation diagram for the WebLogic Portal personalization database.

Figure A-5 Entity-Relation Diagram for WebLogic Portal Personalization

Figure A-6 Entity-Relation Diagram for WebLogic Portal Personalization Continued

List of WebLogic Portal Personalization Tables

WebLogic Portal's personalization features use the following tables. In this list, the tables are sorted by functionality:

Ads and Placeholders tables

The AD_BUCKET Database Table

The AD_COUNT Database Table

The PLACEHOLDER_PREVIEW Database Table

Data Synchronization tables

The DATA_SYNC_APPLICATION Database Table

The DATA_SYNC_ITEM Database Table

The DATA_SYNC_SCHEMA_URI Database Table

The DATA_SYNC_VERSION Database Table

Documentation Management tables

The DOCUMENT Database Table

The DOCUMENT_METADATA Database Table

Mail tables

The MAIL_ADDRESS Database Table

The MAIL_BATCH Database Table

The MAIL_BATCH_ENTRY Database Table

The MAIL_HEADER Database Table

The MAIL_MESSAGE Database Table

User Management tables

The GROUP_HIERARCHY Database Table

The GROUP_SECURITY Database Table

The USER_GROUP_CACHE Database Table

The USER_GROUP_HIERARCHY Database Table

The USER_PROFILE Database Table

The USER_SECURITY Database Table

Common tables used by WebLogic Portal

The ENTITLEMENT_RULESET Database Table Database Table

The ENTITY Database Table

The PROPERTY_KEY Database Table

The PROPERTY_VALUE Database Table

The SAMPLE_UUP_INFO Database Tablee

The SEQUENCER Database Table

The WEBLOGIC_IS_ALIVE Database Table

The Personalization Server Data Dictionary

In this section, WebLogic Portal personalization schema tables are arranged alphabetically as a data dictionary.

Note: Even though the following documentation references "foreign keys" to various tables, these constraints do not currently exist in this release of WebLogic Portal. However, they will be (available in future releases) in place in future versions of WebLogic Portal and we want you to be aware of these relationships now.

The AD_BUCKET Database Table

Table 12-1 describes the AD_BUCKET table. This table maintains content queries for ads.

The Primary Key is AD_BUCKET_ID.

Table A-29 AD_BUCKET Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
AD_BUCKET_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
USER_NAME | VARCHAR (200) | NOT NULL | The user's name associated with the ad.
PLACEHOLDER_XML_REF | VARCHAR(254) | NOT NULL | The location identifier of the XML-based placeholder definition file.
APPLICATION_NAME | VARCHAR(100) | NOT NULL | The name of the application for which the ad has been scoped.
CONTEXT_REF | VARCHAR(254) | NULL | The scenario unique identifier.
CONTAINER_REF | VARCHAR(254) | NULL | The campaign unique identifier.
CONTAINER_TYPE | VARCHAR(50) | NULL | Identifies the service associated with the CONTAINER_REF.
WEIGHT | NUMBER(15) | NULL | A weighted scheme used in prioritizing one placeholder over another.
VIEW_COUNT | NUMBER(15) | NULL | Disabled. Reserved for future use.
EXPIRATION_DATE | DATE | NULL | The date and time the ad expires or becomes invalid.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.
AD_QUERY | CLOB | NULL | The actual content query.

The AD_COUNT Database Table

Table 12-2 describes the AD_COUNT table. This table tracks the number of times the ads are displayed and clicked though.

The Primary Key is comprised of AD_ID, CONTAINER_REF, and APPLICATION_NAME.

Table A-30 AD_COUNT Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
AD_ID | VARCHAR(254) | NOT NULL | A unique, system-generated number used as the record identifier.
CONTAINER_REF | VARCHAR(254) | NOT NULL | The campaign unique identifier.
APPLICATION_NAME | VARCHAR(100) | NOT NULL | The name of the application for which the ad clicks or views were scoped
DISPLAY_COUNT | NUMBER(15) | NOT NULL | The number of times the ad has been displayed.
CLICK_THROUGH_COUNT | NUMBER(15) | NOT NULL | The number of times the ad has been clicked on.

The DATA_SYNC_APPLICATION Database Table

Table 12-3 describes the DATA_SYNC_APPLICATION table. This table holds the various applications available for the data synchronization process..

The Primary Key is APPLICATION_ID.

Table A-31 DATA_SYNC_APPLICATION Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
APPLICATION_ID | NUMBER(15) | NOT NULL | PK - A unique, system-generated number used as the record identifier.
APPLICATION_NAME | VARCHAR(100) | NOT NULL | The deployed J2EE application name. (This should match the name in the WebLogic Server console.)
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.

The DATA_SYNC_ITEM Database Table

Table 12-4 describes the DATA_SYNC_ITEM table. This table stores all the data items to be synchronized.

The Primary Key is DATA_SYNC_ITEM_ID.

Table A-32 DATA_SYNC_ITEM Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
DATA_SYNC_ITEM_ID | NUMBER(15) | NOT NULL | PK - A unique, system-generated number used as the record identifier.
APPLICATION_ID | NUMBER(15) | NOT NULL | FK - to DATA_SYNC_APPLICATON.APPLICATION_ID
SCHEMA_URI_ID | NUMBER(15) | NOT NULL | FK - to DATA_SYNC_SCHEMA_URI.SCHEMA_URI_ID
VERSION_MAJOR | NUMBER(15) | NOT NULL | FK - to DATA_SYNC_VERSION.VERSION_MAJOR
VERSION_MINOR | NUMBER(15) | NOT NULL | FK - to DATA_SYNC_VERSION.VERSION_MINOR
ITEM_CHECKSUM | NUMBER(15) | NOT NULL | A generated number representing the contents of the XML_DEFINITION column.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.
XML_MODIFIED_DATE | DATE | NOT NULL | The date and time the XML file was last modified.
XML_CREATION_DATE | DATE | NOT NULL | The date and time the XML file was created.
XML_DEFINITION | CLOB | NOT NULL | The XML representation of the data item to be synchronized.
ITEM_URI | VARCHAR(254) | NOT NULL | The path on the file system of the data item to be synchronized.
ITEM_AUTHOR | VARCHAR(200) | NULL | Metadata info—the o/s login.
ITEM_NAME | VARCHAR(100) | NULL | Metadata info—the full path to the item.
ITEM_DESCRIPTION | VARCHAR(254) | NULL | Metadata info—a general description of the item to be synchronized.

The DATA_SYNC_SCHEMA_URI Database Table

Table 12-5 describes the DATA_SYNC_SCHEMA_URI table. This table holds information pertaining to each of the governing schemas used by various documents.

The Primary Key is SCHEMA_URI_ID.

Table A-33 DATA_SYNC_SCHEMA_URI Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
SCHEMA_URI_ID | NUMBER(15) | NOT NULL | PK - A unique, system-generated number used as the record identifier.
SCHEMA_URI | VARCHAR(254) | NOT NULL | The governing schema of the document.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.

The DATA_SYNC_VERSION Database Table

Table 12-6 describes the DATA_SYNC_VERSION table. This table is not being used currently. It is reserved for future use and is expected to accommodate data synchronization versioning. As a result, this table only holds one record.

The Primary Key is comprised of both VERSION_MAJOR and VERSION_MINOR.

Table A-34 DATA_SYNC_VERSION Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
VERSION_MAJOR | NUMBER(15) | NOT NULL | The current record has a value of zero.
VERSION_MINOR | NUMBER(15) | NOT NULL | The current record has a value of zero.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time the record was last modified.
BUILD_NUMBER | NUMBER(15) | NULL | The build number associated with the version.
VERSION_DESCRIPTION | VARCHAR(30) | NULL | A description of the particular sync version.

The DOCUMENT Database Table

Table 12-7 describes the DOCUMENT table. This table is used to store information pertinent to each document used within WebLogic Portal.

The Primary Key is ID.

Table A-35 DOCUMENT Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
ID | VARCHAR(254) | NOT NULL | The identifier of the document. This specifies the relative path (case sensitive using forward slashes) to the actual file.
DOCUMENT_SIZE | NUMBER(15) | NOT NULL | The size of the document in bytes.
VERSION | NUMBER(15) | NULL | The version of the document.
AUTHOR | VARCHAR(50) | NULL | The author's name of this document.
CREATION_DATE | DATE | NULL | The date this document was created in the system.
LOCKED_BY | VARCHAR(50) | NULL | This column identifies who has this document locked for edits or updates.
MODIFIED_DATE | DATE | NULL | The date and time this record was last modified.
MODIFIED_BY | VARCHAR(50) | NULL | This column stores the name of the individual who last modified the document record.
DESCRIPTION | VARCHAR(2000) | NULL | A description of the document.
COMMENTS | VARCHAR(2000) | NULL | An area to store miscellaneous notes about the document.
MIME_TYPE | VARCHAR(100) | NOT NULL | This column identifies which MIME type (or file type) is associated with this document. This is supposed to be MIME 1.0.

The DOCUMENT_METADATA Database Table

Table 12-8 describes the DOCUMENT_METADATA table. This table is used to store user-defined properties associated with each document.

The Primary Key is comprised of both ID and NAME.

Table A-36 DOCUMENT_METADATA Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
ID | VARCHAR(254) | NOT NULL | The document identifier. This is a foreign key to the ID column of the DOCUMENT table.
NAME | VARCHAR(240) | NOT NULL | The metadata name for the document.
STATE | VARCHAR(50) | NULL | The current state of this metadata property. This is used by Interwoven and can be set to null.
VALUE | VARCHAR(2000) | NULL | The value to be associated with the metadata name (NAME).

The ENTITLEMENT_RULESET Database Table

Table 12-9 describes the ENTITLEMENT_RULESET table. This table stores the access decision rules used by the Entitlements Engine.

The Primary Key is comprised of both APPLICATION_NAME and RULESET_URI.

Table A-37 ENTITLEMENT_RULESET Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
APPLICATION_NAME | VARCHAR(100) | NOT NULL | PK - A unique application name within a J2EE server.
RULESET_URI | VARCHAR(254) | NOT NULL | The URI used to identify an entitlement access decision rule.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.
RULESET_DOCUMENT | CLOB | NULL | The XML document describing an access decision rule.

The ENTITY Database Table

Table 12-10 describes the ENTITY table. Any ConfigurableEntity within the system will have an entry in this table.

The Primary Key is ENTITY_ID.

Table A-38 ENTITY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
ENTITY_ID | NUMBER(15) | NOT NULL | PK - A unique, sequence-generated number used as the record identifier.
ENTITY_NAME | VARCHAR(200) | NOT NULL | The name of the ConfigurableEntity.
ENTITY_TYPE | VARCHAR(100) | NOT NULL | Defines what type of ConfigurableEntity this is.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.

The GROUP_HIERARCHY Database Table

Table 12-11 describes the PARENT_CHILD_GROUP table. This table stores relationship information between groups.

The Primary Key is comprised of both PARENT_GROUP_ID and CHILD_GROUP_ID.

Table A-39 GROUP_HIERARCHY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PARENT_GROUP_ID | NUMBER(15) | NOT NULL | The parent group identifier. This column is a foreign key to the ENTITY_ID column in the ENTITY table.
CHILD_GROUP_ID | NUMBER(15) | NOT NULL | The child group identifier. This column is a foreign key to the ENTITY_ID column in the ENTITY table.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.

The GROUP_SECURITY Database Table

Table 12-12 describes the GROUP_SECURITY table. This table holds all groups that a user could be given membership to for security authentication of the rdbms realm.

Table A-40 GROUP_SECURITY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
GROUP_ID | NUMBER(15) | NOT NULL | PK - a unique, system-generated number used as the record identifier.
GROUP_NAME | VARCHAR(200) | NOT NULL | The name of the group.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.

The MAIL_ADDRESS Database Table

Table 12-13 describes the metadata for the E-Business Control Center MAIL_ADDRESS table. This table stores all of the address info for e-mail purposes.

The Primary Key is MAIL_ADDRESS_ID.

Table A-41 MAIL_ADDRESS Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
MAIL_ADDRESS_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number to be used as the record ID.
MESSAGE_ID | NUMBER(15) | NOT NULL | FK—foreign key to the MAIL_MESSAGE table.
ADDRESS | VARCHAR(254) | NOT NULL | Stores the various e-mail addresses on the distribution list.
SEND_TYPE | VARCHAR(4) | NOT NULL | Determines how the ADDRESS should be included on the distribution. Possible values are TO, CC, or BCC.

The MAIL_BATCH Database Table

Table 12-14 describes the metadata for the E-Business Control Center MAIL_BATCH table. This table establishes a batch for each mailing.

The Primary Key is BATCH_ID.

Table A-42 MAIL_BATCH Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
BATCH_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number to be used as the record ID.
BATCH_NAME | VARCHAR(254) | NOT NULL | The name of the mail message batch.

The MAIL_BATCH_ENTRY Database Table

Table 12-15 describes the metadata for the E-Business Control Center MAIL_BATCH_ENTRY table. This table is used to correlate the mail batch with the specific mail message.

The Primary Keys are BATCH_ID and MESSAGE_ID.

Table A-43 MAIL_BATCH_ENTRY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
BATCH_ID | NUMBER(15) | NOT NULL | PK and FK—a unique, system-generated number to be used as the record ID.
MESSAGE_ID | NUMBER(15) | NOT NULL | PK and FK—foreign key to the MAIL_MESSAGE table.

The MAIL_HEADER Database Table

Table 12-16 describes the metadata for the E-Business Control Center MAIL_HEADER table. This table contains all of the header information specific to the e-mail message.

The Primary Key is HEADER_ID.

Table A-44 MAIL_HEADER Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
HEADER_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number to be used as the record ID.
MESSAGE_ID | NUMBER(15) | NOT NULL | FK—foreign key to the MAIL_MESSAGE table.
HEADER_NAME | VARCHAR(50) | NULL | The name of the mail message header.
HEADER_VALUE | VARCHAR(254) | NULL | The value of the mail message header.

The MAIL_MESSAGE Database Table

Table 12-17 describes the metadata for the E-Business Control Center MAIL_MESSAGE table. This table contains the specifics of the mail message (e.g., the subject line, text, etc.).

The Primary Key is MESSAGE_ID.

Table A-45 MAIL_MESSAGE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
MESSAGE_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number to be used as the record ID.
FROM_ADDRESS | VARCHAR(254) | NULL | Identifies who is sending the message.
SUBJECT | VARCHAR(128) | NULL | Stores the mail message subject.
MESSAGE_TEXT | CLOB | NULL | Holds the content of the mail message.

The PLACEHOLDER_PREVIEW Database Table

Table 12-18 describes the PLACEHOLDER_PREVIEW table. This table is used as a mechanism to hold the placeholder for previewing purposes only.

The Primary Key is PREVIEW_ID.

Table A-46 PLACEHOLDER_PREVIEW Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PREVIEW_ID | NUMBER | NOT NULL | PK—a unique, system generated number used as the record identifier.
XML_DEFINITION | CLOB | NULL | The representation of the expression to be previewed.

The PROPERTY_KEY Database Table

Table 12-19 describes the PROPERTY_KEY table. Any property assigned to a ConfigurableEntity has a unique PROPERTY_ID. This identifier and associated information is stored here.

The Primary Key is PROPERTY_KEY_ID.

Table A-47 PROPERTY_KEY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PROPERTY_KEY_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
PROPERTY_NAME | VARCHAR(100) | NOT NULL | The name of the property.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.
PROPERTY_SET_NAME | VARCHAR(100) | NULL | The name of the property set.
PROPERTY_SET_TYPE | VARCHAR(100) | NULL | The type the property set.

The PROPERTY_VALUE Database Table

Table 12-20 describes the PROPERTY_VALUE table. This table stores property values for boolean, datetime, float, integer, text, and user-defined properties.

The Primary Key is PROPERTY_VALUE_ID.

Table A-48 PROPERTY_VALUE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PROPERTY_VALUE_ID | NUMBER(15) | NOT NULL | PK - a unique, system-generated number used as the record identifier.
PROPERTY_KEY_ID | NUMBER(15) | NOT NULL | FK - to PROPERTY_KEY.PROPERTY_KEY_ID
ENTITY_ID | NUMBER(15) | NOT NULL | FK - to ENTITY.ENTITY_ID
PROPERTY_TYPE | NUMBER(1) | NOT NULL | Valid entries are: 0=Boolean, 1=Integer, 2=Float, 3=Text, 4=Date and Time, 5=User-Defined (BLOB)
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.
BOOLEAN_VALUE | NUMBER(1) | NULL | The value for each boolean property identifier.
DATETIME_VALUE | DATE | NULL | The value for each date and time property identifier.
DOUBLE_VALUE | NUMBER | NULL | The value associated with each float property identifier.
LONG_VALUE | NUMBER(20) | NULL | The value associated with the integer property.
TEXT_VALUE | VARCHAR(254) | NULL | The value associated with the text property.
BLOB_VALUE | BLOB | NULL | The value associated with the user-defined property.

The SAMPLE_UUP_INFO Database Table

Table 12-21 describes the SAMPLE_UUP_INFO table. This is an example of how to use the Unified Profile Types.

The Primary Key is USER_NAME.

Table A-49 SAMPLE_UUP_INFO Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
USER_NAME | VARCHAR(100) | NOT NULL | A username.
USER_INFO | CLOB | NOT NULL | User data stored in XML representation.

The SEQUENCER Database Table

Table 12-22 describes the SEQUENCER table. The SEQUENCER table is used to maintain all of the sequence identifiers (for example, property_meta_data_id_sequence, and so on) used in the application.

The Primary Key is SEQUENCE_NAME.

Table A-50 SEQUENCER Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
SEQUENCE_NAME | VARCHAR(50) | NOT NULL | PK - A unique name used to identify the sequence.
CURRENT_VALUE | NUMBER(15) | NOT NULL | The current value of the sequence.
IS_LOCKED | NUMBER(1) | NOT NULL | This flag identifies whether or not the particular SEQUENCE_ID has been locked for update. This column is being used as a generic locking mechanism that can be used for multiple database environments.

The USER_GROUP_CACHE Database Table

Table 12-23 describes the USER_GROUP_CACHE table. In the event of a deep group hierarchy, this table will flatten the group hierarchy and enables quick group membership searches.

Note: The startup process GroupCache is disabled by default. This table will only be used if enabled.

The Primary Key is comprised of both USER_NAME and GROUP_NAME.

Table A-51 USER_GROUP_CACHE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
USER_NAME | VARCHAR(200) | NOT NULL | A user's name.
GROUP_NAME | VARCHAR(200) | NOT NULL | A group name.

The USER_GROUP_HIERARCHY Database Table

Table 12-24 describes the USER_GROUP_HIERARCHY table. This table allows you to store associated users and groups.

The Primary Key is comprised of both GROUP_ID and USER_ID.

Table A-52 USER_GROUP_HIERARCHY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
GROUP_ID | NUMBER(15) | NOT NULL | FK - to USER_SECURITY.USER_ID
USER_ID | NUMBER(15) | NOT NULL | FK - to GROUP_SECURITY.GROUP_ID
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.

The USER_PROFILE Database Table

Table 12-25 describes the USER_PROFILE table. This table associates users with profiles (such as the WLCS_CUSTOMER user profile). User profiles use property sets to organize the properties that they contain.

The Primary Key is USER_NAME.

Table A-53 USER_PROFILE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
USER_NAME | VARCHAR(200) | NOT NULL | PK - The name of the user.
PROFILE_TYPE | VARCHAR(100) | NOT NULL | A type of profile associated with the user (such as WLCS_Customer).
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.

The USER_SECURITY Database Table

Table 12-26 describes the USER_SECURITY table. This table holds all the user records for security authentication of the rdbms realm.

The Primary Key is USER_ID.

Table A-54 USER_SECURITY Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
USER_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
USER_NAME | VARCHAR(200) | NOT NULL | The user's name.
PASSWORD | VARCHAR(50) | NULL | The user's password.
CREATION_DATE | DATE | NOT NULL | The date and time this record was created.
MODIFIED_DATE | DATE | NOT NULL | The date and time this record was last modified.

The WEBLOGIC_IS_ALIVE Database Table

Table 12-27 describes the WEBLOGIC_IS_ALIVE table. This table is used by the JDBC connection pools to insure the connection to the database is still alive.

The Primary Key is NAME.

Table A-55 WEBLOGIC_IS_ALIVE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
NAME | VARCHAR(100) | NOT NULL | Used by the JDBC connection pools to insure the connection to the database is still alive.

Defined Constraints

Various constraints are defined and used in the WebLogic Portal personalization services database schema. These constraints can be found in the following scripts:

p13n_create_fkeys.sql—contains the Foreign Keys

p13n_create_tables.sql—contains the Check Constraints

Table A-56 Constraints Defined on WebLogic Portal Personalization Services Database Tables

Table Name | Constraints
--- | ---
DATA_SYNC_ITEM | Column—APPLICATION_ID Constraint—FK1_SYNC_ITEM Constraint Type—FOREIGN KEY Ensures that each DATA_SYNC_ITEM references an existing DATA_SYNC_APPLICATION via the APPLICATION_ID column. Column—SCHEMA_URI_ID Constraint—FK2_SYNC_ITEM Constraint Type—FOREIGN KEY Ensures that each DATA_SYNC_ITEM references an existing DATA_SYNC_SCHEMA_URI via the SCHEMA_URI_ID column. Columns—VERSION_MAJOR and VERSION_MINOR Constraint—FK3_SYNC_ITEM Constraint Type—FOREIGN KEY Ensures that each DATA_SYNC_ITEM references an existing DATA_SYNC_VERSION via the VERSION_MAJOR, VERSION_MINOR columns.
DOCUMENT_METADATA | Column—ID Constraint— FK1_DOCUMENT_MD Constraint Type—FOREIGN KEY Ensures that each DOCUMENT_METADATA references an existing DOCUMENT via the ID column.
GROUP_HIERARCHY | Column—PARENT_GROUP_ID Constraint—FK1_GROUP_HRCHY Constraint Type—FOREIGN KEY Ensures that each PARENT_GROUP_HIERARCHY references an existing GROUP_SECURITY via the GROUP_ID column. Column—CHILD_GROUP_ID Constraint—FK2_GROUP_HRCHY Constraint Type—FOREIGN KEY Ensures that each CHILD_GROUP_HIERARCHY references an existing GROUP_SECURITY via the GROUP_ID column.
MAIL_ADDRESS | Column—MESSAGE_ID Constraint—FK1_MAIL_ADDRESS Constraint Type—FOREIGN KEY Ensures that each MAIL_ADDRESS references an existing MAIL_MESSAGE via the MESSAGE_ID column.
MAIL_BATCH_ENTRY | Column—BATCH_ID Constraint—FK1_MB_ENTRY Constraint Type—FOREIGN KEY Ensures that each MAIL_BATCH_ENTRY references an existing MAIL_BATCH via the BATCH_ID column. Column—MESSAGE_ID Constraint—FK2_MB_ENTRY Constraint Type—FOREIGN KEY Ensures that each MAIL_BATCH_ENTRY references an existing MAIL_MESSAGE via the MESSAGE_ID column.
MAIL_HEADER | Column—FK1_MAIL_HEADER Constraint—FK1_MAIL_HEADER Constraint Type—FOREIGN KEY Ensures that each MAIL_HEADER references an existing MAIL_MESSAGE via the FK1_MAIL_HEADER column.
USER_GROUP_HIERARCHY | Column—USER_ID Constraint—FK1_USER_G_HRCHY Constraint Type—FOREIGN KEY Ensures that each USER_GROUP_HIERARCHY references an existing USER_SECURITY via the USER_ID column. Column—GROUP_ID Constraint—FK2_USER_G_HRCHY Constraint Type—FOREIGN KEY Ensures that each USER_GROUP_HIERARCHY references an existing GROUP_SECURITY via the GROUP_ID column.
PROPERTY_VALUE | Column—ENTITY_ID Constraint—FK1_PROP_VALUE Constraint Type—FOREIGN KEY Ensures that each PROPERTY_VALUE references an existing ENTITY via the ENTITY_ID column. Column—PROPERTY_KEY_ID Constraint—FK2_PROP_VALUE Constraint Type—FOREIGN KEY Ensures that each PROPERTY_VALUE references an existing PROPERTY_KEY via the PROPERTY_KEY_ID column. Column—BOOLEAN_VALUE Constraint—CC1_PROP_VALUE Constraint Type—CHECK Ensures the value of the BOOLEAN_VALUE column is either 0 (false) or 1 (true).
SEQUENCER | Column—IS_LOCKED Constraint—CC1_SEQUENCER Constraint Type—CHECK Ensures the value of the IS_LOCKED column is either 0 (false) or 1 (true).

Portal Database Schemas

This section documents the database schema for the WebLogic Portal package.

This section includes information on the following subjects:

- The Entity-Relation Diagram for the Core Product Catalog Tables

- List of Tables Comprising the Portal Management Package

- The Portal Management Data Dictionary

- Defined Constraints

The Entity-Relation Diagram for the Portal and Portlet Tables

Figure 1-1 shows the logical Entity-Relation diagram for the WebLogic Commerce Server Portal and Portlet tables in the Commerce database. See the subsequent sections in this chapter for information about the data type syntax.

Figure 1-1 Entity-Relation Diagram for the Portal and Portlet Tables

List of Tables Comprising the Portal Management Package

Portal Management tables:

The LAYOUT Database Table

The PORTAL Database Table

The PORTAL_P13N Database Table

The PORTAL_P13N_LAYOUT Database Table

The PORTAL_P13N_SKIN_POOL Database Table

The PORTAL_PAGE Database Table

The PORTAL_PAGE_P13N Database Table

The PORTAL_PAGE_P13N_LAYOUT_POOL Database Table

The PORTLET Database Table

The PORTLET_P13N Database Table

The PORTLET_PLACEHOLDER Database Table

The RESOURCE_GROUP_ADMIN Database Table

The SKIN Database Table

The Portal Management Data Dictionary

In this section, the Portal, Portlet, and Sample Portal schema tables are arranged alphabetically as a data dictionary.

The LAYOUT Database Table

Table 1-1 describes the LAYOUT table. This table stores all of the defined layouts.

The Primary Key is LAYOUT_ID.

Table 1-1 LAYOUT Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
LAYOUT_ID | NUMBER(15) | NOT NULL | PK— a unique, system-generated number used as the record identifier.
PORTAL_ID | NUMBER(15) | NOT NULL | The record identifier for the portal associated with the layout. FK to the PORTAL table.
LAYOUT_XML_REF | VARCHAR(254) | NOT NULL | The identifier for this layout as specified in the portal definition XML file.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.

The PORTAL Database Table

Table 1-2 describes the PORTAL table. This table stores all of the defined portals.

The Primary Key is PORTAL_ID.

Table 1-2 PORTAL Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTAL_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
PORTAL_XML_REF | VARCHAR(254) | NOT NULL | The identifier for this portal as specified in the portal definition XML file.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.
DEFAULT_PORTAL_P13N_ID | NUMBER(15) | NULL | The record identifier for the personalized portal associated with the particular portal.

The PORTAL_P13N Database Table

Table 1-3 describes the PORTAL_P13N table. This table stores all of the personalized portal records.

The Primary Key is PORTAL_P13N_ID.

Table 1-3 PORTAL_P13N Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTAL_P13N_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
PORTAL_ID | NUMBER(15) | NOT NULL | The record identifier for the portal associated with the personalized portal. FK to the PORTAL table.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.
NAVBAR_ORIENTATION | NUMBER(1) | NOT NULL | This flag determines the orientation of the navigation bar. 0 equates to horizontal and 1 equates to vertical.
HOME_PORTAL_PAGE_ID | NUMBER(15) | NOT NULL | The record identifier for the home portal page associated with the personalized portal. FK to the PORTAL_PAGE table.
SKIN_ID | NUMBER(15) | NOT NULL | The record identifier for the skin associated with the personalized portal. FK to the SKIN table.
GROUP_NAME | VARCHAR(200) | NOT NULL | The group name associated with the personalized portal.
USER_NAME | VARCHAR(200) | NOT NULL | The user name associated with the personalized portal.
DISPLAY_NAME | VARCHAR(254) | NOT NULL | The personalized portal display name.

The PORTAL_P13N_LAYOUT Database Table

Table 1-4 describes the PORTAL_P13N_LAYOUT table. This table stores all available layouts for each of the personalized portals.

The Primary Key is comprised of both LAYOUT_ID and PORTAL_P13N_ID.

Table 1-4 PORTAL_P13N_LAYOUT Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
LAYOUT_ID | NUMBER(15) | NOT NULL | PK—combined with PORTAL_P13N_ID to form a unique record identifier. FK to the LAYOUT table.
PORTAL_P13N_ID | NUMBER(15) | NOT NULL | PK—combined with PORTAL_P13N_ID to form a unique record identifier. FK to the PORTAL_P13N table.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.
DISPLAY_NAME | VARCHAR(254) | NULL | The display name for the layout associated with the personalized portal.

The PORTAL_P13N_SKIN_POOL Database Table

Table 1-5 describes the PORTAL_P13N_SKIN_POOL table. This table stores all available skins for a personalized portal.

The Primary Key is comprised of both PORTAL_P13N_ID and SKIN_ID.

Table 1-5 PORTAL_P13N_SKIN_POOL Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTAL_P13N_ID | NUMBER(15) | NOT NULL | PK—combined with SKIN_ID to form a unique record identifier. FK to the PORTAL_P13N table.
SKIN_ID | NUMBER(15) | NOT NULL | PK—combined with PORTAL_P13N_ID to form a unique record identifier. FK to the SKIN table.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.
DISPLAY_NAME | VARCHAR(254) | NULL | The display name for the skin associated with the personalized portal.

The PORTAL_PAGE Database Table

Table 1-6 describes the PORTAL_PAGE table. This table stores all of the defined pages for each portal.

The Primary Key is PORTAL_PAGE_ID.

Table 1-6 PORTAL_PAGE Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTAL_PAGE_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
PORTAL_ID | NUMBER(15) | NOT NULL | The record identifier for the portal associated with the portal page. FK to the PORTAL table.
PORTAL_PAGE_XML_REF | VARCHAR(254) | NOT NULL | The identifier for this portal page as specified in the portal definition XML file.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.

The PORTAL_PAGE_P13N Database Table

Table 1-7 describes the PORTAL_PAGE_P13N table. This table stores information for each personalized portal page.

The Primary Key is PORTAL_PAGE_P13N_ID.

Table 1-7 PORTAL_PAGE_P13N Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTAL_PAGE_P13N_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
PORTAL_P13N_ID | NUMBER(15) | NOT NULL | The record identifier for the personalized portal associated with this personalized portal page. FK to the PORTAL_P13N table.
PORTAL_PAGE_ID | NUMBER(15) | NOT NULL | The record identifier for the portal page associated with this personalized portal page. FK to the PORTAL_PAGE table.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.
INDEX_NUMBER | NUMBER(4) | NOT NULL | The index number associated with this piece of the personalized portal page.
IS_AVAILABLE | NUMBER(1) | NOT NULL | This flag determines whether or not the portal page is available. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_ENTITLED | NUMBER(1) | NOT NULL | This flag determines whether or not entitlement rulesets have been defined for the portal page. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_MANDATORY | NUMBER(1) | NOT NULL | This flag determines whether or not the portal page is mandatory. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_VISIBLE | NUMBER(1) | NOT NULL | This flag determines whether or not the portal page is visible. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
LAYOUT_ID | NUMBER(15) | NOT NULL | The record identifier for the layout associated with this personalized portal page. FK to the LAYOUT table.
DISPLAY_NAME | VARCHAR(254) | NOT NULL | The display name associated with the personalized portal page.

The PORTAL_PAGE_P13N_LAYOUT_POOL Database Table

Table 1-8 describes the PORTAL_PAGE_P13N_LAYOUT_POOL table. This table stores all available layouts for each of the personalized portal pages.

The Primary Key is comprised of both PORTAL_PAGE_P13N_ID and LAYOUT_ID.

Table 1-8 PORTAL_PAGE_P13N_LAYOUT_POOL Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTAL_PAGE_P13N_ID | NUMBER(15) | NOT NULL | PK—combined with LAYOUT_ID this is a unique record identifier. FK to the PORTAL_PAGE_P13N table.
LAYOUT_ID | NUMBER(15) | NOT NULL | PK—combined with PORTAL_PAGE_P13N_ID this is a unique record identifier. FK to the LAYOUT table.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.

The PORTLET Database Table

Table 1-9 describes the PORTLET table. This table stores all of the defined portlets.

The Primary Key is PORTLET_ID.

Table 1-9 PORTLET Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTLET_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
PORTAL_ID | NUMBER(15) | NOT NULL | The record identifier for the portal associated with this particular portlet. FK to the PORTAL table.
PORTLET_XML_REF | VARCHAR(254) | NOT NULL | The identifier for this portlet as specified in the portal definition XML file.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.

The PORTLET_P13N Database Table

Table 1-10 describes the PORTLET_P13N table. This table holds all personalized portlet records.

The Primary Key is PORTLET_P13N_ID.

Table 1-10 PORTLET_P13N Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTLET_P13N_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
PORTLET_ID | NUMBER(15) | NOT NULL | The record identifier for the portal associated with this particular personalized portlet. FK to the PORTLET table.
PORTAL_PAGE_P13N_ID | NUMBER(15) | NOT NULL | The record identifier for the portal associated with this particular personalized portlet. FK to the PORTAL_PAGE_P13N table.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.
PORTLET_PLACEHOLDER_ID | NUMBER(15) | NOT NULL | The record identifier for the placeholder selected for this particular personalized portlet. FK to the PORTLET_PLACEHOLDER table.
PLACEHOLDER_INDEX_NUMBER | NUMBER(4) | NOT NULL | An index specifying the order that this particular personalized portlet appears within the specified placeholder.
IS_MANDATORY | NUMBER(1) | NOT NULL | This flag determines whether or not the personalized portlet is mandatory. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_AVAILABLE | NUMBER(1) | NOT NULL | This flag determines whether or not the personalized portlet is available. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_MOVEABLE | NUMBER(1) | NOT NULL | This column is not being used and is reserved for future use.
IS_MINIMIZEABLE | NUMBER(1) | NOT NULL | This flag determines whether or not the personalized portlet can be minimized. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_MAXIMIZEABLE | NUMBER(1) | NOT NULL | This flag determines whether or not the personalized portlet can be maximized. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_FLOATABLE | NUMBER(1) | NOT NULL | This flag determines whether or not the personalized portlet can float. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_VISIBLE | NUMBER(1) | NOT NULL | This flag determines whether or not the personalized portlet is visible. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_MINIMIZED | NUMBER(1) | NOT NULL | This flag determines whether or not the personalized portlet is minimized at startup. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
IS_ENTITLED | NUMBER(1) | NOT NULL | This flag determines whether or not entitlement rulesets have been defined for the portlet. 0 equates to false, 1 equates to true, and -1 equates to unspecified.
DISPLAY_NAME | VARCHAR(254) | NOT NULL | The display name of the personalized portlet.

The PORTLET_PLACEHOLDER Database Table

Table 1-11 describes the PORTLET_PLACEHOLDER table.

The Primary Key is PORTLET_PLACEHOLDER_ID.

Table 1-11 PORTLET_PLACEHOLDER_ID Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
PORTLET_PLACEHOLDER_ID | NUMBER(15) | NOT NULL | PK—stores all of the defined placeholder records for each portlet.
PORTAL_PAGE_ID | NUMBER(15) | NOT NULL | The record identifier for the portal page associated with this particular portlet placeholder. FK to the PORTAL_PAGE table.
PORTLET_PLACEHOLDER_XML_REF | VARCHAR(254) | NOT NULL | The identifier for this placeholder as specified in the portal definition XML file.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.

The RESOURCE_GROUP_ADMIN Database Table

Table 1-12

describes the RESOURCE_GROUP_ADMIN table. This table is used to store administrative privileges for various users.

The Primary Key is comprised of both RESOURCE_GROUP_TAXONOMY and DELEGATED_TO_USER_NAME.

Table 1-12 RESOURCE_GROUP_ADMIN Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
RESOURCE_GROUP_TAXONOMY | VARCHAR(254) | NOT NULL | PK—combined with DELEGATED_TO_USER_NAME this is used as a unique record identifier.
DELEGATED_TO_USER_NAME | VARCHAR(200) | NOT NULL | PK—combined with RESOURCE_GROUP_TAXONOMY this is a unique record identifier.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.
DELEGATED_BY_USER_NAME | VARCHAR(200) | NULL | The user who administered privileges to the DELEGATED_TO_USER_NAME.

The SKIN Database Table

Table 1-13 describes the SKIN table. This table holds all of the defined skins.

The Primary Key is comprised of SKIN_ID.

Table 1-13 SKIN Table Metadata

Column Name | Data Type | Null Value | Description and Recommendations
--- | --- | --- | ---
SKIN_ID | NUMBER(15) | NOT NULL | PK—a unique, system-generated number used as the record identifier.
PORTAL_ID | NUMBER(15) | NOT NULL | The record identifier for the portal associated with this particular skin. FK to the PORTAL table.
SKIN_XML_REF | VARCHAR(254) | NOT NULL | The identifier for this skin as specified in the portal definition XML file.
CREATION_DATE | DATE | NOT NULL | The date and time the record was created.
MODIFIED_DATE | DATE | NOT NULL | The last date and time the record was modified.

Defined Constraints

Various constraints are defined and used in the Portal database schema. These constraints can be found in the following scripts:

portal_create_fkeys.sql—contains the Foreign Keys

portal_create_tables.sql—contains the Check Constraints

Table 1-14 Constraints Defined on Portal Database Tables

Table Name | Constraints
--- | ---
LAYOUT | Column—PORTAL_ID Constraint—FK1_LAYOUT Constraint Type—FOREIGN KEY Ensures that each LAYOUT references an existing PORTAL via the PORTAL_ID column.
PORTAL_P13N_SKIN_POOL | Column—PORTAL_P13N_ID Constraint—FK1_SKIN_POOL Constraint Type—FOREIGN KEY Ensures that each PORTAL_P13N_SKIN_POOL references an existing PORTAL_P13N via the PORTAL_P13N_ID column.
PORTAL_PAGE | Column—PORTAL_ID Constraint—FK1_PORTAL_PAGE Constraint Type—FOREIGN KEY Ensures that each PORTAL_PAGE references an existing PORTAL via the PORTAL_ID column
PORTAL_PAGE_P13N | Column— PORTAL_P13N_ID Constraint—FK3_PL_PAGE_P13N Constraint Type—FOREIGN KEY Ensures that each PORTAL_PAGE_P13N references an existing PORTAL_P13N via the PORTAL_P13N_ID column.
PORTAL_PAGE_P13N_LAYOUT_POOL | Column—PORTAL_PAGE_P13N_ID Constraint— FK2_LAYOUT_POOL Constraint Type—FOREIGN KEY Ensures that each PORTAL_PAGE_P13N_LAYOUT_POOL references an existing PORTAL_PAGE_P13N via the PORTAL_PAGE_P13N_ID column.
PORTLET | Column—PORTAL_ID Constraint—FK1_PORTLET Constraint Type—FOREIGN KEY Ensures that each PORTLET references an existing PORTAL via the PORTAL_ID column.
PORTLET_P13N | Column—PORTAL_PAGE_P13N_ID Constraint—FK3_PORTLET_P13N Constraint Type—FOREIGN KEY Ensures that each PORTAL_P13N references an existing PORTAL_PAGE_P13N via the PORTAL_PAGE_P13N_ID column.
PORTLET_PLACEHOLDER | Column— PORTAL_PAGE_ID Constraint—FK1_PORTLET_PHD Constraint Type—FOREIGN KEY Ensures that each PORTLET_PLACEHOLDER references an existing PORTAL_PAGE via the PORTAL_PAGE_ID column.
SKIN | Column—PORTAL_ID Constraint— FK1_SKIN Constraint Type—FOREIGN KEY Ensures that each SKIN references an existing PORTAL via the PORTAL_ID column.
PORTAL_P13N_LAYOUT | Column—PORTAL_P13N_ID Constraint—FK1_PL_LAYOUT Constraint Type—FOREIGN KEY Ensures that each PORTAL_P13N_LAYOUT references an existing PORTAL_P13N via the PORTAL_P13N_ID column.
PORTAL_PAGE_P13N | Column—IS_AVAILABLE Constraint—CC1_PL_PAGE_P13N Constraint Type—CHECK Ensures the value of the IS_AVAILABLE column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_MANDATORY Constraint—CC2_PL_PAGE_P13N Constraint Type—CHECK Ensures the value of the IS_MANDATORY column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_VISIBLE Constraint—CC3_PL_PAGE_P13N Constraint Type—CHECK Ensures the value of the IS_VISIBLE column is either -1 (undefined), 0 (false) or (true). Column—IS_ENTITLED Constraint—CC4_PL_PAGE_P13N Constraint Type—CHECK Ensures the value of the IS_ENTITLED column is either -1 (undefined), 0 (false) or 1 (true).
PORTLET_P13N | Column—IS_MINIMIZED Constraint—CC1_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_MINIMIZED column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_MANDATORY Constraint—CC2_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_MANDATORY column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_AVAILABLE Constraint—CC3_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_AVAILABLE column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_MOVEABLE Constraint—CC4_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_MOVEABLE column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_MINIMIZEABLE Constraint—CC5_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_MINIMIZEABLE column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_MAXIMIZEABLE Constraint—CC6_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_MAXIMIZEABLE column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_FLOATABLE Constraint—CC7_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_FLOATABLE column is either -1 (undefined), 0 (false) or 1 (true).
PORTLET_P13N (continued) | Column—IS_VISIBLE Constraint—CC8_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_VISIBLE column is either -1 (undefined), 0 (false) or 1 (true). Column—IS_ENTITLED Constraint—CC9_PORTLET_P13N Constraint Type—CHECK Ensures the value of the IS_ENTITLED column is either -1 (undefined), 0 (false) or 1 (true).