![]() |
![]() |
|
|
The Order Processing Database Schema
This topic describes the database schema for Managing Purchases and Processing Orders services. Understanding this schema will be helpful to those who may be customizing or extending the technologies provided in the product.
This topic includes the following sections:
The Entity-Relation Diagram
Figure 10-1 shows the logical Entity-Relation diagram for the WebLogic Commerce Server order and discount tables in the Commerce database. See the subsequent sections in this chapter for information about the data type syntax.
Figure 10-1 Entity-Relation Diagram for the Order and Discount Tables
Explanations for the columns in each table are provided in the remainder of this topic.
List of Tables Comprising the Order Processing Schema
The WebLogic Commerce Server order management system is comprised of the following tables:
The Order Processing Data Dictionary
In this section, the 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 Commerce Server. However, they will be in place in future versions of WebLogic Commerce Server and we want you to be aware of these relationships now.
The WLCS_CREDIT_CARD Database Table
Table 10-1 describes the metadata for the WebLogic Commerce Server WLCS_CREDIT_CARD table. This table is used to store information related to a customer's credit card(s) in the order processing database.
The Primary Key is CREDIT_CARD_ID.
The WLCS_COUNTRY Database Table
Table 10-2 describes the metadata for the WebLogic Commerce Server WLCS_COUNTRY table. This is a reference table and contains pertinent information regarding each country around the world.
The primary key is COUNTRY_ID.
This table holds infomation that pertains to all of the various currencies used throughout the world.
Note: The WLCS_COUNTRY feature has not been implemented at this time and, therefore, this table is not being used/populated.
The WLCS_CURRENCY Database Table
Table 10-3 describes the metadata for the WebLogic Commerce Server WLCS_CURRENCY table. This table holds info pertaining to all of the various currencies used throughout the world.
The primary key is CURRENCY_ID.
Note: The WLCS_CURRENCY feature has not been implemented at this time and, therefore, this table is not being used/populated.
The WLCS_CUSTOMER Database Table
Table 10-4 describes the metadata for the WebLogic Commerce Server WLCS_CUSTOMER table. This table is used to store information about the customer in the order processing database.
The primary key is CUSTOMER_ID.
The DISCOUNT Database Table
Table 10-5 describes the metadata for the WebLogic Commerce Server DISCOUNT table. This table stores stores one or more discount records for every DISCOUNT_SET record.
The Primary Key is DISCOUNT_ID.
The DISCOUNT_ASSOCIATION Database Table
Table 10-6 describes the metadata for the WebLogic Commerce Server DISCOUNT_ASSOCIATION table. This table associates each customer with a discount and maintains information regarding the times the customer has used each discount.
The primary key is DISCOUNT_ASSOCIATION_ID.
The DISCOUNT_SET Database Table
Table 10-7 describes the metadata for the WebLogic Commerce Server DISCOUNT_SET table. This table is used to establish a group of discounts as a set.
The primary key is DISCOUNT_SET_ID.
The WLCS_ORDER Database Table
Table 10-8 describes the metadata for the WebLogic Commerce Server WLCS_ORDER table. This table is used to store information about a customer's specific order in the order processing database.
Note: The WebLogic Commerce Server product does not populate the SHIPPING_AMOUNT, SHIPPING_CURRENCY, PRICE_AMOUNT, or PRICE_CURRENCY columns.
The primary key is ORDER_ID.
The WLCS_ORDER_LINE Database Table
Table 10-9 describes the metadata for the WebLogic Commerce Server WLCS_ORDER_LINE table. This table is used to store information about each line of a customer's shopping cart in the order processing database.
The Primary Key isORDER_LINE_ID.
The ORDER_ADJUSTMENT Database Table
Table 10-10 describes the metadata for the WebLogic Commerce Server ORDER_ADJUSTMENT table. This table is used to maintain information about a discount taken at the order level (for example, $20.00 off any order between 1/1/01 and 1/31/01.)
The Primary Key is ORDER_ADJUSTMENT_ID.
The ORDER_LINE_ADJUSTMENT Database Table
Table 10-11 describes the metadata for the WebLogic Commerce Server ORDER_LINE_ADJUSTMENT table. This table is used to maintain information about a discount taken at the order line item level (for example, 10% off SKU "Power Drill").
The Primary Key is ORDER_LINE_ADJUSTMENT_ID.
The WLCS_SAVED_ITEM_LIST Database Table
Table 10-12 describes the metadata for the WebLogic Commerce Server WLCS_SAVED_ITEM_LIST table. This table is used to store information about the customer's saved shopping cart items in the order processing database.
The WLCS_SECURITY Database Table
Table 10-13 describes the metadata for the WebLogic Commerce Server WLCS_SECURITY table. This table is used to persist public and private keys for encryption and decryption purposes in the order processing database. This table is meant for internal use by the WebLogic Commerce Server product.
The WLCS_SHIPPING_ADDRESS Database Table
Table 10-14 describes the metadata for the WebLogic Commerce Server WLCS_SHIPPING_ADDRESS table. This table is used to store information related to a customer's shipping address(es) in the order processing database.
The primary key is SHIPPING_ADDRESS_ID.
The WLCS_SHIPPING_METHOD Database Table
Table 10-15 describes the metadata for the WebLogic Commerce Server WLCS_SHIPPING_METHOD table. This table is used to store information about the shipping method in the order processing database.
The primary key is PK_IDENTIFIER.
The WLCS_TRANSACTION Database Table
Table 10-16 describes the metadata for the WebLogic Commerce Server WLCS_TRANSACTION table. This table is used to store data for every payment transaction in the order processing database.
The primary key is TRANSACTION_ID.
The WLCS_TRANSACTION_ENTRY Database Table
Table 10-17 describes the metadata for the WebLogic Commerce Server WLCS_TRANSACTION_ENTRY table. This table is used to store (log) the different states a payment transaction has passed through in the order processing database.
The primary key is TRANSACTION_ENTRY_ID.
The SQL Scripts Used to Create the Database
The database schemas for the WebLogic Personalization Server, WebLogic Commerce Server and Campaign Manager for WebLogic are all created by executing the create_all script for the target database environment.
Cloudscape
For Cloudscape, execute one of the following:
Oracle
For Oracle, from the command line, move to the following directory:
WL_COMMERCE_HOME/db/oracle/8.1.6
After logging into SQL*Plus, simply execute the create_all.sql script, for example @create_all.
Defined Constraints
In each create-order-* SQL file, the database tables described earlier in this chapter are created. In addition, the SQL files define constraints. Table 10-18 shows the table name and describes the constraint(s) defined for it.
Note: The sample SQL statements shown in the table are from the create-order-oracle.sql file. The syntax is different for Cloudscape. Except where noted, the effect of each constraint is the same.
![]() |
![]() |
|
Copyright © 2001 BEA Systems, Inc. All rights reserved.
|