Database diagram

Dependency diagram for the CSV output format, including SQL scripts for generating the tables and to load the data with load data local infile at the end of this article.

Overview of data format elements

In CSV output format, these elements are files, in XML or JSON output format these are nodes.

The data format standard consists of the following elements:

  • product: product from the ITscope catalogue.
  • supplierItem: source of supply for an ITscope product. A specific product offering from a distributor listed on ITscope.
  • project: price, availability and further information on a project item.
  • attribute: properties of a product.
  • attributeCluster: property clusters in which the product can be found for feature searches. Can summarise several property expressions in intervals, according to the quantity, e.g. 64-128 MB RAM.
  • accessory: original accessories and compatible accessories for a product.

Elements marked with a    vary depending on the selected export language.

product

Product from the ITscope catalogue.

Name Compulsory field Data type Description
puid Yes long Unique key
ean   String (18) EAN
manufacturerSKU   String (40) Manufacturer article number
icecatId   String (255) IceCat ID
cnetId   String (255) CNET ID
bechlemId   String (255) Bechlem ID
eClass   String (255) eClass ID Version 5.1.2
manufacturerId Yes long Reference to the manufacturer of this product (n:1 on Manufacturer.id)
manufacturerName   String (255) Name of the manufacturer
productNameWithManufacturer Yes String Product name, including manufacturer name
 shortDescription   String Short description for the product
 longDescription   String Long description for the product
productTypeId Yes long Reference to the product type of this product
productTypeGroupId Yes String (3) Reference to product type group (ProductTypeGroup.id, n:1)
productTypeGroupName Yes String (255) Name of the group of product types, e.g. network technology. Can be used as 1st category level.
productTypeName Yes String (255) Identifier for the product type. Can be used as 2nd category level.
attributeTypeId1   long Unambiguous attribute key to build a possible subcategory, points to 1:n on AttributeCluster.attributeTypeId
attributeTypeName1   String (255) Identifier of the attribute to build up a possible subcategory
attributeTypeId2   long Unambiguous Attribute Key to build a possible subcategory, points to 1:n on AttributeCluster.attributeTypeId
attributeTypeName2   String (255) Identifier of the attribute to build up a possible subcategory
attributeTypeId3   long Unambiguous Attribute Key to build a possible subcategory, points to 1:n on AttributeCluster.attributeTypeId
attributeTypeName3   String (255) Identifier of the attribute to build up a possible subcategory
attributeTypeId4   long Unambiguous Attribute Key to build a possible subcategory, points to 1:n on AttributeCluster.attributeTypeId
attributeTypeName4   String (255) Identifier of the attribute to build up a possible subcategory
attributeTypeId5   long Unambiguous Attribute Key to build a possible subcategory, points to 1:n on AttributeCluster.attributeTypeId
attributeTypeName5   String (255) Identifier of the attribute to build up a possible subcategory
 attributeValue1   String (1024) Property value for the attribute from ProductType.attributeTypeId1. Can be used as a 3rd category level, if applicable.
 attributeValue2   String (1024) Property value for the attribute from ProductType.attributeTypeId2. Can be used as a 4th category level, if applicable.
 attributeValue3   String (1024) Property value for the attribute from ProductType.attributeTypeId3. Can be used as a 5th category level, if applicable.
 attributeValue4   String (1024) Property value for the attribute from ProductType.attributeTypeId4
 attributeValue5   String (1024) Property value for the attribute from ProductType.attributeTypeId5
productSubTypeId   String (1024) ID of product sub type
productSubType   String (1024) Design property of the product, e.g. mouse or keyboard for input devices. Should not be used as a 3rd category level.
productLineId   long ID of the product line
productLine   String (1024) Product line
productModel   String (1024) Product model identifier
estimateGrossWeight   Double Weight in kilograms
largeDimX   String (1024) Length of the product including unit of measurement
largeDimY   String (1024) Height of the product including unit of measurement
largeDimZ   String (1024) Width of product including unit of measurement
customsTariffNumber   String (255) Customs tariff number
deeplink Yes String (2048) Deep link to the ITscope.com platform
 standardHtmlDatasheet Yes String (2048) URL, link to HTML standard datasheet
 standardPdfDatasheet   String (2048) URL, link to PDF standard datasheet
