X-Cart:Database layout

Overview

The current MySQL diagram for any X-Cart version with extensive comments can be found in the sql/xcart_tables.sql file.

The X-Cart database consists of four major clusters:

• Customers — information on users registered with your store, including customers, providers, partners and administrators.
• Products — product information, such as name, description, images, price, weight, etc.
• Categories — product category data.
• Orders — order details, including product, buyer, delivery and payment details, as well as related discount and gift options.

Each cluster has its own a set of data tables that are linked within the unit and with related tables in other units.

Clusters

Customers

Relationships in the Customers cluster

Table column	Referenced by
xcart_customers.id	xcart_address_book.userid
	xcart_category_bookmarks.userid
	xcart_change_password.userid
	xcart_customer_bonuses.userid
	xcart_discount_coupons_login.userid
	xcart_export_ranges.userid
	xcart_giftreg_events.userid
	xcart_import_cache.userid
	xcart_login_history.userid
	xcart_old_passwords.userid
	xcart_order_status_history.userid
	xcart_orders.userid
	xcart_partner_banners.userid
	xcart_partner_clicks.userid
	xcart_partner_commissions.userid
	xcart_partner_payment.userid
	xcart_partner_product_commissions.userid
	xcart_partner_views.userid
	xcart_product_bookmarks.userid
	xcart_provider_commissions.userid
	xcart_provider_product_commissions.userid
	xcart_register_field_values.userid
	xcart_seller_addresses.userid
	xcart_stats_cart_funnel.userid
	xcart_stats_customers_products.userid
	xcart_survey_maillist.userid
	xcart_survey_results.userid
	xcart_wishlist.userid

Here is the entity relationship diagram for the Customers cluster (click to zoom in):

Products

Relationships in the Products cluster

Table column	Referenced by
xcart_products.productid	xcart_products_categories.categoryid
	xcart_products_lng.productid
	xcart_quick_flags.productid
	xcart_quick_prices.productid
	xcart_stats_customers_products.productid
	xcart_variant_backups.productid
	xcart_variants.productid
	xcart_wishlist.productid
	xcart_classes.productid
	xcart_delivery.productid
	xcart_discount_coupons.productid
	xcart_download_keys.productid
	xcart_extra_field_values.productid
	xcart_featured_products.productid
	xcart_gcheckout_restrictions.productid
	xcart_ge_products.productid
	xcart_offer_product_params.productid
	xcart_order_details.productid
	xcart_pconf_products_classes.productid
	xcart_pconf_wizards.productid
	xcart_pricing.productid
	xcart_product_bookmarks.productid
	xcart_product_features.productid
	xcart_product_foptions.productid
	xcart_product_memberships.productid
	xcart_product_options_js.productid
	xcart_product_reviews.productid
	xcart_product_rnd_keys.productid
	xcart_product_taxes.productid
	xcart_product_votes.productid

Here is the entity relationship diagram for the Products cluster (click to zoom in):

Categories

Relationships in the Categories cluster

Table column	Referenced by
xcart_categories.categoryid	xcart_categories_lng.categoryid
	xcart_categories_subcount.categoryid
	xcart_category_bookmarks.categoryid
	xcart_category_memberships.categoryid
	xcart_discount_coupons.categoryid
	xcart_featured_products.categoryid
	xcart_products_categories.categoryid

Here is the entity relationship diagram for the Categories cluster:

Orders

In an X-Cart based store, customers purchase products by placing orders. An order is a collection of information about a purchase, which includes:

• Information on product items being purchased (product titles, quantities, prices).
• Information on the customer who is making the purchase (personal information, billing and shipping addresses, contact information).
• Information about the method of delivery selected by the customer (name of method, shipping cost).
• Information about the payment method selected by the customer (name of method, payment details).
• Discounts, coupon codes or gift certificates applied.
• Total amount.

Each order has its unique order id number and, at any moment in time, is characterized by a status.

Relationships in the Orders cluster

Table column	Referenced by
xcart_orders.orderid	xcart_partner_adv_orders.orderid
	xcart_partner_payment.orderid
	xcart_partner_product_commissions.orderid
	xcart_provider_commissions.orderid
	xcart_provider_product_commissions.orderid
	xcart_shipping_labels.orderid
	xcart_amazon_orders.orderid
	xcart_gcheckout_orders.orderid
	xcart_order_details.orderid
	xcart_order_extras.orderid
	xcart_order_status_history.orderid

Here is the entity relationship diagram for the Orders cluster (click to zoom in):