Shop-Script products data storage in the database
-
shop_product
-
nameProduct name.
-
summaryBrief product description displayed in listings. HTML tags are allowed but may be ignored by design themes.
-
meta_title
meta_keywords
meta_description<title>
element and meta tags are added to the<head>
section on the product-viewing page. Special characters used in HTML are escaped. -
descriptionFull product description displayed on the product-viewing page. HTML code is allowed.
-
contact_id
create_datetime
edit_datetimeInformation about the product creation and last editing date and time. -
status1 — product is published in the storefront,
0 — product is hidden from the storefront, i.e. is not displayed in listings, cannot be ordered by website visitors but can be viewed at its URL,
-1 — product is unpublished and is not visible in the storefront. -
urlUnique value used in the product page URL.
-
type_idProduct type ID from the
shop_type
table. -
tax_idID of the tax applied to the product, from the
shop_tax
table. -
category_idMain product category’s ID.
-
image_id
image_filename
extInformation about the man product image. -
badgeID of the product image badge; e.g.,
'new'
,'bestseller'
, etc.
Or the HTML code of a custom badge. -
sku_idMain product variant’s ID. Information about the main product variant is used to display product data in listings.
-
sku_countNumber of product variants.
-
cross_selling
upsellingCross-selling and upselling items setup information:
0 — disabled,
1 — auto-selection according to settings for applied to the product’s type,
2 — manually filled list of recommended products linked to theshop_product_related
table. -
ratingProduct rating from 1 to 5.
-
rating_countNumber of customer votes for the product counted to calculate the rating.
-
total_salesTotal product sales amount expressed in the main store currency.
-
countQuantity of all product variants in all stocks.
NULL means that a product has at least one variant with empty (unlimited) stock quantity.
0 means that a product has run out of stock. -
price
compare_price
min_price
max_price
base_price_selectable
compare_price_selectable
purchase_price_selectable
currencyInformation about product prices.
See below about storing prices-related data. -
shop_product_skus
-
skuSKU code as a string. Can be used during products import or search and cal also be displayed on the website for better user experience.
-
sortProduct SKUS and variants are displayed in the storefront on the ascending order by the
sort
value. -
nameSKU name.
-
available1: available for ordering
0: unavailable -
status1: visible in the storefront
0: hidden -
image_idID основного изображения модификации товара.
-
file_name
file_size
file_descriptionInformation about a file attached to a product variant. A link to such a file is sent to customers after an order is marked as completed.
Used for selling digital products. -
countQuantity available in all stocks.
NULL: unlimited quantity.
0: out of stock. -
price
primary_price
purchase_price
compare_priceInformation about product variant’s prices.
See below about prices-related data. -
Prices
-
A user can select for each product a currency in which must be expressed the prices of all the product’s variants. A 3-character ISO code of a product currency is stored in the
shop_product.currency
field and all store’s currencies are stored in theshop_currency
table.Some prices in tables
shop_product
andshop_product_skus
are stored in the main store currency rather than the product’s currency. This is useful for writing optimized SQL queries by avoiding use of additional tables for product filtering. Below is described which currency is used in each of the fields. -
shop_product
-
Prices displayed in product listings and used for filtering are stored in the main store currency.
-
price
compare_price
min_price
max_priceMain price,
strike-through price,
from ...
to ... -
Basic parameters expressed in the product’s currency are used to automatically generate product variants.
-
base_price_selectable
compare_price_selectable
purchase_price_selectablePrice,
strike-through price,
purchase price. -
shop_product_skus
-
Prices of individual variants expressed in the product’s currency are displayed on the product-viewing page and are used for the creation of new orders.
-
price
compare_price
purchase_pricePrice,
strike-through price,
purchase price is used to calculate profit in sales reports. -
primary_priceProduct variant’s price converted to the main store currency. Used for product filtering.
-
In stock quantities
-
If multiple stocks are set up then a backend user can choose in which stock product quantities must be reduced. You can also select a default stock for orders placed in the storefront.
Each product can use either by-stock information about the available quantity or use common in stock information. If there is at least one product-related entry in the
shop_product_stocks
table then it means that the product uses by-stock information about in stock quantities.In the by-stock storage mode, each variant’s quantity available in each stock is stored in the
shop_product_stocks
table. TheNULL
value in thecount
field means unlimited quantity. 0 means that a product is out of stock.In the
shop_product_skus.count
field is stored the total in stock quantity when the by-stock storage mode is used.NULL
means that there us at least one stock with unlimited product variant quantity. In theshop_product.count
field is stored the total in stock quantity of all product variants.NULL
means a product’s unlimited in-stock quantity.Without the by-stock storage mode, the
shop_product_stocks
table is not used. In this case, the in stock quantity of each product variant is stored in theshop_product_skus.count
field only, and the total quantity of all product variants is stored inshop_product.count
, with the same meaning of theNULL
value.In the
shop_product_stocks_log
table is stored the in stock quantities update history, which is displayed in the store backend.В Shop-Script offers class methods for correcting values in fields
shop_product.count
andshop_product_skus.count
, with the by-stock mode taken into account.// Fix the 'shop_product.count' and 'shop_product_skus.count' values using the contents of 'shop_product_stocks' if a product is in the by-stock storage mode. // Of fix only 'shop_product.count' using 'shop_product_skus.count' if the by-stock mode is not used. shopProductModel->correct(product_id); // Fix 'shop_product.count' using 'shop_product_skus.count' for all products with one query. // My come in useful after a big products update but does not change 'shop_product_skus.count' using 'shop_product_stocks'. shopProductModel->correctCount();
-
Product features
-
Features’ data are stored in a special way to ensure optimal filtering in product listings.
Table
shop_feature
contains the descriptions of main feature properties such as name and type. In tablesshop_feature_values_*
(with a separate table for each feature type —varchar
,text
,double
,range
,color
,dimension
) are stored all available feature values. Tableshop_product_features
links individual products and their variants with certain feature values.Table
shop_product_features_selectable
contains information about feature values selected for the automatic generation of product variants. -
Other products-related tables
-
shop_product_imagesImages.
-
shop_product_ogMeta data for Open Graph to enable integration with social media.
-
shop_product_pagesSubpages.
-
shop_product_paramsExtra parameters to be used in templates in the form
{$product.params.key}
. -
shop_product_reviewsCustomers’ reviews.
-
shop_product_servicesDetailed services settings for products and their variants where common settings for entire product types are not sufficient.
-
shop_product_tagsProduct tags.
-
shop_product_relatedStorage for upselling and cross-selling recommended products where it is required to specify manually picked products instead of using automatic settings.