manufacturerSite   String (1024) URL, link to manufacturer page
manufacturerDatasheet   String (1024) URL, link to manufacturer datasheet
imageThumb   String (1024) Preview of the best product image
imageThumbWidth   Integer Width of the preview image in pixels
imageThumbHeight   Integer Height of the preview image in pixels
image1   String (1024) Link to the best possible product image, in the largest version
imageWidth1   Integer Width of image in pixels
imageHeight1   Integer Height of the image in pixels
image2   String (1024) Link to another good product image, preferably including product packaging (never the same as the first image)
imageWidth2   Integer Width of the image in pixels
imageHeight2   Integer Height of the image in pixels
image3   String (1024) Link to the first image in the gallery (not one of the already listed images above)
imageWidth3   Integer Width of the image in pixels
imageHeight3   Integer Height of the image in pixels
image4   String (1024) Link to the second picture in the gallery (not one of the already listed images above)
imageWidth4   Integer Width of the image in pixels
imageHeight4   Integer Height of the image in pixels
image5   String (1024) Link to the third image in the gallery (never one of the already listed)
imageWidth5   Integer Width of the image in pixels
imageHeight5   Integer Height of the image in pixels
energyLabel   String (1024) Link to the Energy Label image
entryDate Yes Date Since when has the product been listed on the platform?
rank   Integer Overall popularity rank (rank 1 to n, a high number corresponds to a bad ranking)
qualification   Integer Qualification of the product
 warrantyText   String Warranty text for the product
 marketingText   String Marketing text for the product
 htmlSpecs   String Technical properties of the product, in HTML format
recommendedRetailPriceNet   Double Manufacturer's RRP
price   BigDecimal Price basis for the calculated price
priceCalc   BigDecimal Calculated price, based on the individual price calculation
currencyCode   String (3) Currency unit that applies to this price information
priceCalcVat   BigDecimal Sales tax rate that was used to determine the calculated price
priceLastUpdate   Date Time of last update of price information
priceSupplierId   long Reference to a supplier (1:1) who has provided the source of supply with this price information
priceSupplierName   String (255) Name of the supplier that provided this source of supply
priceSupplierItemId   long Reference to a source of supply (n:1); if this field is zero, then the price information refers to a product (source of supply reference and product reference are mutually exclusive)
priceSupplierSKU   String (40) Product identifier given by the supplier that provided this source of supply
stockSupplierText   String (40) Textual inventory information of the supplier, directly taken over, without interpretation
stockStatus   Integer Numeric key of the delivery status of the inventory information
 stockStatusText   String (255) Delivery status of inventory information, such as 'in stock' or 'in field warehouse'
stock   Integer Quantity of products for the delivery status indicated in this structure
externalStock   Integer External stock (additional information provided by some suppliers in structures with 'in stock' delivery status)
incomingStock   Integer Stock quantity that will be replenished (additional information provided by some suppliers in structures with 'in stock' delivery status)
stockAvailabilityDate   Date Delivery date for products that are not in stock
stockLastUpdate   Date Time of last update of inventory information
aggregatedStatus Yes Integer Best availability status
 aggregatedStatusText Yes String (20) Best availability status
aggregatedStock Yes integer Sum of all stock levels
aggregatedSupplierItems Yes Integer Sum of all distributors offering this product
 supplierItem Yes List <supplierItem> Source of supply of an ITscope product. A specific offer from a distributor listed on ITscope
 attribute   List <attribute> Properties of a product
 attributeCluster   List <attributeCluster> Property clusters in which the product can be found for feature searches. Can summarise several property expressions in intervals, according to the quantity, e.g. 64-128 MB RAM
 accessory   List <accessory> Original accessories and compatible accessories for a product

supplierItem

Source of supply for an ITscope product. A specific offering from a distributor listed on ITscope.

Name Compulsory field Data type Description
id Yes long Unique key
ean   String (18) EAN
manufacturerSKU   String (40) Supplier's part number (does not have to match ITscope)
supplierSKU   String (40) Product number of the supplier
supplierId Yes long Refers 1:1 to Supplier.id
supplierName   String (255) Name of Supplier
manufacturerName   String (80) Manufacturer name as specified by supplier
productName   String (255) Exact identifier of the product, as given by the supplier
longDescription   String (60000) Extended product description by the supplier
conditionId Yes Integer Numerical code for the product condition
 conditionName Yes String (255) Condition of the item (new, used, B-Stock, Refurbished, etc.)
