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 globe 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) | 1WorldSync ID (formerly CNET) | |
| 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) | Attribute cluster value for the attribute from ProductType.attributeTypeId1. Can be used as a 3rd category level, if applicable. | |
| attributeValue2 | String (1024) | Attribute cluster value for the attribute from ProductType.attributeTypeId2. Can be used as a 4th category level, if applicable. | |
| attributeValue3 | String (1024) | Attribute cluster value for the attribute from ProductType.attributeTypeId3. Can be used as a 5th category level, if applicable. | |
| 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 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 image 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 | |
| vat | 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. |