Difference between revisions of "X-Cart:Database layout"

From X-Cart 4 Classic
Jump to: navigation, search
(Customers)
(Products)
 
(26 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
== Overview ==
 +
 
The current MySQL diagram for any X-Cart version with extensive comments can be found in the '''sql/xcart_tables.sql''' file.
 
The current MySQL diagram for any X-Cart version with extensive comments can be found in the '''sql/xcart_tables.sql''' file.
  
Line 4: Line 6:
  
 
* '''Customers''' — information on users registered with your store, including customers, providers, partners and administrators.
 
* '''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.  
+
* '''Products''' — product information, such as name, description, images, price, weight, etc.
 
* '''Categories''' — product category data.
 
* '''Categories''' — product category data.
 
* '''Orders''' — order details, including product, buyer, delivery and payment details, as well as related discount and gift options.
 
* '''Orders''' — order details, including product, buyer, delivery and payment details, as well as related discount and gift options.
Line 11: Line 13:
  
 
Each cluster has its own a set of data tables that are linked within the unit and with related tables in other units.
 
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 ===
 
=== Customers ===
 +
 +
"Customers" is the collective name for all users of your X-Cart based store; it covers customers, administrators, providers, partners. User data includes user identification details, such as:
 +
 +
* User name, e-mail address and password
 +
* Real name and last name
 +
* Contact information, billing and shipping information where applicable
 +
* User type identifiers and other data.
 +
 +
Data from the Customers table is used in all transactions that involve user data, including user profile management, order management, password management, etc. The following table describes the relationships between the Customers table and other tables of the store.
  
 
'''Relationships in the Customers cluster'''
 
'''Relationships in the Customers cluster'''
Line 76: Line 89:
 
|}
 
|}
  
Coming up is the entity relationship diagram for the Customers cluster.
+
Here is the entity relationship diagram for the Customers cluster (click to zoom in):
 +
 
 +
: [[File:xcart_customers.png|640px|link=http://help.qtmsoft.com/images/4/48/Xcart_customers.png]]
  
 
=== Products ===
 
=== Products ===
 +
 +
The Products table stores information on products in your store, including:
 +
 +
* Product SKU and name
 +
* Brief and detailed product descriptions
 +
* Product images
 +
* Pricing details
 +
* Dimensions and weight details
 +
* Products available in stock.
 +
 +
Product data is used in all product-related transactions in your X-Cart based store, such as product management, offer management, tax management, etc. The following table describes the relationships between the Products table and other tables of the store.
  
 
'''Relationships in the Products cluster'''
 
'''Relationships in the Products cluster'''
Line 85: Line 111:
 
! Table column !! Referenced by
 
! Table column !! Referenced by
 
|-
 
|-
| xcart_products.productid || xcart_products_categories.categoryid
+
| xcart_products.productid || xcart_products_categories.productid
 
|-
 
|-
 
| || xcart_products_lng.productid
 
| || xcart_products_lng.productid
Line 146: Line 172:
 
|}
 
|}
  
Coming up is the entity relationship diagram for the Products cluster.
+
Here is the entity relationship diagram for the Products cluster (click to zoom in):
 +
 
 +
: [[File:xcart_products.png|480px|link=http://help.qtmsoft.com/images/1/1d/Xcart_products.png]]
  
 
=== Categories ===
 
=== Categories ===
 +
 +
Categories provide a centralized structure for the product catalog and help customers locate products. Categories data is used to link categories with products, discount coupons, featured products, etc. Categories may be available subject to users' membership.
 +
 +
The following table describes the relationships between the Categories table and other tables of the store.
  
 
'''Relationships in the Categories cluster'''
 
'''Relationships in the Categories cluster'''
Line 170: Line 202:
 
|}
 
|}
  
Coming up is the entity relationship diagram for the Categories cluster.
+
Here is the entity relationship diagram for the Categories cluster:
 +
 
 +
: [[File:xcart_categories.png|300px|link=http://help.qtmsoft.com/images/f/f3/Xcart_categories.png]]
  
 
=== Orders ===
 
=== Orders ===
Line 202: Line 236:
 
|-
 
|-
 
| || xcart_shipping_labels.orderid
 
| || xcart_shipping_labels.orderid
|-
 
| || xcart_split_checkout.orderid
 
 
|-
 
|-
 
| || xcart_amazon_orders.orderid
 
| || xcart_amazon_orders.orderid
Line 216: Line 248:
 
|}
 
|}
  
Coming up is the entity relationship diagram for the Orders cluster.
+
Here is the entity relationship diagram for the Orders cluster (click to zoom in):
 +
 
 +
: [[File:xcart_orders.png|640px|link=http://help.qtmsoft.com/images/7/79/Xcart_orders.png]]
 +
 
 +
[[Category:X-Cart developer guide]]

Latest revision as of 15:34, 10 October 2012

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.
XC444 DB structure.png

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

"Customers" is the collective name for all users of your X-Cart based store; it covers customers, administrators, providers, partners. User data includes user identification details, such as:

  • User name, e-mail address and password
  • Real name and last name
  • Contact information, billing and shipping information where applicable
  • User type identifiers and other data.

Data from the Customers table is used in all transactions that involve user data, including user profile management, order management, password management, etc. The following table describes the relationships between the Customers table and other tables of the store.

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):

Xcart customers.png

Products

The Products table stores information on products in your store, including:

  • Product SKU and name
  • Brief and detailed product descriptions
  • Product images
  • Pricing details
  • Dimensions and weight details
  • Products available in stock.

Product data is used in all product-related transactions in your X-Cart based store, such as product management, offer management, tax management, etc. The following table describes the relationships between the Products table and other tables of the store.

Relationships in the Products cluster

Table column Referenced by
xcart_products.productid xcart_products_categories.productid
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):

Xcart products.png

Categories

Categories provide a centralized structure for the product catalog and help customers locate products. Categories data is used to link categories with products, discount coupons, featured products, etc. Categories may be available subject to users' membership.

The following table describes the relationships between the Categories table and other tables of the store.

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:

Xcart categories.png

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):

Xcart orders.png