Database diagram
Dependency diagram for the CSV output format, including SQL scripts for generating the tables and to load the data with loadData 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 they are nodes.
The data format standard consists of the following elements:
- product: product from the ITscope catalogue
- supplierItem: source of supply of an ITscope product. A specific offer from a distributor listed on ITscope
- project: price, availability and further information on a project agreement
- attribute: properties for a product
- attributeCluster: property clusters using which the product can be found for feature searches. Can summarise several property expressions according to the quantity in intervals, e.g. 64-128 MB RAM
- accessory: original accessories and compatible accessories for a product
- historicalDataItem: weekly or monthly aggregated historical data, depending on the desired period and aggregation
- contractParameter: contractual parameters for a source of supply that must be specified when placing an order, see orders involving licensees.
Elements marked with a globe change their language depending on the selected export oder API request language:
Fields marked with a * are new compared to the API 2.0, fields with a ** have been moved to a different location within the file, fields with a *** have been renamed.
product
Product from the ITscope catalogue.
| Name | Mandatory | Data type | Description |
|---|---|---|---|
| puid | Yes | long | Unique key |
| ean | String (18) | EAN | |
| manufacturerSKU | String (150) | Manufacturer’s SKU/article number [was String(40) before 18/10/2021] | |
| icecatId | String (255) | Icecat ID | |
| cnetId | String (255) | 1WorldSync ID (formerly CNET) | |
| bechlemId | String (255) | Bechlem ID | |
| eClass | String (255) | eClass ID Version 5.1.2 | |
| eClassV7 * | String (255) | eClass ID Version 7.0 | |
| customsTariffNumber * | String (255) | Customs tariff number | |
| UNSPSC * | String (14) | UNSPSC Classification Code | |
| manufacturerId | Yes | Long | Reference to the manufacturer of this product (n:1 to Manufacturer.id) |
| manufacturerName | String (255) | Name of the manufacturer | |
| productName *** | Yes | String | Product identifier, by default including manufacturer name, removal of manufacturer name can only be configured via export |
| shortDescription | String | By default the short name of the product, but can only be additionally configured via the export | |
| longDescription | String | Long description text 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 of the product type. Can be used as 2nd category level |
| attributeTypeId1 | Long | Unambiguous attribute key to build a possible subcategory, points 1:n to 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 1:n to 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 1:n to 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 1:n to 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 1:n to AttributeCluster.attributeTypeId | |
| attributeTypeName5 | String (255) | Identifier of the attribute to build up a possible subcategory | |
| attributeValue1 | String (1024) | Attribute cluster value for the attribute from ProductType.attributeTypeId1. Can be used as 3rd category level if available | |
| attributeValue2 | String (1024) | Attribute cluster value for the attribute from ProductType.attributeTypeId1. Can be used as 4th category level if available | |
| attributeValue3 | String (1024) | Attribute cluster value for the attribute from ProductType.attributeTypeId1. Can be used as 5th category level if available | |
| attributeValue4 | String (1024) | Attribute cluster value for the attribute from ProductType.attributeTypeId4 | |
| attributeValue5 | String (1024) | Attribute cluster value for the attribute from ProductType.attributeTypeId5 | |
| productSubTypeId | String (1024) | ID of the product sub type | |
| productSubType | String (1024) | Product sub type, e.g. mouse or keyboard for input devices. Should not be used as 3rd category level | |
| productLineId | Long | ID of the product line | |
| productLine | String (1024) | Product line | |
| productModel | String (1024) | Product model identifier | |
| colorFamilyId * | String (255) | ID of the colour family | |
| colorFamily * | String (255) | Colour family | |
| estimateGrossWeight | Double | Weight in kilograms | |
| grossDimX | String (1024) | Length of the product including unit of measurement | |
| grossDimY | String (1024) | Height of the product including unit of measurement | |
| grossDimZ | String (1024) | Width of product including unit of measurement | |
| combinedLengthAndGirth * | String (36) | Girth | |
| netWeight * | String (36) | Net weight | |
| netDimX * | String (36) | Net dimension X | |
| netDimY * | String (36) | Net dimension Y | |
| netDimZ * | String (36) | Net dimension Z | |
| valueAddedTaxGermany * | Integer | German VAT rate | |
| deeplink | Yes | String (2048) | Deeplink 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 (thumbnail) | |
| imageThumbWidth | Integer | Width of thumbnail image in pixels | |
| imageThumbHeight | Integer | Height of thumbnail image in pixels | |
| imageHighRes1 *** | String (1024) | Link to the best possible image, in the largest version | |
| imageWidth1 | Integer | Width of image in pixels | |
| imageHeight1 | Integer | Height of image in pixels | |
| image2 | String (1024) | Link to another good product image, preferably product packaging (never the same as the first image) | |
| imageWidth2 | Integer | Width of image in pixels | |
| imageHeight2 | Integer | Height of image in pixels | |
| image3 | String (1024) | Link to the first image in the gallery (never one of those already listed) | |
| imageWidth3 | Integer | Width of image in pixels | |
| imageHeight3 | Integer | Height of image in pixels | |
| image4 | String (1024) | Link to the second image in the gallery (never one of those already listed) | |
| imageWidth4 | Integer | Width of image in pixels | |
| imageHeight4 | Integer | Height of image in pixels | |
| image5 | String (1024) | Link to the third image in the gallery (never one of those already listed) | |
| imageWidth5 | Integer | Width of image in pixels | |
| imageHeight5 | Integer | Height of image in pixels | |
| energyLabel | String (1024) | Link to the Energy Label image | |
| energyEfficiencyClass * | String (10) | Energy efficiency class | |
| 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 | Product qualification level | |
| warrantyText | String | Warranty text for the product | |
| marketingText | String | Marketing text for the product | |
 keySellingPoints * | String | Key Selling Points | |
