Neuer Changes-DB-Import: Übersicht und Datenbankschema

CREATE TABLE brands (
    code                          VARCHAR(50)    NOT NULL PRIMARY KEY,
    name                          VARCHAR(255)   NULL,
    logo                          VARCHAR(512)   NULL,
    description                   VARCHAR(MAX)   NULL,
    slogan                        VARCHAR(500)   NULL,
    supplierNumber                VARCHAR(50)    NULL,
    priority                      BIGINT         NOT NULL,
    useOxomiPreviewImages         BIT            NOT NULL,
    oxomiSupplierNumber           BIT            NOT NULL,
    manufacturerName              VARCHAR(255)   NULL,
    manufacturerPostalAddress     VARCHAR(500)   NULL,
    manufacturerElectronicAddress VARCHAR(255)   NULL,
    distributorName               VARCHAR(255)   NULL,
    distributorPostalAddress      VARCHAR(500)   NULL,
    distributorElectronicAddress  VARCHAR(255)   NULL,
    changeDate                    DATETIME2      NULL
);
CREATE INDEX idx_brands_changedate ON brands(changeDate);

CREATE TABLE featuresettings (
    code                 VARCHAR(255) NOT NULL PRIMARY KEY,
    type                 VARCHAR(20)  NOT NULL,
    label                VARCHAR(255) NULL,
    priority             INT          NOT NULL,
    showInDetails        BIT          NOT NULL,
    showInList           BIT          NOT NULL,
    showAsFilter         BIT          NOT NULL,
    noRangeFilter        BIT          NOT NULL,
    useAsContent         BIT          NOT NULL,
    useAsExtendedContent BIT          NOT NULL,
    showInTooltip        BIT          NOT NULL,
    characteristic       BIT          NOT NULL,
    multiple             BIT          NOT NULL,
    changeDate           DATETIME2    NULL
);
CREATE INDEX idx_featuresettings_changedate ON featuresettings(changeDate);

CREATE TABLE catalogs (
    name        VARCHAR(255) NOT NULL PRIMARY KEY,
    visibleName VARCHAR(255) NULL,
    logo        VARCHAR(512) NULL,
    offer       BIT          NOT NULL,
    partnerRole VARCHAR(255) NULL,
    validFrom   DATE         NULL,
    validUntil  DATE         NULL,
    language    VARCHAR(2)   NULL,
    changeDate  DATETIME2    NULL
);
CREATE INDEX idx_catalogs_changedate ON catalogs(changeDate);

CREATE TABLE [groups] (
    code       VARCHAR(255) NOT NULL PRIMARY KEY,
    name       VARCHAR(255) NULL,
    image      VARCHAR(512) NULL,
    parent     VARCHAR(255) NULL,
    type       VARCHAR(30)  NOT NULL,
    brand      VARCHAR(50)  NULL,
    priority   INT          NOT NULL,
    cssClass   VARCHAR(255) NULL,
    changeDate DATETIME2    NULL
);
CREATE INDEX idx_groups_changedate ON [groups](changeDate);

CREATE TABLE grouptexts (
    [group]    VARCHAR(255)  NOT NULL,
    type       VARCHAR(50)   NOT NULL,
    text       VARCHAR(MAX)  NOT NULL,
    changeDate DATETIME2     NULL
);
CREATE INDEX idx_grouptexts_group ON grouptexts([group]);
CREATE INDEX idx_grouptexts_changedate ON grouptexts(changeDate);

CREATE TABLE quantityunits (
    code         VARCHAR(255) NOT NULL PRIMARY KEY,
    shortName    VARCHAR(255) NULL,
    unitName     VARCHAR(255) NULL,
    alias1       VARCHAR(255) NULL,
    alias2       VARCHAR(255) NULL,
    alias3       VARCHAR(255) NULL,
    alias4       VARCHAR(255) NULL,
    alias5       VARCHAR(255) NULL,
    alias6       VARCHAR(255) NULL,
    standardCode VARCHAR(255) NULL,
    erpCode      VARCHAR(255) NULL,
    changeDate   DATETIME2    NULL
);
CREATE INDEX idx_quantityunits_changedate ON quantityunits(changeDate);

