Profiles Database Structure

Article
11/12/2009

For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.

The Profiles System stores data in SQL database tables. The Profiles System uses the following SQL Server database tables to store properties of runtime objects.

Table name	Purpose
UserObject	Stores general user information, including names, addresses, multiple-valued fields, and more. The UserObject table serves as the entry point into the Profiles System.
Addresses	Stores addresses that are used in the UserObject and OrganizationObject tables.
OrganizationObject	Stores organizations that are used in the UserObject and BlanketPOs tables.
CreditCards	Stores the credit cards for a user in the Profiles System. It stores the credit cards as a multiple-valued property in the the u_credit_cards field in the UserObject table. It also stores the preferred credit card for a user in the u_preferred_credit_card field in the UserObject table.
Currency	Stores currency and conversion information used for placing orders.
BlanketPOs	Stores purchase orders used for placing orders.

UserObject

The following table defines the default columns, data types, and lengths stored in the UserObject SQL Server database table.

Column name	Data type	Length
u_user_id	nvarchar	50
u_org_id	nvarchar	50
u_user_type	nvarchar	50
u_first_name	nvarchar	50
u_last_name	nvarchar	50
u_email_address	nvarchar	64
u_preferred_address	nvarchar	50
u_addresses	nvarchar	255
u_preferred_credit_card	nvarchar	50
u_credit_cards	nvarchar	255
u_tel_number	nvarchar	32
u_tel_extension	nvarchar	50
u_fax_number	nvarchar	32
u_fax_extension	nvarchar	50
u_user_security_password	nvarchar	100
u_user_id_changed_by	nvarchar	50
u_account_status	nvarchar	50
u_user_catalog_set	nvarchar	50
dt_date_registered	datetime	8
u_campaign_history	nvarchar	50
dt_date_last_changed	datetime	8
dt_csadapter_date_last_changed	datetime	8
dt_date_created	datetime	8
u_language	nvarchar	128
u_Pref1	nvarchar	50
u_Pref2	nvarchar	50
u_Pref3	nvarchar	50
u_Pref4	nvarchar	50
u_Pref5	nvarchar	50
u_password_question	nvarchar	255
u_password_answer	nvarchar	255
u_logon_error_dates	nvarchar	255
u_password_answer_error_dates	nvarchar	255
i_keyindex	int	4
i_access_level_id	int	4
b_change_password	bit	1
dt_date_last_password_changed	datetime	8
dt_last_logon	datetime	8
dt_last_lockedout_date	datetime	8
u_application_name	nvarchar	256
dt_last_activity_date	datetime	8
b_direct_mail_opt_out	bit	1
b_express_checkout	bit	1
dt_date_address_list_last_changed	datetime	8
dt_date_credit_card_list_last_changed	datetime	8

Addresses

The following table defines the default columns, data types, and lengths stored in the Addresses SQL Server database table.

Column name	Data type	Length
u_address_id	nvarchar	50
i_address_type	int	4
u_address_name	nvarchar	50
u_first_name	nvarchar	64
u_last_name	nvarchar	64
u_description	nvarchar	50
u_address_line1	nvarchar	80
u_address_line2	nvarchar	80
u_city	nvarchar	64
u_region_code	nvarchar	50
u_region_name	nvarchar	64
u_postal_code	nvarchar	20
u_country_code	nvarchar	50
u_country_name	nvarchar	50
u_tel_number	nvarchar	32
u_tel_extension	nvarchar	50
i_locale	int	4
u_user_id_changed_by	nvarchar	50
dt_date_last_changed	datetime	8
dt_csadapter_date_last_changed	datetime	8
dt_date_created	datetime	8

OrganizationObject

The following table defines the default columns, data types, and lengths stored in the OrganizationObject SQL Server database table.

Column name	Data type	Length
u_org_id	nvarchar	50
u_Name	nvarchar	255
u_trading_partner_number	nvarchar	255
u_user_id_admin_contact	nvarchar	50
u_user_id_receiver	nvarchar	50
u_preferred_address	nvarchar	50
u_addresses	nvarchar	255
u_org_catalog_set	nvarchar	255
u_user_id_changed_by	nvarchar	50
u_purchasing	nvarchar	255
dt_date_last_changed	datetime	8
dt_csadapter_date_last_changed	datetime	8
dt_date_created	datetime	8

CreditCards

The following table defines the default columns, data types, and lengths stored in the CreditCards SQL Server database table.

Column name	Data type	Length
u_id	nvarchar	50
u_payment_group_id	nvarchar	50
u_cc_number	nvarchar	512
u_expiration_date	nvarchar	12
u_last_4_digits	nvarchar	10
u_billing_address	nvarchar	50
i_keyindex	int	4
u_user_id_changed_by	nvarchar	50
dt_date_last_changed	datetime	8
dt_csadapter_date_last_changed	datetime	8
dt_date_created	datetime	8

Currency

The following table defines the default columns, data types, and lengths stored in the Currency SQL Server database table.

Column name	Data type	Length
u_currency_code	nvarchar	50
u_currency_culture	nvarchar	50
d_conversion_factor	decimal	17
u_user_id_changed_by	nvarchar	50
Dt_date_last_changed	datetime	8
Dt_csadapter_date_last_changed	datetime	8
Dt_date_created	datetime	8

BlanketPOs

The following table defines the default columns, data types, and lengths stored in the BlanketPOs SQL Server database table.

Column name	Data type	Length
u_org_id	nvarchar	50
u_po_id	nvarchar	50
u_po_number	nvarchar	255
u_description	nvarchar	255
u_user_id_changed_by	nvarchar	50
dt_date_last_changed	datetime	8
dt_csadapter_date_last_changed	datetime	8
dt_date_created	datetime	8

Partager via