CSV all data 2 (full2.csv)
This format contains all product data, divided into several tables. The tables contain references to each other. The format is available in the export. API retrieval is done with “full.csv”.
SQL database import
Dependencies graph for the individual table and export files
The RatingContent, MediaContent, and TextContent tables do not have a foreign key to the ContentTag table, because the column contentTagRefId contains an ascending comma-separated list of TagIds in the respective tables.
MySql script for creating database tables
This script for MySql creates the tables similar to the dependency diagram including primary and foreign keys.
Sequence for importing the files
- SetGroup
- set
- Supplier
- Alten
- ContentProvider
- ContentModel
- ContentCategory
- ContentTag
- FeatureModel
- AttributeCluster
- Product
- ProductXRefAttributeCluster
- SupplierItem
- ProductPriceInfo
- ProductStock
- SupplierItemPriceInfo
- SupplierItemStock
- KeyContent
- RatingContent
- RefContent
- MediaContent
- TextContent
Main table
Product
Contains all products
| field | Data type | description |
| Puid | Long | Unique key |
| SetRefId | Long | Points 1: 1 to Set.id |
| ManRefId | Long | Refers 1: 1 to Manufacturer.id |
| Rank | Integer | General popularity rank (rank 1 to n, a high number corresponds to a bad ranking) |
| Qualification | Integer | Qualification of the product |
| Ean | String (20) | EAN |
| ManufacturerSKU | String (40) | Manufacturer part number |
| ShortInfo | text | Short identifier |
| ProductName | String | Product name, long name |
| EntryDate | Date | When is the product on the platform |
| RecRetailPrice | Double | Manufacturer’s UVP |
| vat | Double | More fire control |
| EstimateGrossWeight | Double | weight |
| LargeDimX | String | Length of product |
| LargeDimY | String | Height of the product |
| LargeDimZ | String | Width of the product |
| Deeplink | String | Deeplink on the ITscope.com platform |
| Relevance | Double | Addiction relevance |
| FeatureAttribute1 | String (2000) | Property value for the attributes from Set.attributeTypeId1 |
| FeatureAttribute2 | String (2000) | Property value for the attributes from Set.attributeTypeId2 |
| FeatureAttribute3 | String (2000) | Property value for the attributes from Set.attributeTypeId3 |
| FeatureAttribute4 | String (2000) | Property value for the attributes from Set.attributeTypeId4 |
| FeatureAttribute5 | String (2000) | Property value for the attributes from Set.attributeTypeId5 |
| ProductType | String (2000) | Construction property |
| ProductLine | String (2000) | Product line |
| ProductModel | String (2000) | Product model |
Referenced tables
ProductPriceInfo
Contains the Min and Max price data on the products
| field | Data type | description |
| ProdRefId | Long | Refers to product.puid (eg minimum price of the product) |
| SupreFId | Long | Refers 1: 1 to Supplier.id |
| Price | Double | price |
| Type | String | Nature of the prize as MIN or MAX |
| MinScale | Integer | Price from season |
| PriceSourceId | Integer | Identifier for the origin of the price and stock information (relating to price source name) |
| PriceSourceName | String | readable text for origin pricing and inventory information |
| LastUpdate | Date | Price date |
| CurrencyCode | String | Currency unit |
| CalcPrice | Double | Calculated price |
| CalcPriceBase | Double | Price based on the closing price |
ProductStock
Contains aggregated inventory data for products
| field | Data type | description |
| Puid | Long | Unique ITscope Product ID |
| AggregatedStatus | Integer | Best availability status |
| AggregatedStatusText | String | Best readable availability status |
| AggregatedStock | Integer | Total of all inventories |
| AggregatedSupplierItems | Integer | Sum of all distributors to this product |
SupplierItem
References to products
| field | Data type | description |
| id | Long | Unique key |
| ProdRefId | Long | Refers to Product.puid |
| SupreFId | Long | Refers 1: 1 to Supplier.id |
| SetRefId | Long | Points 1: 1 to Set.id |
| ManRefId | Long | Refers 1: 1 to Manufacturer.id |
| SupplierItemId | String (40) | Product number of the supplier |
| ProductName | String | Exact identifier of the article at the supplier |
| InfoText | text | Extended article description at the supplier |
| StateId | Integer | numeric code of the state of the goods |
| StateName | String | Condition of item (new, used, B-goods, refurbished etc.) |
| MatchQuality | Integer | Mapping quality of the article for ITscope product catalog |
| NewProduct | Boolean | Flag new article |
| EolProduct | Boolean | Flag Discontinued Items |
| SupplierEan | String (20) | EAN specification of the supplier |
| EanValid | Boolean | Flag, whether the EAN of the vendor is valid according to the EAN criteria |
| SupplierManufacturerSKU | String (40) | Supplier’s part number (does not have to match ITscope) |
| SupplierManufacturerName | String (80) | Manufacturer name as specified by supplier |
| RecRetailPrice | Double | UVP-indication of the supplier |
| SupplierPromo | String (40) | Name of the promotion activity of the supplier |
| vat | Integer | VAT rate for the item |
| LargeDimX | Double | Length of the product |
| LargeDimY | Double | Height of the product |
| LargeDimZ | Double | Width of the product |
| Warranty | String (40) | Supplier’s warranty |
| Deeplink | String | Link to the article at the supplier |
| SpecialOffer | Boolean | Flag whether item is special offer |
| TopSeller | Boolean | Flag if the article is a top seller |
| FlatCharge | Double | Copyright |
| CustTariffNumber | String (50) | Customs tariff number |
| SourceCountry | String (20) | Country of origin of the article |
SupplierItemPriceInfo
Contains the price data for the individual sources of supply
| field | Data type | description |
| SupItemRefId | Long | Refers to SupplierItem.id if this price is a reference source |
| SupreFId | Long | Refers 1: 1 to Supplier.id |
| Price | Double | price |
| Type | String | Type of price |
| MinScale | Integer | Price from season |
| PriceSourceId | Integer | Identifier for the origin of the price and stock information (relating to price source name) |
| PriceSourceName | String | readable text for origin pricing and inventory information |
| LastUpdate | Date | Price date |
| CurrencyCode | String (10) | Currency unit |
| CalcPrice | Double | Calculated price |
| CalcPriceBase | Double | Price based on the closing price |
SupplierItemStock
Reference sources only with storage data for products
| field | Data type | description |
| id | Long | Refers 1: 1 to SupplierItem.id |
| SupplierStockText | String | Inventory report of the supplier |
| Stock | Integer | Stock in this reference source |
| LastStockUpdate | Date | Time of last update of inventory |
| StockStatus | Integer | Identifier for state of the stock (refers to stockStatusText) |
| StockStatusText | String | Readable inventory status |
| StockSourceId | Integer | Identifier for the origin of the price and stock information (refers to stockSourceName) |
| StockSourceName | String | Readable text on the origin of the stock information (stock list, …) |
| StockUnlimited | Boolean | True = unlimited stock, false = not unlimited |
| StockAvailabilityDate | Date | The time at which the reference source is available again |
Content tables
KeyContent
Keys from content providers for Contentlink
| field | Data type | description |
| Key | Long | Key of the record, not unique |
| ContentModelRefId | Long | References n: 1 on ContentModel.id |
| ProdRefId | Long | Refers to Product.puid |
| ContentProviderRefId | Long | Refers to n: 1 on ContentProvider.id |
| Value | text | Value of the key |
| long | String | Language code in Iso639 |
MediaContent
Media (such as images) to products
| field | Data type | description |
| Key | Long | Key of the record, not unique |
| LongId | Long | Language identifier , suitable for long field, but does not reference a table |
| ContentModelRefId | Long | Refers to ContentModel.id |
| ProdRefId | Long | Refers to Product.puid |
| ContentProviderRefId | Long | Refers to n: 1 on ContentProvider.id |
| ContentCategoryRefId | Long | Refers to n: 1 on ContentCategory.id |
| ContentTagRefId | String | Comma separated list of tags, refer to ContentTag.id |
| Value | text | Value of the medium |
| long | String | Language code in Iso639 |
| MimeType | String | MimeType eg image / jpeg, text / html, … |
| Height | Long | Height of medium |
| Width | Long | Width of medium |
RatingContent
Reviews for a product (eg tests, awards, …)
| field | Data type | description |
| Key | Long | Key of the record, not unique |
| ContentModelRefId | Long | References n: 1 on ContentModel.id |
| ProdRefId | Long | Refers to Product.puid |
| ContentProviderRefId | Long | Refers to n: 1 on ContentProvider.id |
| ContentTagRefId | String | Comma separated list of tags, refer to ContentTag.id |
| Value | text | Value of rankings |
| long | String | Language code in Iso639 |
| Unit | String (20) | Unit of the ranking, eg note, points, … |
| Deeplink | String | Deeplink on the ranking platform |
RefContent
References to external content
| field | Data type | description |
| Key | Long | Key of the record, not unique |
| ContentModelRefId | Long | References n: 1 on ContentModel.id |
| ProdRefId | Long | N: 1 on Product.puid (product to which this content belongs) |
| ContentProviderRefId | Long | Refers to n: 1 on ContentProvider.id |
| CrossProdRefId | Long | N: 1 on Product.puid (Crossref products) |
| TypeId | Integer | is the unique identifier for the type field , no reference to a table |
| Type | String | Identifier for the type field |
| OriginalReference | String | External reference |
TextContent
Texts on the product, such as marketing texts, short texts, long texts
| field | Data type | description |
| Key | Long | Key of the record, not unique |
| ContentModelRefId | Long | References n: 1 on ContentModel.id |
| ProdRefId | Long | N: 1 on Product.puid (product to which this content belongs) |
| ContentProviderRefId | Long | Refers to n: 1 on ContentProvider.id |
| CrossProdRefId | Long | N: 1 on Product.puid (Crossref products) |
| TypeId | Integer | is the unique identifier for the type field , no reference to a table |
| Type | String | Identifier for the type field |
| OriginalReference | String | External reference |
Aggregated master data
ContentProvider
Provider of Content as 1WorldSync, Icecat, Manufacturer, Distributor
| field | Data type | description |
| id | Long | Unique key |
| name | String | Clear text name of the content supplier (eg Icecat, 1WorldSync, …) |
| Rank | Integer | Prioritization, for sorting, same values are possible several times |
ContentModel
Model / type of content , according to content providers
| field | Data type | description |
| id | Long | Unique key |
| name | String | Display name of the content (eg Icecat standard image large, 1WorldSync product ID, …) |
ContentCategory
ITscope categorization of content , what content is present (eg Structured feature articles, product photo, …)
| field | Data type | description |
| id | Long | Unique key |
| name | String | Identifier of category |
ContentTag
Tags for content , content description of the content, uniform across content providers across
| field | Data type | description |
| id | Long | Unique key |
| name | String | Identifier for the content type (small picture, thumbnail, marketing text, ..) |
| Rank | Integer | Prioritization, for sorting, same values are possible several times |
| Source | String | “Itscope” when ITscope’s tag was entered, “supplier” for tags automatically generated from read-in data |
FeatureModel
As supplied by the ContentProvider
| field | Data type | description |
| id | Long | Unique key |
| name | String | Name of the property |
AttributeCluster
Value ranges or characteristics of a product property
| field | Data type | description |
| id | Long | Unique key |
| SetId | Long | Refers to Set.id 1: 1, but can also be null if the cluster is assigned to a general set |
| name | String | Identifier of the expression (eg 2 GB, 15 “, 80 W – 120 W, …) |
| Min | String | Mininimal limit with respect to a property in which the expression of the product must fall in order to be assigned to this attribute cluster |
| Max | String | Maximum limit with respect to a property in which the value of the product must fall in order to be assigned to this attribute cluster |
| Rank | Long | Rank of the property |
| AttributeTypeId | Long | Identifies a property and identifies attributeTypeName, attributeTypeRank, and attributeTypeUnit. No table reference. Refer to Set.attributeTypeId (1..5) |
| AttributeTypeName | String | Name of a property. Can be the same as the name from Set.attributeTypeName (1..5) |
| AttributeTypeRank | Long | Ranking of the property, for example, can be used for sorting |
| AttributeTypeUnit | String | Unit of the property |
| AttributeTypeGroupId | String | Identifier of a property group, no table reference, but uniquely identifies attributeTypeGroupName |
| AttributeTypeGroupName | String | Name of a property group |
Alten
manufacturer
| field | Data type | description |
| id | Long | Unique key |
| name | String | Identifier of the manufacturer |
| ShortName | String | Manufacturer’s nickname |
| Deeplink | String | URL, link to the manufacturer within the ITscope platform |
Supplier
Suppliers
| field | Data type | description |
| id | Long | Unique key |
| name | String | Identifier of the manufacturer |
| Deeplink | String | URL, link to the supplier within the ITscope platform |
| partner | String | Customer number in case of partnership. Can be used as a filter to determine whether there is a partnership for this supplier |
set
Product types
| field | Data type | description |
| id | Long | Unique key |
| GrpRefId | String | Refers to SetGroup.id |
| name | String | Identifier of the product type |
| AttributeTypeId1 | Long | Unique attribute key to build a possible subcategory, 1: n points to AttributeCluster.attributeTypeId |
| AttributeTypeName1 | String | Identifier of the attribute to build up a possible subcategory |
| AttributeTypeId2 | Long | Unique attribute key to build a possible subcategory, 1: n points to AttributeCluster.attributeTypeId |
| AttributeTypeName2 | String | Identifier of the attribute to build up a possible subcategory |
| AttributeTypeId3 | Long | Unique attribute key to build a possible subcategory, 1: n points to AttributeCluster.attributeTypeId |
| AttributeTypeName3 | String | Identifier of the attribute to build up a possible subcategory |
| AttributeTypeId4 | Long | Unique attribute key to build a possible subcategory, 1: n points to AttributeCluster.attributeTypeId |
| AttributeTypeName4 | String | Identifier of the attribute to build up a possible subcategory |
| AttributeTypeId5 | Long | Unique attribute key to build a possible subcategory, 1: n points to AttributeCluster.attributeTypeId |
| AttributeTypeName5 | String | Identifier of the attribute to build up a possible subcategory |
SetGroup
Product type groups
| field | Data type | description |
| id | String | Unique key |
| name | String | Identifier of the product type group |
Cross Table
ProductXRefAttributeCluster
Associates the tables Product and AttributeCluster n: m, thus assigning each product its set of attributes of product properties
| field | Data type | description |
| ProdRefId | Long | Points 1: n to Product.puid |
| AttributeClusterRefId | Long | Points 1: n to AttributeCluster.id |
CSV PriceStoreUpdate 2 (prices2.csv)
In this format, everything from the Vollexport has been omitted, which has nothing to do with prices and inventory. The format is available in the export. API retrieval is done with “prices2.csv”.
This update format includes ProductPriceInfo, ProductStock, SupplierItemPriceInfo and SupplierItemStock, ie the price and warehouse data are similar to “CSV all data 2 (zip)”.
ProductPriceInfo
see all CSV data 2 – Product Price Info
ProductStock
see all CSV data 2 – Product Stock
SupplierItemPriceInfo
see all CSV data 2 – SupplierItemPriceInfo
SupplierItemStock
see all CSV data 2 – Supplier Items Tock
CSV Reference Source List including PriceStockUpdate (sources.csv)
In this format, everything from the Vollexport has been omitted, which does not have to do with prices, stock and reference sources. The format is available in the export. API retrieval is done with “sources.csv”.
The update format includes ProductPriceInfo, ProductStock, SupplierItemPriceInfo, SupplierItemStock, and SupplierItem as “CSV all data 2 (zip)” for each product. This update format includes product price, stock data and all reference source data including the respective price and stock data.
ProductPriceInfo
see all CSV data 2 – Product Price Info
ProductStock
see all CSV data 2 – Product Stock
SupplierItemPriceInfo
see all CSV data 2 – SupplierItemPriceInfo
SupplierItemStock
see all CSV data 2 – Supplier Items Tock
SupplierItem
see all CSV data 2 – SupplierItem