Stand: 2026-03-05

Übergeordnet: CAFM-Architektur — Gesamtübersicht Verwandt: 2.1 Systemebenen | 2.4 Migration

asset.item ist die universelle Tabelle für Technische Plätze und Equipment. Beide Konzepte werden in derselben Tabelle geführt — der Unterschied liegt im status_code und der Verknüpfung über asset.item_installation.

CREATE TABLE asset.item (
    id              INT            NOT NULL IDENTITY PRIMARY KEY,
    legacy_id       VARCHAR(50)    NULL,        -- Original-PK aus Quellsystem
    legacy_source   VARCHAR(20)    NULL,        -- 'AMED','ENIVERS','TECDB',...
    type_code       VARCHAR(20)    NOT NULL,    -- FK → asset.item_type.code
    status_code     VARCHAR(20)    NOT NULL,    -- 'planned','active','decommissioned'
    item_number     VARCHAR(50)    NULL,        -- Anlagenkennzeichen / EMR-Stelle
    name            NVARCHAR(200)  NOT NULL,
    description     NVARCHAR(MAX)  NULL,
    branch_id       INT            NULL,        -- FK → org.branch
    location_id     INT            NULL,        -- FK → org.location
    parent_id       INT            NULL,        -- FK → asset.item.id (TP-Hierarchie)
    properties      NVARCHAR(MAX)  NULL,        -- JSON: type-spezifische Felder
    is_active       BIT            NOT NULL DEFAULT 1,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME(),
    updated_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME(),
    created_by      INT            NULL,
    updated_by      INT            NULL
)

asset.property_schema beschreibt alle Felder eines type_code. Formulare, Validierungen und Views werden daraus generiert — ohne hardcodierte Logik.

CREATE TABLE asset.property_schema (
    id              INT           NOT NULL IDENTITY PRIMARY KEY,
    type_code       VARCHAR(20)   NOT NULL,
    property_name   VARCHAR(100)  NOT NULL,
    display_name    NVARCHAR(200) NOT NULL,
    display_group   NVARCHAR(100) NULL,
    sql_type        VARCHAR(20)   NOT NULL,   -- 'nvarchar','int','decimal','bit',...
    sql_length      INT           NULL,
    sql_precision   INT           NULL,
    sql_scale       INT           NULL,
    is_lookup       BIT           NOT NULL DEFAULT 0,
    lookup_schema   VARCHAR(50)   NULL,
    lookup_table    VARCHAR(100)  NULL,
    lookup_key_col  VARCHAR(100)  NULL,
    lookup_disp_col VARCHAR(100)  NULL,
    is_required     BIT           NOT NULL DEFAULT 0,
    is_readonly     BIT           NOT NULL DEFAULT 0,
    is_unique       BIT           NOT NULL DEFAULT 0,
    default_value   NVARCHAR(200) NULL,
    min_value       NVARCHAR(50)  NULL,
    max_value       NVARCHAR(50)  NULL,
    regex_pattern   NVARCHAR(500) NULL,
    allowed_values  NVARCHAR(MAX) NULL,      -- JSON Array
    unit            NVARCHAR(50)  NULL,
    ui_widget       VARCHAR(50)   NULL,      -- 'text','select','checkbox','date',...
    is_searchable   BIT           NOT NULL DEFAULT 1,
    is_filterable   BIT           NOT NULL DEFAULT 1,
    is_sortable     BIT           NOT NULL DEFAULT 1,
    is_exportable   BIT           NOT NULL DEFAULT 1,
    is_visible      BIT           NOT NULL DEFAULT 1,
    sort_order      INT           NOT NULL DEFAULT 0,
    legacy_column   VARCHAR(100)  NULL,      -- Quellspalte im Altsystem
    is_active       BIT           NOT NULL DEFAULT 1
)

Aktueller Stand: 90 Einträge (device-Typ vollständig; motor-Typ vollständig).

Ein DDL-Trigger auf asset.property_schema generiert bei jeder Schema-Änderung automatisch eine typenspezifische View (z.B. asset.v_item_device).