| packageContents * | String | Scope of delivery | |
| productFeatures * | String | Product features | |
| htmlMainSpecs * | String | Technical characteristics of the product in short form, separated with br-tags | |
| htmlSpecs | String | Technical characteristics of the product, in HTML format | |
| recommendedRetailPriceNet | Double | Manufacturer’s MSRP | |
| 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 for the calculated price | |
| priceLastUpdate | Date | Time of last price information update | |
| priceSupplierId | Long | Reference to the supplier (1:1) that provided the source of supply for 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, the price information refers to a product (source of supply reference and product reference are mutually exclusive) | |
| priceSupplierSKU | String (150) | Product identifier of the supplier that provided the source of supply [was String(40) before 18/10/2021] | |
| 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 for the delivery status indicated in this structure | |
| externalStock | Integer | Quantity available from external sources (additional information provided by some suppliers for delivery status ‘in stock’) | |
| incomingStock | Integer | Quantity in transit (additional information provided by some suppliers for delivery status ‘in stock’) | |
| stockAvailabilityDate | Date | Delivery date for products currently not in stock | |
| stockLastUpdate | Date | Time of last update of inventory information | |
| aggregatedStatus | Integer | Best availability status | |
| aggregatedStatusText | String (20) | Best availability status | |
| aggregatedStock | integer | Total of all inventories | |
| aggregatedSupplierItems | Integer | Sum of all distributors for this product | |
| contractTypeId * | Integer | The ID of the product family for licence and service products | |
| contractTypeName * | String (20) | The name of the product family for licence and service products | |
| priceBillingPeriod | Integer | Length of the price billing period | |
| priceBillingPeriodUnit | String (40) | Unit of the price billing period (e.g. “Years”, “Month”) | |
| priceSubscriptionPeriod | Integer | Length of the price subscription period | |
| priceSubscriptionPeriodUnit | String (40) | Unit of the price subscription period (e.g. “Years”, “Month”) | |
| supplierItem | yes | List <supplierItem> | Sources of supply for an ITscope product. Specific offers from distributors listed on ITscope |
| attribute | List <attribute> | Attributes of a product | |
| attributeCluster | List <attributeCluster> | Attribute clusters in which the product can be found for feature searches. Can summarise multiple attribute values according to the quantity in intervals, eg 64-128MB RAM | |
| accessory | List <accessory> | Original accessories and compatible accessories for a product | |
| keyContent | List<keyContent> | Content data for a product that is available in the form of keys (e.g. category IDs, classification IDs forEclass, UNSPSC, ETIM, each with different versions of the classifications and customs tariff numbers) |
supplierItem
Source of supply for an ITscope product. A specific offer from a distributor listed on ITscope.
| Name | Mandatory | Data type | Description |
|---|---|---|---|
| id | Yes | Long | Unique key |
| ean | String (18) | EAN specified by the supplier | |
| manufacturerSKU | String (150) | Supplier’s SKU/article number (does not have to match ITscope’s) [was String(40) before 18/10/2021] | |
| supplierSKU | String (150) | Product number specified by the supplier [was String(40) before 18/10/2021] | |
| supplierId | Yes | Long | Refers 1:1 to Supplier.id |
| supplierName | String (255) | Name of the supplier | |
| manufacturerName | String (80) | Manufacturer name as specified by the supplier | |
| productName | String (255) | Exact identifier of the product, as given by the supplier | |
| longDescription | String (60000) | Long product description specified by the supplier | |
| conditionId | Yes | Integer | Numeric code for the product’s condition |
| conditionName | Yes | String (255) | Condition of the product (new, used, B-Stock, refurbished, etc.) |
| eolProduct | Yes | Boolean | Flag a product as end of life (soon to be discontinued) |
| matchQuality | Yes | Integer | Mapping quality of the product to ITscope’s product catalogue |
| eanValid | Yes | Boolean | Flag to indicate whether the EAN of the supplier is valid according to EAN criteria |
| specialOffer | Yes | Boolean | Flag to indicate whether product is a special offer |
| promotion | String (40) | Name of the supplier’s promotional campaign | |
| vat | Integer | VAT rate for the product | |
| copyrightLevy | Double | Levy due to copyright | |
| customsTariffNumber | String (50) | Customs tariff number | |
| countryOfOrigin | String (20) | Country of origin of the product | |
| grossDimX | Double | Length of the product including unit of measurement | |
| grossDimY | Double | Height of the product including unit of measurement | |
| grossDimZ | 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 | MSRP indication provided by 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 for the calculated price |
| priceLastUpdate | Yes | Date | Time of last price information update |
| stockSupplierText | String | 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 for the delivery status indicated in this structure | |
| externalStock | Integer | Quantity available from external sources (additional information provided by some suppliers for delivery status ‘in stock’) | |
| incomingStock | Integer | Quantity in transit (additional information provided by some suppliers for delivery status ‘in stock’) | |
| stockAvailabilityDate | Date | Delivery date for products currently not in stock | |
| lastStockUpdate | Date | Time of last update of inventory information | |
| contractTypeId * | Integer | The ID of the product family for licence and service products | |
| contractTypeName * | String (20) | The name of the product family for licence and service products | |
| priceBillingPeriod | Integer | Length of the price billing period | |
| priceBillingPeriodUnit | String (40) | Unit of the price billing period (e.g. “Years”, “Month”) | |
| priceSubscriptionPeriod | Integer | Length of the price subscription period | |
| priceSubscriptionPeriodUnit | String (40) | Unit of the price subscription period (e.g. “Years”, “Month”) | |
| weeeRegNo | String (12) | WEEE number, is considered as proof of the registration of the manufacturer, the brand and the type of electrical or electronic equipment carried out in the country of manufacture. | |
| contractParameters | List<contractParameter> | Contract parameters of a source of supply that must be specified when placing an order | |
| projects | List<project> | Projects for the source of supply | |
| supplierPackagingInfo | supplierPackagingInfo | Information on the packaging units of a source of supply | |
| scaledPriceInfo | scaledPriceInfo | Information on scaled prices of a source of supply |
supplierPackageInfo
Information on packaging units of a source of supply. The unit codes for packaging units are in UN/ECE Recommendation No.21. This standard is recommended by UN/CEFACT. Details see UNECE.
| Name | Mandatory | Data type | description |
|---|---|---|---|
| contentUnit | String (50) | Unit code of the contents of a package | |
| intervalQuantity | | Integer | Number indicating in which graduation the item can be ordered (in order units). |
| minQuantity | Integer | Minimum number of packaging units to trigger an order | |
| numberContentUnitPerOrderUnit | Integer | Number of contents of a packaging unit | |
| orderUnit | | String (50) | Packaging unit code |
| priceQuantity | Integer | Number of packaging units for a given price | |
| supplierPackingQuantity | Integer | Number of packaging units a supplier provides in a purchase order | |
| totalPriceQuantity | | Integer | Total number of units (packaging units * contents) for a given price |
scaledPriceInfo
Information on scaled prices of a source of supply
| Name | Mandatory | Data type | description |
|---|---|---|---|
| scale | Yes | Integer | Indicates the scale from which the price applies |
| price | Yes | BigDecimal | The price for the scale |
project
Price, availability and further information for a project item.
| name | Mandatory | 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 | |
| projectBundleId * | String (50) | Unique key for the project bundle (activate via the ITscope test lab) | |
| projectBundleName * | String (255) | Name of the project bundle (activate via the ITscope test lab) | |
| projectPosition * | Integer | Project line item number (activate via the ITscope test lab) |
attribute
Attributes of a product.
| Name | Mandatory | Data type | Description |
|---|---|---|---|
| value * | String (1024) | The base value (see also base unit) of the attribute. For text as a text module, for numbers as the smallest value | |
| displayValue | String (1024) | The readable value of the attribute, including unit | |
| attributeTypeId | Long | Unique identifier of the attribute type | |
| attributeTypeName | String (255) | Name of the attribute type | |
| attributeTypeRank | Long | Ranking of the attribute type – this can, for example, be used for sorting | |
| attributeTypeGroupId | Long | Identifier of an attribute group, no table reference, but uniquely identifies attributeTypeGroupName | |
| attributeTypeGroupName | String (255) | Name of the attribute group |
attributeCluster
Attribute clusters in which the product can be found for feature searches. Can summarise multiple attribute values according to the quantity in intervals, eg 64-128MB RAM.
| Name | Mandatory | 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 attribute belongs, optional | |
| attributeTypeName | String (255) | Name of an attribute. Can be the same as the name from ProductType.attributeTypeName (1..5) | |
| attributeTypeGroupName | String (255) | Name of an attribute group | |
| name | String (255) | Identifier of the product version (e.g. 2 GB, 15 “, 80 W – 120 W) | |
| rank | Long | Rank of the attribute |
accessory
Original accessories and compatible accessories for a product.
| Name | Mandatory | Data type | Description |
|---|---|---|---|
| referencedProductId | Long | Refers to a reference product, e.g. a product which is listed as an original accessory for this particular 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. |
historicalDataItem
Weekly or monthly aggregated historical data, depending on desired period and aggregation.
| Name | Mandatory | Data type | Description |
|---|---|---|---|
| date | String | Start date of the week or month depending on the desired aggregation | |
| priceMin | BigDecimal | Price minimum within a given period | |
| priceMax | BigDecimal | Price maximum within a given period | |
| priceMedian | BigDecimal | Price median within a given period | |
| priceMedianInStock | BigDecimal | Price median for in-stock items within a given period | |
| priceAvg | BigDecimal | Price average within a given period | |
| priceAvgInStock | BigDecimal | Price Average for in-stock items within a given time period | |
| supplierCount | Integer | Number of suppliers within a given period | |
| supplierCountInStock | Integer | Number of suppliers with stock within a given period | |
| stockSum | Integer | Sum of in-stock items within a given period | |
| salesTrend | Integer | Sales trend within a given period |
contractParameter
Contract parameters of a source of supply that must be specified when placing an order, see ordering service and licence products.
| Name | Mandatory | Data type | description |
|---|---|---|---|
| supplierItemRefId | Yes | Long | Reference to the source of supply (supplierItem) |
| displayName | Yes | String(255) | Description of the contract parameter |
| orderParameterName | Yes | String(255) | Name of the contract parameter that must be provided in the order |
| mandatory | Yes | Boolean | Is this parameter mandatory or optional in an order? |
| example | String(255) | Example value for this parameter |
keyContent
Content data for a product that is in the form of keys (eg category IDs, customs tariff numbers)
| Name | Mandatory | Data type | Description |
|---|---|---|---|
| key | Long | Key of the record, not unique | |
| langId | Long | Internal identifier of the language of this content | |
| contentModel | contentModel | Reference to the type of content (ContentModel.id, n:1) | |
| contentProvider | contentProvider | Reference to the content provider (ContentProvider.id, n:1) | |
  contentCategory | contentCategory | Reference to the content category (ContentCategory.id, n:1) | |
| value | String (255) | Content of the content (value of the key) | |
| lang | String (10) | Language code in ISO639 for the language of the content |
contentModel
Classification of content in different types by the content provider.
| Name | Mandatoy | Data type | Description |
|---|---|---|---|
| id | long | Unique key | |
| name | String (1023) | Displayed name of the content (e.g. Icecat standard image large, 1WorldSync product ID) |
contentProvider
Origin of the content e.g. CNET, Icecat, manufacturer or distributor
| Name | Mandatory | Data type | Description |
|---|---|---|---|
| id | long | Unique key | |
| name | String (255) | Clear text name of the content supplier (e.g. Icecat, 1WorldSync) | |
| rank | Integer | Prioritisation for sorting, same values are possible several times |
contentCategory
ITscope categorisation of content (e.g. structured feature descriptions, product photo, etc.).
| Name | MAndatory | Data type | Description |
|---|---|---|---|
| id | long | Unique key | |
| name | String (255) | Identifier of category |