CREATE TABLE variants (
    code                      VARCHAR(255)  NOT NULL PRIMARY KEY,
    name                      VARCHAR(1024) NULL,
    previewImage              VARCHAR(512)  NULL,
    featureX                  VARCHAR(128)  NULL,
    featureY                  VARCHAR(128)  NULL,
    itemPreviewIsVariantImage BIT           NOT NULL,
    changeDate                DATETIME2     NULL
);
CREATE INDEX idx_variants_changedate ON variants(changeDate);

CREATE TABLE items (
    itemNumber            VARCHAR(50)    NOT NULL PRIMARY KEY,
    shortText             VARCHAR(MAX)   NOT NULL,
    previewImage          VARCHAR(512)   NULL,
    brand                 VARCHAR(50)    NULL,
    supplierNumber        VARCHAR(50)    NULL,
    ranking               BIGINT         NOT NULL,
    quantityUnit          VARCHAR(255)   NULL,
    priceQuantity         DECIMAL(20, 3) NOT NULL,
    minOrderQuantity      DECIMAL(20, 3) NOT NULL,
    orderStep             DECIMAL(20, 3) NOT NULL,
    ean                   VARCHAR(50)    NULL,
    supplierArticleNumber VARCHAR(50)    NULL,
    userCorrelationNumber VARCHAR(50)    NULL,
    model                 VARCHAR(50)    NULL,
    variant               VARCHAR(255)   NULL,
    classificationSystem  VARCHAR(50)    NULL,
    classificationClass   VARCHAR(50)    NULL,
    priority              BIGINT         NOT NULL,
    changeDate            DATETIME2      NOT NULL,
    deleted               BIT            NOT NULL
);
CREATE INDEX idx_items_changedate ON items(changeDate);

CREATE TABLE features (
    item             VARCHAR(50)   NOT NULL,
    feature          VARCHAR(128)  NOT NULL,
    value            VARCHAR(255)  NULL,
    value2           VARCHAR(255)  NULL,
    valueDescription VARCHAR(255)  NULL,
    unit             VARCHAR(50)   NULL
);
CREATE INDEX idx_features_item ON features(item);

CREATE TABLE additionaltexts (
    item     VARCHAR(50)  NOT NULL,
    language VARCHAR(3)   NOT NULL,
    type     VARCHAR(50)  NOT NULL,
    text     VARCHAR(MAX) NOT NULL
);
CREATE INDEX idx_additionaltexts_item ON additionaltexts(item);

CREATE TABLE attachments (
    item        VARCHAR(50)   NOT NULL,
    type        VARCHAR(50)   NOT NULL,
    [file]      VARCHAR(1024) NOT NULL,
    languages   VARCHAR(60)   NULL,
    description VARCHAR(255)  NULL,
    priority    INT           NOT NULL
);
CREATE INDEX idx_attachments_item ON attachments(item);

CREATE TABLE itemrelations (
    sourceItem          VARCHAR(50)    NOT NULL,
    targetItem          VARCHAR(50)    NOT NULL,
    relationType        VARCHAR(30)    NOT NULL,
    priority            INT            NOT NULL,
    recommendedQuantity DECIMAL(20, 3) NULL,
    [group]             VARCHAR(50)    NULL
);
CREATE INDEX idx_itemrelations_source ON itemrelations(sourceItem);
CREATE INDEX idx_itemrelations_target ON itemrelations(targetItem);

CREATE TABLE catalogitemmappings (
    catalog           VARCHAR(255)   NOT NULL,
    item              VARCHAR(50)    NOT NULL,
    catalogItemNumber VARCHAR(50)    NULL,
    price             DECIMAL(20, 3) NULL,
    discountGroup     VARCHAR(50)    NULL,
    PRIMARY KEY (catalog, item)
);
CREATE INDEX idx_catalogitemmappings_item ON catalogitemmappings(item);

CREATE TABLE pricescales (
    catalog        VARCHAR(255)   NOT NULL,
    item           VARCHAR(50)    NOT NULL,
    conditionValue DECIMAL(20, 3) NOT NULL,
    price          DECIMAL(20, 3) NULL,
    PRIMARY KEY (catalog, item, conditionValue)
);
CREATE INDEX idx_pricescales_item_catalog ON pricescales(item, catalog);