eolProduct Yes Boolean Notice for discontinued products
matchQuality Yes Integer Mapping quality of the product with the ITscope product catalogue
eanValid Yes Boolean Flag to indicate whether the EAN of the suppler is valid according to the EAN criteria
specialOffer Yes Boolean Flag to indicate if the item is on special offer
promotion   String (40) Name of the promotion activity of the supplier
v   Integer VAT rate for the item
copyrightLevy   Double Copyright levy
customsTariffNumber   String (50) Customs tariff number
countryOfOrigin   String (20) Country of origin of the product
largeDimX   Double Length of the product including unit of measurement
largeDimY   Double Height of the product including unit of measurement
largeDimZ   double Width of product including unit of measurement
warrantyText   String (40) Supplier's warranty
deeplink   String (1024) Link to the product with the supplier
recommendedRetailPriceNet   Double RRP indication from the supplier
price Yes BigDecimal Price
priceCalc Yes BigDecimal Calculated price, based on the individual price calculation
currencyCode Yes String (3) Currency unit that applies to this price information
priceCalcVat Yes BigDecimal Sales tax rate that was used to determine the calculated price
priceLastUpdate Yes Date Time of last update of price information
stockSupplierText   String Stock information on the source of supply, as given by the supplier
stockStatus   Integer Numeric key for the delivery status of the inventory information
 stockStatusText   String (255) Delivery status of inventory information, such as 'in stock' or 'in field warehouse'
stock   Integer Stock level
externalStock   Integer External stock (additional information provided by some suppliers in structures with 'in stock' delivery status)
incomingStock   Integer Stock is being replenished (additional information provided by some suppliers in structures with 'in stock' delivery status)
stockAvailabilityDate   Date Delivery date for products that are not in stock
lastStockUpdate   Date Time of last update of inventory information

project

Price, availability and further information on a project agreement.

Name Compulsory field Data type Description
supplierProjectId   String (40) Project number of the supplier
manufacturerProjectId Yes String (40) Project number of the manufacturer (unique key)
projectName   String (255) Project description
endCustomer   String (255) End customer for the project item
price Yes BigDecimal Project price
validFrom   Date Date the project conditions become valid
validTo   Date Date the project conditions expire
targetQuantity   Integer Negotiated project amount
remainingQuantity Yes Integer Remaining quota that can be ordered
minQuantity   Integer Minimum ordering quantity
projectLastUpdate   Date Time of last update of project information

attribute

Properties of a product.

Name Compulsory field Data type Description
 displayValue   String (10000) The readable value of the property, including unit
attributeTypeId   long Unique identifier of a concrete property type
 attributeTypeName   String (255) Name of the concrete property type
attributeTypeRank   long Ranking of the property type, for example, can be used for sorting
attributeTypeGroupId   long Identifier of a property group, no table reference, but uniquely identifies attributeTypeGroupName
 attributeTypeGroupName   String (255) Name of the property group

attributeCluster

Property clusters in which the product can be found for feature searches. Can summarise several property expressions in intervals, according to the quantity, e.g. 64-128 MB RAM.

Name Compulsory field Data type Description
id   long Unique ITscope Key
productTypeId   long Reference to the product type, optional
 productTypeName   String (255) Name of the product type to which this property belongs, optional
 attributeTypeName   String (255) Name of a property. Can be the same as the name from ProductType.attributeTypeName (1..5)
 attributeTypeGroupName   String (255) Name of a property group
 name   String (255) Identifier of the characteristic (e.g. 2 GB, 15", 80 W - 120 W)
rank   long Rank of the property

accessory

Original accessories and compatible accessories for a product.

Name Compulsory field Data type Description
referencedProductId   long Reference to a reference product, e.g. a product that is offered as an original accessory for another product
typeId   Integer Internal identifier for the type of the reference product
 type   String (255) Identifier for the type of the reference product, including original accessories, all compatible accessories, etc.

Database scripts for CSV

Note: This page has been translated with the Google Translation Service.

Have more questions? Submit a request