Difference between revisions of "X-Cart:Database layout"
(Added Customers cluster diagram) |
(→Products) |
||
| (25 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: | ||
|} | |} | ||
| − | Here 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 87: | Line 111: | ||
! Table column !! Referenced by | ! Table column !! Referenced by | ||
|- | |- | ||
| − | | xcart_products.productid || xcart_products_categories. | + | | xcart_products.productid || xcart_products_categories.productid |
|- | |- | ||
| || xcart_products_lng.productid | | || xcart_products_lng.productid | ||
| Line 148: | Line 172: | ||
|} | |} | ||
| − | + | 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 172: | Line 202: | ||
|} | |} | ||
| − | + | 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 204: | Line 236: | ||
|- | |- | ||
| || xcart_shipping_labels.orderid | | || xcart_shipping_labels.orderid | ||
| − | |||
| − | |||
|- | |- | ||
| || xcart_amazon_orders.orderid | | || xcart_amazon_orders.orderid | ||
| Line 218: | Line 248: | ||
|} | |} | ||
| − | + | 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.
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):
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):
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:
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):