CREATE TABLE groupmappings (
    item    VARCHAR(50)  NOT NULL,
    [group] VARCHAR(255) NOT NULL,
    PRIMARY KEY (item, [group])
);
CREATE INDEX idx_groupmappings_group ON groupmappings([group]);

CREATE TABLE brands (
    code                          VARCHAR(50)    NOT NULL,
    name                          VARCHAR(255)   NULL,
    logo                          VARCHAR(512)   NULL,
    description                   TEXT           NULL,
    slogan                        VARCHAR(500)   NULL,
    supplierNumber                VARCHAR(50)    NULL,
    priority                      BIGINT         NOT NULL,
    useOxomiPreviewImages         TINYINT(1)     NOT NULL,
    oxomiSupplierNumber           TINYINT(1)     NOT NULL,
    manufacturerName              VARCHAR(255)   NULL,
    manufacturerPostalAddress     VARCHAR(500)   NULL,
    manufacturerElectronicAddress VARCHAR(255)   NULL,
    distributorName               VARCHAR(255)   NULL,
    distributorPostalAddress      VARCHAR(500)   NULL,
    distributorElectronicAddress  VARCHAR(255)   NULL,
    changeDate                    DATETIME       NULL,

    PRIMARY KEY (code),
    INDEX idx_brands_changedate (changeDate)
);

CREATE TABLE featuresettings (
    code                 VARCHAR(255) NOT NULL,
    type                 VARCHAR(20)  NOT NULL,
    label                VARCHAR(255) NULL,
    priority             INT          NOT NULL,
    showInDetails        TINYINT(1)   NOT NULL,
    showInList           TINYINT(1)   NOT NULL,
    showAsFilter         TINYINT(1)   NOT NULL,
    noRangeFilter        TINYINT(1)   NOT NULL,
    useAsContent         TINYINT(1)   NOT NULL,
    useAsExtendedContent TINYINT(1)   NOT NULL,
    showInTooltip        TINYINT(1)   NOT NULL,
    characteristic       TINYINT(1)   NOT NULL,
    multiple             TINYINT(1)   NOT NULL,
    changeDate           DATETIME     NULL,

    PRIMARY KEY (code),
    INDEX idx_featuresettings_changedate (changeDate)
);

CREATE TABLE catalogs (
    name            VARCHAR(255) NOT NULL,
    visibleName     VARCHAR(255) NULL,
    logo            VARCHAR(512) NULL,
    offer           TINYINT(1)   NOT NULL,
    partnerRole     VARCHAR(255) NULL,
    validFrom       DATE         NULL,
    validUntil      DATE         NULL,
    language        VARCHAR(2)   NULL,
    changeDate      DATETIME     NULL,

    PRIMARY KEY (name),
    INDEX idx_catalogs_changedate (changeDate)
);

CREATE TABLE `groups` (
    code       VARCHAR(255) NOT NULL,
    name       VARCHAR(255) NULL,
    image      VARCHAR(512) NULL,
    parent     VARCHAR(255) NULL,
    type       VARCHAR(30)  NOT NULL,
    brand      VARCHAR(50)  NULL,
    priority   INT          NOT NULL,
    cssClass   VARCHAR(255) NULL,
    changeDate DATETIME     NULL,

    PRIMARY KEY (code),
    INDEX idx_groups_changedate (changeDate)
);

CREATE TABLE grouptexts (
    `group`    VARCHAR(255) NOT NULL,
    type       VARCHAR(50)  NOT NULL,
    text       TEXT         NOT NULL,
    changeDate DATETIME     NULL,

    INDEX idx_grouptexts_group (`group`),
    INDEX idx_grouptexts_changedate (changeDate)
);

CREATE TABLE quantityunits (
    code         VARCHAR(255) NOT NULL,
    shortName    VARCHAR(255) NULL,
    unitName     VARCHAR(255) NULL,
    alias1       VARCHAR(255) NULL,
    alias2       VARCHAR(255) NULL,
    alias3       VARCHAR(255) NULL,
    alias4       VARCHAR(255) NULL,
    alias5       VARCHAR(255) NULL,
    alias6       VARCHAR(255) NULL,
    standardCode VARCHAR(255) NULL,
    erpCode      VARCHAR(255) NULL,
    changeDate   DATETIME     NULL,

    PRIMARY KEY (code),
    INDEX idx_quantityunits_changedate (changeDate)
);

