35 lines
1.5 KiB
SQL
35 lines
1.5 KiB
SQL
CREATE TABLE assets (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
inventarnummer VARCHAR(255) UNIQUE NOT NULL,
|
|
bezeichnung VARCHAR(500) NOT NULL,
|
|
kategorie_id INT,
|
|
standort_id INT,
|
|
abteilung_id INT,
|
|
raum VARCHAR(100),
|
|
lieferant VARCHAR(255),
|
|
seriennummer VARCHAR(255),
|
|
anschaffungsdatum DATE,
|
|
anschaffungspreis DECIMAL(10,2),
|
|
garantie_bis DATE,
|
|
zustand ENUM('neu', 'gut', 'befriedigend', 'schlecht', 'defekt') DEFAULT 'gut',
|
|
status ENUM('aktiv', 'inaktiv', 'ausgemustert') DEFAULT 'aktiv',
|
|
kostenstelle VARCHAR(50),
|
|
notizen TEXT,
|
|
created_by INT,
|
|
updated_by INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (kategorie_id) REFERENCES categories(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (standort_id) REFERENCES locations(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (abteilung_id) REFERENCES departments(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
|
|
INDEX idx_inventarnummer (inventarnummer),
|
|
INDEX idx_kategorie_id (kategorie_id),
|
|
INDEX idx_standort_id (standort_id),
|
|
INDEX idx_status (status),
|
|
INDEX idx_seriennummer (seriennummer),
|
|
INDEX idx_anschaffungsdatum (anschaffungsdatum),
|
|
INDEX idx_garantie_bis (garantie_bis)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|