CREATE OR ALTER TRIGGER asset.trg_regen_type_views
ON asset.property_schema
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON
    -- 1. Betroffene type_codes sammeln
    DECLARE @types TABLE (type_code VARCHAR(20))
    INSERT INTO @types
        SELECT DISTINCT type_code FROM inserted
        UNION
        SELECT DISTINCT type_code FROM deleted
 
    -- 2. Pro type_code: CREATE OR ALTER VIEW generieren
    DECLARE @TYPE VARCHAR(20), @SQL NVARCHAR(MAX), @cols NVARCHAR(MAX)
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT type_code FROM @types
    OPEN cur; FETCH NEXT FROM cur INTO @TYPE
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cols = N''
        SELECT @cols += N',
    ' + CASE ps.sql_type
            WHEN 'int'     THEN 'CAST(JSON_VALUE(i.properties,''$.' + ps.property_name + ''') AS int)'
            WHEN 'float'   THEN 'CAST(JSON_VALUE(i.properties,''$.' + ps.property_name + ''') AS float)'
            WHEN 'decimal' THEN 'CAST(JSON_VALUE(i.properties,''$.' + ps.property_name + ''') AS decimal('
                                 + CAST(ps.sql_precision AS VARCHAR) + ',' + CAST(ps.sql_scale AS VARCHAR) + '))'
            WHEN 'bit'     THEN 'CAST(JSON_VALUE(i.properties,''$.' + ps.property_name + ''') AS bit)'
            ELSE 'JSON_VALUE(i.properties,''$.' + ps.property_name + ''')'
        END + ' AS [' + ps.property_name + ']'
        FROM asset.property_schema ps
        WHERE ps.type_code = @TYPE AND ps.is_active = 1
        ORDER BY ps.sort_order
 
        SET @SQL = N'CREATE OR ALTER VIEW asset.v_item_' + @TYPE + N' AS
SELECT i.id, i.item_number, i.name, i.description,
    i.type_code, i.status_code, i.branch_id, i.location_id,
    i.parent_id, i.legacy_id, i.legacy_source,
    i.is_active, i.created_at, i.updated_at' + @cols + N'
FROM asset.item i WHERE i.type_code = ''' + @TYPE + N''''
 
        EXEC sp_executesql @SQL
        FETCH NEXT FROM cur INTO @TYPE
    END
    CLOSE cur; DEALLOCATE cur
END

Der Trigger feuert nur bei Schema-Änderungen (kein Laufzeit-Overhead). Generierte Views wie asset.v_item_device erhalten einen stabilen Query Plan.

asset.item.parent_id darf nur strukturelle Hierarchie ausdrücken (TP in TP). Die Frage „welches Equipment ist aktuell an diesem TP verbaut?“ braucht eine separate Relation.

CREATE TABLE asset.item_installation (
    id              INT       NOT NULL IDENTITY PRIMARY KEY,
    fl_item_id      INT       NOT NULL,   -- FK → asset.item (Technischer Platz)
    eq_item_id      INT       NOT NULL,   -- FK → asset.item (Equipment)
    installed_from  DATE      NOT NULL,
    installed_until DATE      NULL,       -- NULL = aktuell verbaut
    removed_reason  NVARCHAR(500) NULL,
    created_at      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    created_by      INT       NULL,
    CONSTRAINT UQ_installation_active
        UNIQUE (fl_item_id, installed_until)   -- nur ein aktives Equipment pro TP
)

TP (asset.item, status='planned')
  │
  ├── Equipment einbauen
  │     → item_installation (installed_from=heute, installed_until=NULL)
  │     → Equipment: status='active'
  │     → TP: status='active'
  │
  ├── Equipment tauschen
  │     → item_installation.installed_until = heute (altes EQ)
  │     → neues item_installation für neues EQ
  │
  └── TP außer Betrieb
        → TP: status='decommissioned'
        → offene item_installation wird geschlossen

SELECT
    tp.item_number  AS tp_nummer,
    tp.name         AS tp_name,
    eq.item_number  AS eq_nummer,
    eq.name         AS eq_name,
    ii.installed_from
FROM asset.item tp
JOIN asset.item_installation ii ON ii.fl_item_id = tp.id AND ii.installed_until IS NULL
JOIN asset.item eq ON eq.id = ii.eq_item_id
WHERE tp.type_code IN ('device','motor')
  AND tp.is_active = 1

Verknüpft Schaltschrankabgang (kv_number, kv_code) mit einem TP (device_id) und einem Motor (motor_id).

CREATE TABLE asset.electrical_connection (
    id          INT           NOT NULL IDENTITY PRIMARY KEY,
    outlet_id   INT           NULL,        -- FK → asset.item (Schaltschrankabgang)
    device_id   INT           NULL,        -- FK → asset.item (Gerät / TP)
    motor_id    INT           NULL,        -- FK → asset.item (Motor / Equipment)
    kv_number   NVARCHAR(50)  NULL,        -- Kraftverteiler-Nummer (aus LD)
    kv_code     NVARCHAR(50)  NULL,        -- EMR-Stelle (aus LD BESTAND)
    cabinet     NVARCHAR(100) NULL,
    switchhouse NVARCHAR(100) NULL,
    properties  NVARCHAR(MAX) NULL         -- JSON: weitere Felder
)

Diese Tabelle löst die Disziplin-Grenze zwischen Mechanik (device/motor) und Elektrik (kv_code): Beide Perspektiven auf dasselbe physische Objekt sind in einem Record sichtbar.

Ein Baustein ist eine benannte Gruppe von Unter-Eigenschaften, die in mehreren type_code-Schemata wiederverwendet werden. Beispiel: standort enthält immer Anlage, Betrieb, Gebäude, Stock, Zone — unabhängig davon, ob das Item ein device, motor oder piping ist.

-- Erweiterung der property_schema-Tabelle:
ALTER TABLE asset.property_schema ADD
    parent_code  VARCHAR(100) NULL   -- NULL = einfache Eigenschaft
                                     -- non-NULL = Unter-Eigenschaft eines Bausteins
                                     -- REFERENCES asset.property_schema(property_name + type_code)

Ein Baustein hat sql_type = 'COMPOSITE' und parent_code = NULL. Seine Unter-Eigenschaften haben parent_code = <baustein_property_name>.

-- Baustein-Definition (sql_type='COMPOSITE')
INSERT INTO asset.property_schema (type_code, property_name, display_name, sql_type, parent_code, sort_order)
VALUES ('*', 'standort', 'Standort', 'COMPOSITE', NULL, 10);
-- Unter-Eigenschaften
INSERT INTO asset.property_schema (type_code, property_name, display_name, sql_type, parent_code, sort_order)
VALUES
  ('*', 'standort.anlage',   'Anlage',         'FK_LIST', 'standort', 1),  -- list_type='ANLAGE'
  ('*', 'standort.betrieb',  'Betrieb',        'FK_LIST', 'standort', 2),  -- list_type='BETRIEB'
  ('*', 'standort.gebaeude', 'Gebäude',        'nvarchar','standort', 3),
  ('*', 'standort.stock',    'Stock / Etage',  'nvarchar','standort', 4),
  ('*', 'standort.zone',     'Zone',           'nvarchar','standort', 5);

Der Wert eines Bausteins wird als verschachteltes JSON-Objekt in asset.item.properties gespeichert:

{
  "standort": {
    "anlage":   "LDB2",
    "betrieb":  "2",
    "gebaeude": "A",
    "stock":    "EG",
    "zone":     "01"
  },
  "nennleistung": 15.5,
  "schutzart":    "IP54"
}

Der trg_regen_type_views-Trigger muss für sql_type = 'COMPOSITE' erweitert werden:

-- Für COMPOSITE: JSON_QUERY statt JSON_VALUE (gibt Objekt zurück)
WHEN 'COMPOSITE' THEN 'JSON_QUERY(i.properties, ''$.' + ps.property_name + ''')'
-- Für Unter-Eigenschaften (parent_code IS NOT NULL): gepunkteter Pfad
ELSE 'JSON_VALUE(i.properties, ''$.' + ps.parent_code + '.' + ps.property_name_short + ''')'

Alle Auswahllisten (Combo-Boxen, Enums, Lookup-Tabellen) werden in einer einzigen Tabelle geführt. Inspiriert vom WIS-Konzept (RuleKey/Rules), aber allgemeiner anwendbar.

CREATE TABLE shared.list_item (
    id          INT           NOT NULL IDENTITY PRIMARY KEY,
    parent_id   INT           NULL REFERENCES shared.list_item(id),  -- für hierarchische Listen
    list_type   VARCHAR(50)   NOT NULL,    -- z.B. 'ANLAGE', 'ARBEIT_ART', 'SIL_KLASSE'
    code        VARCHAR(20)   NOT NULL,    -- Kurzschlüssel: 'R', 'LDB2', 'SIL2'
    label       NVARCHAR(200) NOT NULL,    -- Anzeige-Text: 'Reparatur', 'LDB2 - Werk 2'
    value_type  VARCHAR(10)   NOT NULL DEFAULT 'VARCHAR',  -- INT, DATE, NUMERIC, DECIMAL, VARCHAR
    value_data  NVARCHAR(500) NULL,        -- gespeicherter Wert (als Text, via value_type konvertieren)
    sort_order  SMALLINT      NOT NULL DEFAULT 0,
    is_active   BIT           NOT NULL DEFAULT 1,
    description NVARCHAR(MAX) NULL,
    CONSTRAINT UQ_list_item_type_code UNIQUE (list_type, code)
)

value_type gibt an, in welchen Datentyp value_data beim Lesen konvertiert wird. Das code-Feld ist der maschinelle Schlüssel (für Programm-Logik); label ist der angezeigte Text (kann sprachabhängig erweitert werden).

INSERT INTO shared.list_item (list_type, code, label, sort_order) VALUES
  ('ARBEIT_ART', 'R', 'Reparatur',                        1),
  ('ARBEIT_ART', 'W', 'Wartung',                          2),
  ('ARBEIT_ART', 'S', 'Störungsbeseitigung',              3),
  ('ARBEIT_ART', 'U', 'Wiederkehrende Überprüfung',       4),
  ('ARBEIT_ART', 'B', 'Betriebliche Anforderung',         5),
  ('ARBEIT_ART', 'P', 'Projektauftrag (Rep./Investition)',6),
  ('ARBEIT_ART', 'A', 'Wiederkehrende Arbeiten',          7),
  ('ARBEIT_ART', 'N', 'Normale Arbeiten',                 8),
  ('ARBEIT_ART', 'V', 'Verbesserungsvorschlag',           9),
  ('ARBEIT_ART', 'K', 'Anlagenänderungen',               10);

Für Bäume wie Anlage → Betrieb → Betriebseinheit:

-- Ebene 1: Anlage
INSERT INTO shared.list_item (list_type, code, label) VALUES ('ANLAGE', 'LDB1', 'LDB1 - Werk 1');
INSERT INTO shared.list_item (list_type, code, label) VALUES ('ANLAGE', 'LDB2', 'LDB2 - Werk 2');
-- Ebene 2: Betrieb (parent_id → LDB2)
INSERT INTO shared.list_item (list_type, code, label, parent_id)
  VALUES ('BETRIEB', '51', 'Betrieb 51 - Kompressor', (SELECT id FROM shared.list_item WHERE list_type='ANLAGE' AND code='LDB2'));

Eigenschaften mit sql_type = 'FK_LIST' referenzieren shared.list_item über list_type:

-- In asset.property_schema:
-- sql_type='FK_LIST', lookup_table='shared.list_item', lookup_key_col='code',
-- lookup_disp_col='label', regex_pattern='ANLAGE'  ← enthält list_type

Alternativ: neues Feld list_type_ref VARCHAR(50) NULL in asset.property_schema.