CREATE TABLE variants (
    code                       VARCHAR(255)  NOT NULL,
    name                       VARCHAR(1024) NULL,
    previewImage               VARCHAR(512)  NULL,
    featureX                   VARCHAR(128)  NULL,
    featureY                   VARCHAR(128)  NULL,
    itemPreviewIsVariantImage  TINYINT(1)    NOT NULL,
    changeDate                 DATETIME      NULL,

    PRIMARY KEY (code),
    INDEX idx_variants_changedate (changeDate)
);

CREATE TABLE items (
    itemNumber              VARCHAR(50)    NOT NULL,
    shortText               TEXT           NOT NULL,
    previewImage            VARCHAR(512)   NULL,
    brand                   VARCHAR(50)    NULL,
    supplierNumber          VARCHAR(50)    NULL,
    ranking                 BIGINT         NOT NULL,
    quantityUnit            VARCHAR(255)   NULL,
    priceQuantity           DECIMAL(20, 3) NOT NULL,
    minOrderQuantity        DECIMAL(20, 3) NOT NULL,
    orderStep               DECIMAL(20, 3) NOT NULL,
    ean                     VARCHAR(50)    NULL,
    supplierArticleNumber   VARCHAR(50)    NULL,
    userCorrelationNumber   VARCHAR(50)    NULL,
    model                   VARCHAR(50)    NULL,
    variant                 VARCHAR(255)   NULL,
    classificationSystem    VARCHAR(50)    NULL,
    classificationClass     VARCHAR(50)    NULL,
    priority                BIGINT         NOT NULL,
    changeDate              DATETIME       NOT NULL,
    deleted                 TINYINT(1)     NOT NULL,

    PRIMARY KEY (itemNumber),
    INDEX idx_items_changedate (changeDate)
);

CREATE TABLE features (
    item             VARCHAR(50)  NOT NULL,
    feature          VARCHAR(128) NOT NULL,
    value            VARCHAR(255) NULL,
    value2           VARCHAR(255) NULL,
    valueDescription VARCHAR(255) NULL,
    unit             VARCHAR(50)  NULL,

    INDEX idx_features_item (item)
);

CREATE TABLE additionaltexts (
    item     VARCHAR(50) NOT NULL,
    language VARCHAR(3)  NOT NULL,
    type     VARCHAR(50) NOT NULL,
    text     TEXT        NOT NULL,

    INDEX idx_additionaltexts_item (item)
);

CREATE TABLE attachments (
    item        VARCHAR(50)   NOT NULL,
    type        VARCHAR(50)   NOT NULL,
    file        VARCHAR(1024) NOT NULL,
    languages   VARCHAR(60)   NULL,
    description VARCHAR(255)  NULL,
    priority    INT           NOT NULL,

    INDEX idx_attachments_item (item)
);

CREATE TABLE itemrelations (
    sourceItem          VARCHAR(50)    NOT NULL,
    targetItem          VARCHAR(50)    NOT NULL,
    relationType        VARCHAR(30)    NOT NULL,
    priority            INT            NOT NULL,
    recommendedQuantity DECIMAL(20, 3) NULL,
    `group`             VARCHAR(50)    NULL,

    INDEX idx_itemrelations_source (sourceItem),
    INDEX idx_itemrelations_target (targetItem)
);

CREATE TABLE catalogitemmappings (
    catalog           VARCHAR(255)   NOT NULL,
    item              VARCHAR(50)    NOT NULL,
    catalogItemNumber VARCHAR(50)    NULL,
    price             DECIMAL(20, 3) NULL,
    discountGroup     VARCHAR(50)    NULL,

    PRIMARY KEY (catalog, item),
    INDEX idx_catalogitemmappings_item (item)
);

