OpenNIT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- Themengebiete koennen mehreren Standorten zugeordnet sein: dieselbe Anleitung
-- gilt oft fuer mehrere Kunden. Die Zuordnungstabelle ist ab hier massgeblich;
-- `topics`.`location_id` bleibt unveraendert stehen und wird nicht mehr gelesen.

CREATE TABLE IF NOT EXISTS `topic_locations` (
    `topic_id`    BIGINT UNSIGNED NOT NULL,
    `location_id` INT UNSIGNED    NOT NULL,
    PRIMARY KEY (`topic_id`, `location_id`),
    INDEX `idx_topic_locations_location` (`location_id`),
    CONSTRAINT `fk_topic_locations_topic`
        FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_topic_locations_location`
        FOREIGN KEY (`location_id`) REFERENCES `ticket_locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bestehende Einzelzuordnung uebernehmen.
INSERT IGNORE INTO `topic_locations` (`topic_id`, `location_id`)
    SELECT `id`, `location_id` FROM `topics` WHERE `location_id` IS NOT NULL;