|
|
The Order Processing Database Schema
This topic describes the database schema for the BEA WebLogic Commerce Server Order Processing package. 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 2-1 shows the Entity-Relation diagram for the BEA WebLogic Commerce Server order processing database.
Figure 2-1 Entity-Relation Diagram for the Order Processing Database
Explanations for the columns in each table are provided in the remainder of this topic.
The WLCS_CUSTOMER Database Table
Table 2-1 describes the metadata for the Commerce Server WLCS_CUSTOMER table. This table is used to store information about the customer in the order processing database.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
CUSTOMER_ID |
VARCHAR(20) |
VARCHAR2(20) |
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(256) |
VARCHAR2(256) |
A label for the customer (such as preferred, standard, or business). |
FIRST_NAME |
VARCHAR(30) |
VARCHAR2(30) |
The customer's first name. |
LAST_NAME |
VARCHAR(30) |
VARCHAR2(30) |
The customer's last name. |
MIDDLE_NAME |
VARCHAR(30) |
VARCHAR2(30) |
The customer's middle name. |
TITLE |
VARCHAR(10) |
VARCHAR2(10) |
The customer's preferred title (Mr., Mrs., Ms.). |
SUFFIX |
VARCHAR(10) |
VARCHAR2(10) |
The customer's preferred suffix (Jr., Sr.). |
|
VARCHAR(80) |
VARCHAR2(80) |
The customer's email address. |
HOME_PHONE |
VARCHAR(15) |
VARCHAR2(15) |
The customer's home phone number. |
BUSINESS_PHONE |
VARCHAR(20) |
VARCHAR2(20) |
The customer's business phone number. |
FAX |
VARCHAR(15) |
VARCHAR2(15) |
The customer's fax number. |
MAILING_GEOCODE |
VARCHAR(10) |
VARCHAR2(2) |
The code used by the TAXWARE system to identify taxes for the order based on jurisdiction. |
MAILING_STREET1 |
VARCHAR(30) |
VARCHAR2(30) |
The first line in the customer's street address. |
MAILING_STREET2 |
VARCHAR(30) |
VARCHAR2(30) |
The second line in the customer's street address. |
MAILING_CITY |
VARCHAR(30) |
VARCHAR2(30) |
The city in the customer's address. |
MAILING_STATE |
VARCHAR(40) |
VARCHAR2(40) |
The state in the customer's address. |
MAILING_COUNTRY |
VARCHAR(40) |
VARCHAR2(40) |
The country in the customer's address. |
MAILING_POBOX |
VARCHAR(30) |
VARCHAR2(30) |
The post office box in the customer's address. |
MAILING_COUNTY |
VARCHAR(30) |
VARCHAR2(30) |
The county in the customer's address. |
MAILING_POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
The postal (zip) code in the customer's address. |
MAILING_POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
Format or type of postal code, generally determined by country (such as zip code in the United States). |
The WLCS_SHIPPING_ADDRESS Database Table
Table 2-2 describes the metadata for the 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.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
SHIPPING_ADDRESS_ID |
INTEGER |
NUMBER(15) |
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_ |
CUSTOMER_ID |
VARCHAR(20) |
VARCHAR2(20) |
A unique identifier for the customer. |
MAP_KEY |
VARCHAR(30) |
VARCHAR2(30) |
Key that maps multiple shipping addresses with a single customer. |
SHIPPING_GEOCODE |
VARCHAR(2) |
VARCHAR2(2) |
The code used by the TAXWARE system to identify taxes for the order based on jurisdiction. |
SHIPPING_STREET1 |
VARCHAR(30) |
VARCHAR2(30) |
The first line in the customer's shipping address. |
SHIPPING_STREET2 |
VARCHAR(30) |
VARCHAR2(30) |
The second line in the customer's shipping address. |
SHIPPING_CITY |
VARCHAR(30) |
VARCHAR2(30) |
The city in the customer's shipping address. |
SHIPPING_STATE |
VARCHAR(40) |
VARCHAR2(40) |
The state in the customer's shipping address. |
SHIPPING_COUNTRY |
VARCHAR(40) |
VARCHAR2(40) |
The country in the customer's shipping address. |
SHIPPING_POBOX |
VARCHAR(30) |
VARCHAR2(30) |
The post office box in the customer's shipping address. |
SHIPPING_COUNTY |
VARCHAR(30) |
VARCHAR2(30) |
The county in the customer's shipping address. |
SHIPPING_POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
The postal (zip) code in the customer's shipping address. |
SHIPPING_POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
Format or type of postal code, generally determined by country (such as zip code in the United States). |
The WLCS_CREDIT_CARD Database Table
Table 2-3 describes the metadata for the 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.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
CREDIT_CARD_ID |
INTEGER |
NUMBER(15) |
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. |
CUSTOMER_ID |
VARCHAR(20) |
VARCHAR2(20) |
A unique identifier for the customer. |
MAP_KEY |
VARCHAR(20) |
VARCHAR2(20) |
Key that maps multiple credit cards with a single customer. |
CC_NUMBER |
VARCHAR(200) |
VARCHAR2(200) |
The customer's credit card number. This is encrypted if is.encryption. |
CC_TYPE |
VARCHAR(20) |
VARCHAR2(20) |
The customer's credit card type (VISA, MasterCard, and so on). |
CC_EXP_DATE |
DATE |
DATE |
The expiration date on the customer's credit card. |
CC_NAME |
VARCHAR(50) |
VARCHAR2(50) |
The credit card holder's name. |
CC_DISPLAY_NUMBER |
VARCHAR(20) |
VARCHAR2(20) |
The version of the credit card number that is displayed (all Xs except last 4-digits). |
CC_COMPANY |
VARCHAR(50) |
VARCHAR2(50) |
The name of the credit card company. |
BILLING_GEOCODE |
VARCHAR(2) |
VARCHAR2(2) |
The code used by the TAXWARE system to identify taxes for the order based on jurisdiction. |
BILLING_STREET1 |
VARCHAR(30) |
VARCHAR2(30) |
The first line in the customer's billing address. |
BILLING_STREET2 |
VARCHAR(30) |
VARCHAR2(30) |
The second line in the customer's billing address. |
BILLING_CITY |
VARCHAR(30) |
VARCHAR2(30) |
The city in the customer's billing address. |
BILLING_STATE |
VARCHAR(40) |
VARCHAR2(40) |
The state in the customer's billing address. |
BILLING_COUNTRY |
VARCHAR(40) |
VARCHAR2(40) |
The country in the customer's billing address. |
BILLING_POBOX |
VARCHAR(30) |
VARCHAR2(30) |
The post office box in the customer's billing address. |
BILLING_COUNTY |
VARCHAR(30) |
VARCHAR2(30) |
The county in the customer's billing address. |
BILLING_POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
The postal (zip) code in the customer's billing address. |
BILLING_POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
Format or type of postal code, generally determined by country (such as zip code in the United States). |
The WLCS_TRANSACTION Database Table
Table 2-4 describes the metadata for the Commerce Server WLCS_TRANSACTION table. This table is used to store data for every payment transaction in the order processing database.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
TRANSACTION_ID |
VARCHAR(25) |
VARCHAR2(25) |
A unique identifier for the transaction. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ |
BATCH_ID |
VARCHAR(15) |
VARCHAR2(15) |
A unique identifier of a batch submitted for settlement, as returned by CyberCash. This field need not be populated for other external payment services. |
TRAN_DATE |
DATE |
DATE |
The date of the transaction (date on which the transaction was first started). |
TRAN_STATUS |
VARCHAR(20) |
VARCHAR2(20) |
The current status of the transaction (Settled, Authorized, MarkedForSettle, PendingSettle, Retry, Settled). |
TRAN_AMOUNT |
DOUBLE |
NUMBER(16,4) |
The most recent amount applied to the transaction (MarkForSettle amounts can be different from the authorization amount). |
TRAN_CURRENCY |
VARCHAR(30) |
VARCHAR2(30) |
The currency of the transaction. |
CC_NUMBER |
VARCHAR(200) |
VARCHAR2(200) |
The customer's credit card number. This is encrypted if is.encryption. |
CC_TYPE |
VARCHAR(20) |
VARCHAR2(20) |
The customer's credit card type (VISA, MasterCard, and so on). |
CC_EXP_DATE |
DATE |
DATE |
The expiration date on the customer's credit card. |
CC_NAME |
VARCHAR(50) |
VARCHAR2(50) |
The credit card holder's name. |
CC_DISPLAY_NUMBER |
VARCHAR(20) |
VARCHAR2(20) |
The version of the credit card number that is displayed (all Xs except last 4-digits). |
CC_COMPANY |
VARCHAR(50) |
VARCHAR2(50) |
The name of the credit card company. |
GEOCODE |
VARCHAR(2) |
VARCHAR2(2) |
The code used by the TAXWARE system to identify taxes for the order based on jurisdiction. |
STREET1 |
VARCHAR(30) |
VARCHAR2(30) |
The first line in the customer's street address. |
STREET2 |
VARCHAR(30) |
VARCHAR2(30) |
The second line in the customer's street address. |
CITY |
VARCHAR(30) |
VARCHAR2(30) |
The city in the customer's address. |
STATE |
VARCHAR(40) |
VARCHAR2(40) |
The state in the customer's address. |
COUNTRY |
VARCHAR(40) |
VARCHAR2(40) |
The country in the customer's address. |
POBOX |
VARCHAR(30) |
VARCHAR2(30) |
The post office box in the customer's address. |
COUNTY |
VARCHAR(30) |
VARCHAR2(30) |
The county in the customer's address. |
POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
The postal (zip) code in the customer's address. |
POSTAL_CODE_TYPE |
VARCHAR(10) |
VARCHAR2(10) |
Format or type of postal code, generally determined by country (such as zip code in the United States). |
DESCRIPTION |
VARCHAR(30) |
VARCHAR2(30) |
Any additional data. Can be NULL. |
The WLCS_TRANSACTION_ENTRY Database Table
Table 2-5 describes the metadata for the 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.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
TRANSACTION_ENTRY_ID |
INTEGER |
NUMBER(25) |
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_ |
TRAN_ENTRY_SEQUENCE |
VARCHAR(30) |
VARCHAR2(30) |
Represents the running count per transaction. |
TRAN_ENTRY_DATE |
DATE |
DATE |
The date of the log entry. |
TRAN_ENTRY_STATUS |
VARCHAR(20) |
VARCHAR2(20) |
The status of the transaction when this entry was made. |
TRAN_ENTRY_AMOUNT |
DOUBLE |
NUMBER(16,4) |
The amount of the transaction when the log entry was made. |
TRAN_ENTRY_CURRENCY |
VARCHAR(30) |
VARCHAR2(30) |
The currency of the transaction. |
TRANSACTION_ID |
VARCHAR(25) |
VARCHAR2(25) |
A unique identifier for the transaction. |
The WLCS_SAVED_ITEM_LIST Database Table
Table 2-6 describes the metadata for the 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.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
CUSTOMER_ID |
VARCHAR(20) |
VARCHAR2(20) |
A unique identifier for the customer. |
SKU |
VARCHAR(40) |
VARCHAR2(40) |
A unique identifier (the "Stock Keeping Unit," or SKU) for a product item. |
The WLCS_ORDER Database Table
Table 2-7 describes the metadata for the 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 BEA WebLogic Commerce Server product does not populate the SHIPPING_AMOUNT, SHIPPING_CURRENCY, PRICE_AMOUNT, or PRICE_CURRENCY columns.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
ORDER_ID |
VARCHAR(20) |
VARCHAR2(20) |
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) |
VARCHAR2(20) |
A unique identifier for the customer. |
TRANSACTION_ID |
VARCHAR(25) |
VARCHAR2(25) |
A unique identifier for the transaction. |
STATUS |
VARCHAR(20) |
VARCHAR2(20) |
The status of the order. |
ORDER_DATE |
DATE |
DATE |
The date the order was placed. |
SHIPPING_METHOD |
VARCHAR(40) |
VARCHAR2(40) |
The method by which the order is to be shipped. |
SHIPPING_AMOUNT |
DOUBLE PRECISION |
NUMBER(16,4) |
The shipping amount for the order. |
SHIPPING_CURRENCY |
VARCHAR(10) |
VARCHAR2(10) |
The currency associated with the shipping amount. |
PRICE_AMOUNT |
DOUBLE PRECISION |
NUMBER(16,4) |
The price of the order. |
PRICE_CURRENCY |
VARCHAR(10) |
VARCHAR2(10) |
The currency associated with the price. |
SHIPPING_GEOGODE |
VARCHAR(2) |
VARCHAR2(2) |
The code used by the TAXWARE system to identify taxes for the order based on jurisdiction. |
SHIPPING_STREET1 |
VARCHAR(30) |
VARCHAR2(30) |
The first line in the customer's shipping address. |
SHIPPING_STREET2 |
VARCHAR(30) |
VARCHAR2(30) |
The second line in the customer's shipping address. |
SHIPPING_CITY |
VARCHAR(30) |
VARCHAR2(30) |
The city in the customer's shipping address. |
SHIPPING_STATE |
VARCHAR(40) |
VARCHAR2(40) |
The state in the customer's shipping address. |
SHIPPING_COUNTRY |
VARCHAR(40) |
VARCHAR2(40) |
The country in the customer's shipping address. |
SHIPPING_POBOX |
VARCHAR(30) |
VARCHAR2(30) |
The post office box in the customer's shipping address. |
SHIPPING_COUNTY |
VARCHAR(40) |
VARCHAR2(30) |
The county in the customer's shipping address. |
SHIPPING_POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
The postal (zip) code in the customer's shipping address. |
SHIPPING_POSTAL_CODE |
VARCHAR(10) |
VARCHAR2(10) |
Format or type of postal code, generally determined by country (such as zip code in the United States). |
SPECIAL_INSTRUCTIONS |
VARCHAR(256) |
VARCHAR2(256) |
Any special shipping instructions associated with the order. |
SPLITTING_PREFERENCE |
VARCHAR(256) |
VARCHAR2(256) |
The splitting preferences for the customer's order. |
The WLCS_ORDER_LINE Database Table
Table 2-8 describes the metadata for the 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.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
ORDER_LINE_ID |
INTEGER |
NUMBER(15) |
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 |
DOUBLE |
NUMBER(16,4) |
The quantity of the item in the shopping cart. |
PRODUCT_ID |
VARCHAR(40) |
VARCHAR2(40) |
An identification number for the item in the shopping cart. |
TAX_AMOUNT |
DOUBLE |
NUMBER(16,4) |
The tax amount for the order. |
TAX_CURRENCY |
VARCHAR(10) |
VARCHAR2(10) |
The currency associated with the tax amount. |
SHIPPING_AMOUNT |
DOUBLE |
NUMBER(16,4) |
The shipping amount for the order. |
SHIPPING_CURRENCY |
VARCHAR(10) |
VARCHAR2(10) |
The currency associated with the shipping amount. |
UNIT_PRICE_AMOUNT |
DOUBLE |
NUMBER(16,4) |
The unit price amount for the item. |
UNIT_PRICE_CURRENCY |
VARCHAR(10) |
VARCHAR2(10) |
The currency associated with the unit price. |
MSRP_AMOUNT |
DOUBLE |
NUMBER(16,4) |
The MSRP amount for the item. |
MSRP_CURRENCY |
VARCHAR(10) |
VARCHAR2(10) |
The currency associated with the MSRP amount. |
DESCRIPTION |
VARCHAR(256) |
VARCHAR2(256) |
The name of the item that is part of the order. |
ORDER_ID |
VARCHAR(20) |
VARCHAR2(20) |
A unique identifier for the order. |
The WLCS_SHIPPING_METHOD Database Table
Table 2-9 describes the metadata for the Commerce Server WLCS_SHIPPING_METHOD table. This table is used to store information about the shiping method in the order processing database.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
PK_IDENTIFIER |
VARCHAR(20) |
VARCHAR2(20) |
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_ |
CARRIER |
VARCHAR(40) |
VARCHAR2(40) |
The carrier being used to ship the order (such as UPS, FedEx, and so on). |
METHOD |
VARCHAR(40) |
VARCHAR2(40) |
The method by which the order is to be shipped (such as air, 2nd day air, parcel post, and so on). |
AVERAGE_SHIPPING_ |
INTEGER |
NUMBER |
The average number of days it will take the order to arrive. |
PRICE_VALUE |
DOUBLE PRECISION |
NUMBER(16,4) |
The amount it will cost to ship the order. |
PRICE_CURRENCY |
VARCHAR(10) |
VARCHAR2(10) |
The currency associated with the PRICE_VALUE column (such as dollars, pounds, lira, and so on). |
WEIGHT_LIMIT |
DOUBLE PRECISION |
NUMBER(16,4) |
The weight limit for the shipment. |
RESTRICTIONS |
VARCHAR(256) |
VARCHAR2(256) |
Any restrictions associated with the shipment. |
DESCRIPTION |
VARCHAR(256) |
VARCHAR2(256) |
A description of the shipping method (such as FedEx Overnight or Standard). |
PO_BOX_ALLOWED |
INTEGER |
NUMBER |
Specifies whether or not the shipment can be left at a post office box. |
SIGNATURE_REQUIRED |
INTEGER |
NUMBER |
Specifies whether or not a signature is required upon receipt of the shipment. |
SATURDAY_DELIVERY |
INTEGER |
NUMBER |
Specifies whether or not the shipment can be delivered on Saturday. |
INTERNATIONAL_ |
INTEGER |
NUMBER |
Specifies whether or not international delivery is an option. |
SIZE_LIMIT |
DOUBLE PRECISION |
NUMBER(16,4) |
The size limit for the shipment. |
PACKAGING_TYPE |
VARCHAR(50) |
VARCHAR2(50) |
The packaging type for the shipment. |
The WLCS_SECURITY Database Table
Table 2-10 describes the metadata for the 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 BEA WebLogic Commerce Server product.
Column Name |
Cloudscape Type |
Oracle Type |
Description and Recommendations |
---|---|---|---|
ID |
INTEGER |
NUMBER(2) |
A unique identifier for the key pair. This field is the table's primary key and cannot be NULL. |
PUBLIC_KEY |
VARCHAR(2000) |
VARCHAR2(2000) |
The public key to be used for encryption/decryption of credit cards. |
PRIVATE_KEY |
VARCHAR(2000) |
VARCHAR2(2000) |
The private key to be used for encryption/decryption of credit cards. |
The SQL Files and Defined Constraints
The BEA WebLogic Commerce Server product provides two SQL files to create the Cloudscape and Oracle versions of the order processing database. The SQL files are in the WL_COMMERCE_HOME\db\<database-vendor>\wlcs\ directories. WL_COMMERCE_HOME is the directory in which you installed the WebLogic Commerce Server software, and the <database-vendor> directory is either cloudscape or oracle. The files are:
You can run the create-* procedure for the desired database vendor type by invoking one of the following procedures in the WL_COMMERCE_HOME\db\directory:
create-all-cloudscape.sh (UNIX)
create-all-oracle.sh (UNIX)
Note: You can also create just the WebLogic Commerce Server or WebLogic Personalization Server specific databases. Simply substitute wlcs or wlps for all in the procedures shown above.
In each create-order-* SQL file, the database tables described earlier in this chapter are created. In addition, the SQL files define constraints. Table 2-11 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.
Table Name |
Constraints as Defined in create-order-oracle.sql |
---|---|
WLCS_SHIPPING_ADDRESS |
If a customer is deleted from the database, the CUSTOMER_FK constraint causes all their associated shipping addresses to be deleted. The constraint for the schema in Oracle is: |
WLCS_CREDIT_CARD |
If a customer is deleted from the database, the CUSTOMER_CREDIT_CARD_FK constraint causes all their associated credit cards to be deleted. The constraint for the schema in Oracle is: |
WLCS_TRANSACTION_ENTRY |
If a transaction is deleted from the database, the WLCS_TRANSACTION_FK constraint causes all the associated transaction entries be deleted. The constraint for the schema in Oracle is: |
WLCS_ORDER_LINE |
If an order is deleted from the database, the WLCS_ORDER_FK constraint causes all the associated order line items to be deleted. The constraint for the schema in Oracle is: |
|
Copyright © 2000 BEA Systems, Inc. All rights reserved.
|