CREATE TABLE pricescales (
    catalog        VARCHAR(255)   NOT NULL,
    item           VARCHAR(50)    NOT NULL,
    conditionValue DECIMAL(20, 3) NOT NULL,
    price          DECIMAL(20, 3) NULL,

    PRIMARY KEY (catalog, item, conditionValue),
    INDEX idx_pricescales_item_catalog (item, catalog)
);

CREATE TABLE groupmappings (
    item      VARCHAR(50)  NOT NULL,
    `group`   VARCHAR(255) NOT NULL,

    PRIMARY KEY (item, `group`),
    INDEX idx_groupmappings_group (`group`)
);

CREATE TABLE brands (
    code                          VARCHAR(50)    PRIMARY KEY,
    name                          VARCHAR(255),
    logo                          VARCHAR(512),
    description                   TEXT,
    slogan                        VARCHAR(500),
    supplierNumber                VARCHAR(50),
    priority                      BIGINT         NOT NULL,
    useOxomiPreviewImages         BOOLEAN        NOT NULL,
    oxomiSupplierNumber           BOOLEAN        NOT NULL,
    manufacturerName              VARCHAR(255),
    manufacturerPostalAddress     VARCHAR(500),
    manufacturerElectronicAddress VARCHAR(255),
    distributorName               VARCHAR(255),
    distributorPostalAddress      VARCHAR(500),
    distributorElectronicAddress  VARCHAR(255),
    changeDate                    TIMESTAMP
);
CREATE INDEX idx_brands_changedate ON brands(changeDate);

CREATE TABLE featuresettings (
    code                 VARCHAR(255) PRIMARY KEY,
    type                 VARCHAR(20)  NOT NULL,
    label                VARCHAR(255),
    priority             INTEGER      NOT NULL,
    showInDetails        BOOLEAN      NOT NULL,
    showInList           BOOLEAN      NOT NULL,
    showAsFilter         BOOLEAN      NOT NULL,
    noRangeFilter        BOOLEAN      NOT NULL,
    useAsContent         BOOLEAN      NOT NULL,
    useAsExtendedContent BOOLEAN      NOT NULL,
    showInTooltip        BOOLEAN      NOT NULL,
    characteristic       BOOLEAN      NOT NULL,
    multiple             BOOLEAN      NOT NULL,
    changeDate           TIMESTAMP
);
CREATE INDEX idx_featuresettings_changedate ON featuresettings(changeDate);

CREATE TABLE catalogs (
    name        VARCHAR(255) PRIMARY KEY,
    visibleName VARCHAR(255),
    logo        VARCHAR(512),
    offer       BOOLEAN      NOT NULL,
    partnerRole VARCHAR(255),
    validFrom   DATE,
    validUntil  DATE,
    language    VARCHAR(2),
    changeDate  TIMESTAMP
);
CREATE INDEX idx_catalogs_changedate ON catalogs(changeDate);

CREATE TABLE "groups" (
    code       VARCHAR(255) PRIMARY KEY,
    name       VARCHAR(255),
    image      VARCHAR(512),
    parent     VARCHAR(255),
    type       VARCHAR(30)  NOT NULL,
    brand      VARCHAR(50),
    priority   INTEGER      NOT NULL,
    cssClass   VARCHAR(255),
    changeDate TIMESTAMP
);
CREATE INDEX idx_groups_changedate ON "groups"(changeDate);

CREATE TABLE grouptexts (
    "group"    VARCHAR(255) NOT NULL,
    type       VARCHAR(50)  NOT NULL,
    text       TEXT         NOT NULL,
    changeDate TIMESTAMP
);
CREATE INDEX idx_grouptexts_group ON grouptexts("group");
CREATE INDEX idx_grouptexts_changedate ON grouptexts(changeDate);

CREATE TABLE quantityunits (
    code         VARCHAR(255) PRIMARY KEY,
    shortName    VARCHAR(255),
    unitName     VARCHAR(255),
    alias1       VARCHAR(255),
    alias2       VARCHAR(255),
    alias3       VARCHAR(255),
    alias4       VARCHAR(255),
    alias5       VARCHAR(255),
    alias6       VARCHAR(255),
    standardCode VARCHAR(255),
    erpCode      VARCHAR(255),
    changeDate   TIMESTAMP
);
CREATE INDEX idx_quantityunits_changedate ON quantityunits(changeDate);

CREATE TABLE variants (
    code                      VARCHAR(255)  PRIMARY KEY,
    name                      VARCHAR(1024),
    previewImage              VARCHAR(512),
    featureX                  VARCHAR(128),
    featureY                  VARCHAR(128),
    itemPreviewIsVariantImage BOOLEAN       NOT NULL,
    changeDate                TIMESTAMP
);
CREATE INDEX idx_variants_changedate ON variants(changeDate);

CREATE TABLE items (
    itemNumber            VARCHAR(50)    PRIMARY KEY,
    shortText             TEXT           NOT NULL,
    previewImage          VARCHAR(512),
    brand                 VARCHAR(50),
    supplierNumber        VARCHAR(50),
    ranking               BIGINT         NOT NULL,
    quantityUnit          VARCHAR(255),
    priceQuantity         NUMERIC(20, 3) NOT NULL,
    minOrderQuantity      NUMERIC(20, 3) NOT NULL,
    orderStep             NUMERIC(20, 3) NOT NULL,
    ean                   VARCHAR(50),
    supplierArticleNumber VARCHAR(50),
    userCorrelationNumber VARCHAR(50),
    model                 VARCHAR(50),
    variant               VARCHAR(255),
    classificationSystem  VARCHAR(50),
    classificationClass   VARCHAR(50),
    priority              BIGINT         NOT NULL,
    changeDate            TIMESTAMP      NOT NULL,
    deleted               BOOLEAN        NOT NULL
);
CREATE INDEX idx_items_changedate ON items(changeDate);

CREATE TABLE features (
    item             VARCHAR(50)  NOT NULL,
    feature          VARCHAR(128) NOT NULL,
    value            VARCHAR(255),
    value2           VARCHAR(255),
    valueDescription VARCHAR(255),
    unit             VARCHAR(50)
);
CREATE INDEX idx_features_item ON features(item);

CREATE TABLE additionaltexts (
    item     VARCHAR(50) NOT NULL,
    language VARCHAR(3)  NOT NULL,
    type     VARCHAR(50) NOT NULL,
    text     TEXT        NOT NULL
);
CREATE INDEX idx_additionaltexts_item ON additionaltexts(item);

CREATE TABLE attachments (
    item        VARCHAR(50)   NOT NULL,
    type        VARCHAR(50)   NOT NULL,
    file        VARCHAR(1024) NOT NULL,
    languages   VARCHAR(60),
    description VARCHAR(255),
    priority    INTEGER       NOT NULL
);
CREATE INDEX idx_attachments_item ON attachments(item);

CREATE TABLE itemrelations (
    sourceItem          VARCHAR(50)    NOT NULL,
    targetItem          VARCHAR(50)    NOT NULL,
    relationType        VARCHAR(30)    NOT NULL,
    priority            INTEGER        NOT NULL,
    recommendedQuantity NUMERIC(20, 3),
    "group"             VARCHAR(50)
);
CREATE INDEX idx_itemrelations_source ON itemrelations(sourceItem);
CREATE INDEX idx_itemrelations_target ON itemrelations(targetItem);

CREATE TABLE catalogitemmappings (
    catalog           VARCHAR(255)   NOT NULL,
    item              VARCHAR(50)    NOT NULL,
    catalogItemNumber VARCHAR(50),
    price             NUMERIC(20, 3),
    discountGroup     VARCHAR(50),
    PRIMARY KEY (catalog, item)
);
CREATE INDEX idx_catalogitemmappings_item ON catalogitemmappings(item);

CREATE TABLE pricescales (
    catalog        VARCHAR(255)   NOT NULL,
    item           VARCHAR(50)    NOT NULL,
    conditionValue NUMERIC(20, 3) NOT NULL,
    price          NUMERIC(20, 3),
    PRIMARY KEY (catalog, item, conditionValue)
);
CREATE INDEX idx_pricescales_item_catalog ON pricescales(item, catalog);

CREATE TABLE groupmappings (
    item    VARCHAR(50)  NOT NULL,
    "group" VARCHAR(255) NOT NULL,
    PRIMARY KEY (item, "group")
);
CREATE INDEX idx_groupmappings_group ON groupmappings("group");