admin/Kundenverwaltung
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
db-umbau
Kundenverwaltung/update/schema.sql
T
adminandClaude Opus 5 f24a7a88dd Schema: Pflichtspalten mit den frueheren impliziten Standardwerten 
Der alte Code fuegt fast ueberall Zeilen ein, ohne alle NOT-NULL-Spalten zu
setzen; der nicht strikte Modus hat dann '', 0 oder ein Nulldatum
eingesetzt. Diese Werte stehen jetzt als DEFAULT im Schema. Weggelassene
Spalten verhalten sich damit wie bisher, ungueltige Werte, zu lange Texte
und falsche Verweise lehnt der strikte Modus weiterhin ab.

Das Migrationsskript meldet Spalten, die in der Quelle fehlen und ueber den
Standardwert gefuellt werden.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
2026-09-17 10:28:44 +02:00

678 lines
30 KiB
SQL
Raw Permalink Blame History

-- Schema der Kundenverwaltung
--
-- Maßgeblich ist die Datenbank der Instanz kunden; project und work
-- stammen aus ingwa, weil sie dort fehlen, der Code sie aber braucht.
-- Einheitlich utf8mb4. Die Anwendung verbindet sich im strikten sql_mode
-- (siehe db_verbinden() in incs/db.php).
--
-- Pflichtspalten haben als DEFAULT den Wert, den der frühere nicht strikte
-- Modus beim Weglassen eingesetzt hat ('', 0, Nulldatum). Weggelassene
-- Spalten verhalten sich damit wie bisher; ungültige Werte, zu lange Texte
-- und falsche Verweise lehnt der strikte Modus trotzdem ab.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE `accesstype` (
`id` tinyint(3) NOT NULL AUTO_INCREMENT,
`typ` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `addKey` (
`accesskey` int(11) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `adresse` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`partner_id` int(11) NOT NULL DEFAULT 0,
`anrede_id` int(5) NOT NULL DEFAULT 0,
`anrede2_id` int(5) NOT NULL DEFAULT 0,
`titel_id` int(5) NOT NULL DEFAULT 0,
`titel2_id` int(5) NOT NULL DEFAULT 0,
`vname` varchar(50) NOT NULL DEFAULT '',
`vname2` varchar(50) NOT NULL DEFAULT '',
`nname` varchar(50) NOT NULL DEFAULT '',
`nname2` varchar(50) NOT NULL DEFAULT '',
`firma` varchar(50) NOT NULL DEFAULT '',
`str_id` int(11) NOT NULL DEFAULT 0,
`typ_id` int(2) NOT NULL DEFAULT 0,
`hnr` varchar(10) NOT NULL DEFAULT '',
`zusatz` varchar(100) NOT NULL DEFAULT '',
`gebdat` date NOT NULL DEFAULT '0000-00-00',
`gebdat2` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`id`),
KEY `fk_partner_titel_id` (`titel_id`),
KEY `fk_partner_anrede_id` (`anrede_id`),
KEY `fk_adr_str_id` (`str_id`),
KEY `fk_adr_typ_id` (`typ_id`),
KEY `onlyoneadresstype` (`partner_id`,`typ_id`),
CONSTRAINT `fk_adr_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_adr_str_id` FOREIGN KEY (`str_id`) REFERENCES `strasse` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_adr_typ_id` FOREIGN KEY (`typ_id`) REFERENCES `adresstyp` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_partner_anrede_id` FOREIGN KEY (`anrede_id`) REFERENCES `anrede` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_partner_titel_id` FOREIGN KEY (`titel_id`) REFERENCES `titel` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `adresstyp` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`typ` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `altlasten` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vacationdays` int(11) NOT NULL DEFAULT 0,
`hours` decimal(8,4) NOT NULL DEFAULT 0,
`ma_id` int(11) NOT NULL DEFAULT 0,
`last_generated` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`id`),
KEY `fk_altlasten_user_id` (`ma_id`),
CONSTRAINT `fk_altlasten_user_id` FOREIGN KEY (`ma_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `anrede` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`anrede` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `artikel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bezeichn` varchar(150) NOT NULL DEFAULT '',
`typ_id` int(5) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_id` int(11) NOT NULL DEFAULT 0,
`einheit_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_artikel_typ_id` (`typ_id`),
KEY `fk_art_rech_einheit_id` (`einheit_id`),
KEY `fk_artikel_user_id` (`user_id`),
CONSTRAINT `fk_art_rech_einheit_id` FOREIGN KEY (`einheit_id`) REFERENCES `einheit` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_artikel_typ_id` FOREIGN KEY (`typ_id`) REFERENCES `artikeltyp` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_artikel_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `artikel_eigensch` (
`art_id` int(11) NOT NULL DEFAULT 0,
`eig_id` int(11) NOT NULL DEFAULT 0,
`rech_id` int(11) NOT NULL DEFAULT 0,
`posnr` int(5) NOT NULL DEFAULT 0,
`artposnr` int(11) NOT NULL DEFAULT 0,
KEY `fk_art_eig_art_id` (`art_id`),
KEY `fk_art_eig_rech_id` (`rech_id`),
KEY `fk_art_eig_eig_id` (`eig_id`),
CONSTRAINT `fk_art_eig_art_id` FOREIGN KEY (`art_id`) REFERENCES `artikel` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_art_eig_eig_id` FOREIGN KEY (`eig_id`) REFERENCES `eigensch` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_art_eig_rech_id` FOREIGN KEY (`rech_id`) REFERENCES `rechnung` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `artikel_rechn` (
`art_id` int(11) NOT NULL DEFAULT 0,
`rech_id` int(11) NOT NULL DEFAULT 0,
`posnr` int(5) NOT NULL DEFAULT 0,
`rabatt` int(3) NOT NULL DEFAULT 0,
`rabatttyp` int(2) NOT NULL DEFAULT 0,
`menge` decimal(6,3) NOT NULL DEFAULT 0,
`preis` decimal(10,2) DEFAULT NULL,
`angtyp` int(1) NOT NULL DEFAULT 0,
`artmwst` int(3) NOT NULL DEFAULT 0,
`wanr` varchar(100) NOT NULL DEFAULT '',
`pagebreak` int(1) NOT NULL DEFAULT 0,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
KEY `fk_art_rech_art_id` (`art_id`),
KEY `fk_art_rech_rech_id` (`rech_id`),
CONSTRAINT `fk_art_rech_art_id` FOREIGN KEY (`art_id`) REFERENCES `artikel` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_art_rech_rech_id` FOREIGN KEY (`rech_id`) REFERENCES `rechnung` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `artikel_termin` (
`art_id` int(11) NOT NULL DEFAULT 0,
`termin_id` int(11) NOT NULL DEFAULT 0,
`menge` decimal(6,3) NOT NULL DEFAULT 0,
`rabatt` int(3) NOT NULL DEFAULT 0,
`wanr` varchar(100) NOT NULL DEFAULT '',
KEY `fk_art_term_art_id` (`art_id`),
KEY `fk_art_term_ang_id` (`termin_id`),
CONSTRAINT `fk_art_term_ang_id` FOREIGN KEY (`termin_id`) REFERENCES `termin` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_art_term_art_id` FOREIGN KEY (`art_id`) REFERENCES `artikel` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `artikelsks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rechnr` int(11) NOT NULL DEFAULT 0,
`bezeichn1` varchar(60) NOT NULL DEFAULT '',
`bezeichn2` varchar(60) NOT NULL DEFAULT '',
`menge` decimal(6,3) NOT NULL DEFAULT 0,
`beschr` text DEFAULT NULL,
`pos` tinyint(3) NOT NULL DEFAULT 0,
`rabatt` tinyint(3) NOT NULL DEFAULT 0,
`preis` decimal(10,2) DEFAULT NULL,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `fk_artikelsks_rech_id` (`rechnr`),
CONSTRAINT `fk_artikelsks_rech_id` FOREIGN KEY (`rechnr`) REFERENCES `rechnung` (`rechnr`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `artikeltyp` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`bezeichn` varchar(50) NOT NULL DEFAULT '',
`dropdown` tinyint(1) NOT NULL DEFAULT 0,
`list` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `ausgabe` (
`id` int(11) NOT NULL DEFAULT 0,
`user_id` int(11) NOT NULL DEFAULT 0,
`partner_id` int(11) NOT NULL DEFAULT 0,
`rec_kto_id` int(11) NOT NULL DEFAULT 0,
`send_kto_id` int(11) NOT NULL DEFAULT 0,
`betr` decimal(12,3) NOT NULL DEFAULT 0,
`rabatt` decimal(4,1) NOT NULL DEFAULT 0,
KEY `fk_ausg_user_id` (`user_id`),
KEY `fk_ausg_reckto_id` (`rec_kto_id`),
KEY `fk_ausg_sendkto_id` (`send_kto_id`),
KEY `fk_ausg_partner_id` (`partner_id`),
CONSTRAINT `fk_ausg_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_ausg_reckto_id` FOREIGN KEY (`rec_kto_id`) REFERENCES `konto` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_ausg_sendkto_id` FOREIGN KEY (`send_kto_id`) REFERENCES `konto` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_ausg_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `bank` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`blz` varchar(30) NOT NULL DEFAULT '',
`bank` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `buchungsKontos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`kto` int(11) NOT NULL DEFAULT 0,
`name` varchar(100) NOT NULL DEFAULT '',
`descr` text NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `eigensch` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bezeichn` varchar(100) NOT NULL DEFAULT '',
`wert` varchar(50) NOT NULL DEFAULT '',
`einheit_id` int(11) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_eigensch_user_id` (`user_id`),
KEY `fk_eigensch_einheit_id` (`einheit_id`),
CONSTRAINT `fk_eigensch_einheit_id` FOREIGN KEY (`einheit_id`) REFERENCES `einheit` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_eigensch_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `einheit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`einheit` varchar(10) NOT NULL DEFAULT '',
`si` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `kontakt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`partner_id` int(11) NOT NULL DEFAULT 0,
`typ_id` int(3) NOT NULL DEFAULT 0,
`kontakt` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fk_kontakt_typ_id` (`typ_id`),
KEY `fk_kontakt_partner_id` (`partner_id`),
CONSTRAINT `fk_kontakt_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_kontakt_typ_id` FOREIGN KEY (`typ_id`) REFERENCES `kontakttyp` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `kontakttyp` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`typ` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `konto` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bank_id` int(5) NOT NULL DEFAULT 0,
`partner_id` int(11) NOT NULL DEFAULT 0,
`inhaber` varchar(50) NOT NULL DEFAULT '',
`iban` varchar(30) NOT NULL DEFAULT '',
`swift` varchar(15) NOT NULL DEFAULT '',
`ktonr` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fk_kto_partner_id` (`partner_id`),
KEY `fk_kto_bank_id` (`bank_id`),
CONSTRAINT `fk_kto_bank_id` FOREIGN KEY (`bank_id`) REFERENCES `bank` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_kto_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `land` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`land_short` varchar(5) NOT NULL DEFAULT '',
`land` varchar(30) NOT NULL DEFAULT '',
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`typ_id` tinyint(3) NOT NULL DEFAULT 0,
`user_id` int(11) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`qry` text NOT NULL DEFAULT '',
`undo_qry` text DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_log_accesstype_id` (`typ_id`),
KEY `fk_log_user_id` (`user_id`),
CONSTRAINT `fk_log_accesstype_id` FOREIGN KEY (`typ_id`) REFERENCES `accesstype` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_log_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `message` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`send_id` int(11) NOT NULL DEFAULT 0,
`rec_id` int(11) NOT NULL DEFAULT 0,
`head` varchar(50) DEFAULT NULL,
`msg` text NOT NULL DEFAULT '',
`rd` tinyint(1) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `fk_msg_senduser_id` (`send_id`),
KEY `fk_msg_recuser_id` (`rec_id`),
CONSTRAINT `fk_msg_recuser_id` FOREIGN KEY (`rec_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_msg_senduser_id` FOREIGN KEY (`send_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `mwst` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`mwst` decimal(4,1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `mykonto` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bank_id` int(5) NOT NULL DEFAULT 0,
`inhaber` varchar(50) NOT NULL DEFAULT '',
`iban` varchar(30) NOT NULL DEFAULT '',
`swift` varchar(15) NOT NULL DEFAULT '',
`ktonr` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fk_mykto_bank_id` (`bank_id`),
CONSTRAINT `fk_mykto_bank_id` FOREIGN KEY (`bank_id`) REFERENCES `bank` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `notiz` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`partner_id` int(11) NOT NULL DEFAULT 0,
`notiz` text NOT NULL DEFAULT '',
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`color` varchar(6) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fk_notizen_partner_id` (`partner_id`),
CONSTRAINT `fk_notizen_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `ort` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ort` varchar(30) NOT NULL DEFAULT '',
`ortsteil` varchar(30) NOT NULL DEFAULT '',
`plz` varchar(15) NOT NULL DEFAULT '',
`vwahl` varchar(15) NOT NULL DEFAULT '',
`land_id` int(5) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_ort_land_id` (`land_id`),
CONSTRAINT `fk_ort_land_id` FOREIGN KEY (`land_id`) REFERENCES `land` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `partner` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime_new` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`datetime_lastchange` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_id` int(11) NOT NULL DEFAULT 0,
`typ_id` int(2) NOT NULL DEFAULT 0,
`zahlziel_id` int(3) NOT NULL DEFAULT 0,
`rabatt` int(3) NOT NULL DEFAULT 0,
`sks_not_checked` int(1) NOT NULL DEFAULT 0,
`sks_kdnotiz` text NOT NULL DEFAULT '',
`updated_by` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_partner_typ_id` (`typ_id`),
KEY `fk_partner_zahlziel_id` (`zahlziel_id`),
KEY `fk_partner_user_id` (`user_id`),
CONSTRAINT `fk_partner_typ_id` FOREIGN KEY (`typ_id`) REFERENCES `partnertyp` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_partner_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_partner_zahlziel_id` FOREIGN KEY (`zahlziel_id`) REFERENCES `zahlziel` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `partnertyp` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`typ` varchar(15) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `passkeys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lastAuth` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`name` varchar(40) NOT NULL DEFAULT '',
`userId` int(11) NOT NULL DEFAULT 0,
`credentialId` varchar(1023) NOT NULL DEFAULT '',
`credentialPublicKey` text NOT NULL DEFAULT '',
`signatureCounter` text NOT NULL DEFAULT '',
`authKey` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `project` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(150) NOT NULL DEFAULT '',
`name2` varchar(150) NOT NULL DEFAULT '',
`partner_id` int(11) NOT NULL DEFAULT 0,
`changetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`changeuser` int(11) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`intro` text NOT NULL DEFAULT '',
`outro` text NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fek_proj_partner_id` (`partner_id`),
CONSTRAINT `fek_proj_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `rechnr` (
`rechnr` int(11) NOT NULL DEFAULT 0,
`typ_id` int(5) NOT NULL DEFAULT 0,
PRIMARY KEY (`rechnr`,`typ_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `rechnung` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`adressfields` varchar(12) NOT NULL DEFAULT '',
`anrede1` varchar(150) NOT NULL DEFAULT '',
`anrede2` varchar(150) NOT NULL DEFAULT '',
`partner_id` int(11) NOT NULL DEFAULT 0,
`mwst_id` int(3) NOT NULL DEFAULT 0,
`user_id` int(11) NOT NULL DEFAULT 0,
`typ_id` int(2) NOT NULL DEFAULT 0,
`zahlziel_id` int(3) NOT NULL DEFAULT 0,
`adresse_id` int(11) NOT NULL DEFAULT 0,
`wahrung_id` tinyint(3) NOT NULL DEFAULT 0,
`changetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`changeuser` int(11) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`rechnr` int(11) NOT NULL DEFAULT 0,
`rabatt` int(3) NOT NULL DEFAULT 0,
`rabattdesc` varchar(100) NOT NULL DEFAULT '',
`intro` text NOT NULL DEFAULT '',
`outro` text NOT NULL DEFAULT '',
`sks` tinyint(1) NOT NULL DEFAULT 0,
`sub_mwst` int(1) NOT NULL DEFAULT 0,
`kommission` int(11) NOT NULL DEFAULT 0,
`lieferdatum` varchar(100) NOT NULL DEFAULT '',
`adresserweiterung` varchar(100) NOT NULL DEFAULT '',
`storno` int(1) NOT NULL DEFAULT 0,
`stornodate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`buchungsKto_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fek_rech_partner_id` (`partner_id`),
KEY `fek_rech_adresse_id` (`adresse_id`),
KEY `fek_rech_mwst_id` (`mwst_id`),
KEY `fek_rech_user_id` (`user_id`),
KEY `fek_rech_zahlziehl_id` (`zahlziel_id`),
KEY `fek_rech_typ_id` (`typ_id`),
KEY `fek_rech_wahrung_id` (`wahrung_id`),
KEY `rechnr` (`rechnr`),
KEY `fk_rech_buchungsKto_id` (`buchungsKto_id`),
CONSTRAINT `fek_rech_adresse_id` FOREIGN KEY (`adresse_id`) REFERENCES `adresse` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fek_rech_mwst_id` FOREIGN KEY (`mwst_id`) REFERENCES `mwst` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fek_rech_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fek_rech_typ_id` FOREIGN KEY (`typ_id`) REFERENCES `rechtyp` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fek_rech_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fek_rech_wahrung_id` FOREIGN KEY (`wahrung_id`) REFERENCES `wahrung` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fek_rech_zahlziehl_id` FOREIGN KEY (`zahlziel_id`) REFERENCES `zahlziel` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rech_buchungsKto_id` FOREIGN KEY (`buchungsKto_id`) REFERENCES `buchungsKontos` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `rechtyp` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`typ` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `setting` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT 0,
`opt` varchar(10) NOT NULL DEFAULT '',
`val` text NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fk_settings_user_id` (`user_id`),
CONSTRAINT `fk_settings_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `strasse` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`str` varchar(50) NOT NULL DEFAULT '',
`ort_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_str_ort_id` (`ort_id`),
CONSTRAINT `fk_str_ort_id` FOREIGN KEY (`ort_id`) REFERENCES `ort` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `tel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ort_id` int(11) NOT NULL DEFAULT 0,
`partner_id` int(11) NOT NULL DEFAULT 0,
`typ_id` int(3) NOT NULL DEFAULT 0,
`nr` varchar(20) NOT NULL DEFAULT '',
`name` varchar(30) NOT NULL DEFAULT '',
`adresse_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_tel_ort_id` (`ort_id`),
KEY `fk_tel_typ_id` (`typ_id`),
KEY `fk_tel_partner_id` (`partner_id`),
CONSTRAINT `fk_tel_ort_id` FOREIGN KEY (`ort_id`) REFERENCES `ort` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tel_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tel_typ_id` FOREIGN KEY (`typ_id`) REFERENCES `teltyp` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `teltyp` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`typ` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `termin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`typ` int(2) NOT NULL DEFAULT 0,
`adresse_id` int(11) NOT NULL DEFAULT 0,
`text` text NOT NULL DEFAULT '',
`changetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`done` tinyint(1) NOT NULL DEFAULT 0,
`user` int(11) NOT NULL DEFAULT 0,
`changeuser` int(11) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`termtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`termdauer` time NOT NULL DEFAULT '00:00:00',
`followed_by` int(11) NOT NULL DEFAULT 0,
`act_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`head` varchar(100) NOT NULL DEFAULT '',
`main_id` int(11) NOT NULL DEFAULT 0,
`user2` int(11) NOT NULL DEFAULT 0,
`user3` int(11) NOT NULL DEFAULT 0,
`user4` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_termin_typ_termtyp_id` (`typ`),
CONSTRAINT `fk_termin_typ_termtyp_id` FOREIGN KEY (`typ`) REFERENCES `termtyp` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `termtyp` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`typ` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `titel` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`titel` varchar(50) NOT NULL DEFAULT '',
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `tmp_artikel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bezeichn` varchar(150) NOT NULL DEFAULT '',
`typ_id` int(5) NOT NULL DEFAULT 0,
`einheit_id` int(11) NOT NULL DEFAULT 0,
`preis` decimal(10,2) DEFAULT NULL,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`posnr` int(5) NOT NULL DEFAULT 0,
`rabatt` int(3) NOT NULL DEFAULT 0,
`rabatttyp` int(2) NOT NULL DEFAULT 0,
`menge` decimal(6,3) NOT NULL DEFAULT 0,
`angtyp` int(1) NOT NULL DEFAULT 0,
`mwst_id` int(3) NOT NULL DEFAULT 0,
`rech_id` int(11) NOT NULL DEFAULT 0,
`wanr` varchar(100) NOT NULL DEFAULT '',
`pagebreak` int(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_tmp_artikel_typ_id` (`typ_id`),
KEY `fk_tmp_art_mwst_id` (`mwst_id`),
KEY `fk_tmp_art_rech_id` (`rech_id`),
KEY `fk_tmp_artikel_einheit_id` (`einheit_id`),
CONSTRAINT `fk_tmp_art_mwst_id` FOREIGN KEY (`mwst_id`) REFERENCES `mwst` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_art_rech_id` FOREIGN KEY (`rech_id`) REFERENCES `tmp_rechnung` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_artikel_einheit_id` FOREIGN KEY (`einheit_id`) REFERENCES `einheit` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_artikel_typ_id` FOREIGN KEY (`typ_id`) REFERENCES `artikeltyp` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `tmp_eigensch` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bezeichn` varchar(100) NOT NULL DEFAULT '',
`wert` varchar(50) NOT NULL DEFAULT '',
`einheit_id` int(11) NOT NULL DEFAULT 0,
`art_id` int(11) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`posnr` int(5) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_tmp_eigensch_art_id` (`art_id`),
KEY `fk_tmp_eigensch_einheit_id` (`einheit_id`),
CONSTRAINT `fk_tmp_eigensch_art_id` FOREIGN KEY (`art_id`) REFERENCES `tmp_artikel` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_eigensch_einheit_id` FOREIGN KEY (`einheit_id`) REFERENCES `einheit` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `tmp_rechnung` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`adressfields` varchar(12) NOT NULL DEFAULT '',
`partner_id` int(11) NOT NULL DEFAULT 0,
`anrede1` varchar(150) NOT NULL DEFAULT '',
`anrede2` varchar(150) NOT NULL DEFAULT '',
`mwst_id` int(3) NOT NULL DEFAULT 0,
`user_id` int(11) NOT NULL DEFAULT 0,
`rechnr` int(11) NOT NULL DEFAULT 0,
`typ_id` int(2) NOT NULL DEFAULT 0,
`zahlziel_id` int(3) NOT NULL DEFAULT 0,
`adresse_id` int(11) NOT NULL DEFAULT 0,
`wahrung_id` tinyint(3) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`rabatt` int(3) NOT NULL DEFAULT 0,
`rabattdesc` varchar(100) NOT NULL DEFAULT '',
`intro` text NOT NULL DEFAULT '',
`outro` text NOT NULL DEFAULT '',
`sub_mwst` int(1) NOT NULL DEFAULT 0,
`kommission` int(11) NOT NULL DEFAULT 0,
`lieferdatum` varchar(100) NOT NULL DEFAULT '',
`adresserweiterung` varchar(100) NOT NULL DEFAULT '',
`storno` int(1) NOT NULL DEFAULT 0,
`buchungsKto_id` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_tmp_rech_partner_id` (`partner_id`),
KEY `fk_tmp_rech_adresse_id` (`adresse_id`),
KEY `fk_tmp_rech_mwst_id` (`mwst_id`),
KEY `fk_tmp_rech_user_id` (`user_id`),
KEY `fk_tmp_rech_zahlziehl_id` (`zahlziel_id`),
KEY `fk_tmp_rech_typ_id` (`typ_id`),
KEY `fk_tmp_rech_wahrung_id` (`wahrung_id`),
KEY `fek_rech_buchungsKto_id` (`buchungsKto_id`),
CONSTRAINT `fek_rech_buchungsKto_id` FOREIGN KEY (`buchungsKto_id`) REFERENCES `buchungsKontos` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_rech_adresse_id` FOREIGN KEY (`adresse_id`) REFERENCES `adresse` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_rech_mwst_id` FOREIGN KEY (`mwst_id`) REFERENCES `mwst` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_rech_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_rech_typ_id` FOREIGN KEY (`typ_id`) REFERENCES `rechtyp` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_rech_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_rech_wahrung_id` FOREIGN KEY (`wahrung_id`) REFERENCES `wahrung` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_tmp_rech_zahlziehl_id` FOREIGN KEY (`zahlziel_id`) REFERENCES `zahlziel` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vname` varchar(50) NOT NULL DEFAULT '',
`nname` varchar(50) NOT NULL DEFAULT '',
`nick` varchar(50) NOT NULL DEFAULT '',
`pass` varchar(150) NOT NULL DEFAULT '',
`mail` varchar(50) NOT NULL DEFAULT '',
`salt` varchar(50) NOT NULL DEFAULT '',
`tel_priv` varchar(30) NOT NULL DEFAULT '',
`tel_gesch` varchar(30) NOT NULL DEFAULT '',
`tel_mobil` varchar(30) NOT NULL DEFAULT '',
`lastlogin` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`numlogins` int(11) NOT NULL DEFAULT 0,
`logedout` tinyint(2) NOT NULL DEFAULT 0,
`wrongpw` tinyint(3) NOT NULL DEFAULT 0,
`notizen` text NOT NULL DEFAULT '',
`blocked` tinyint(1) NOT NULL DEFAULT 0,
`rights` varchar(6) NOT NULL DEFAULT '',
`weekly_hours` int(11) NOT NULL DEFAULT 0,
`rfid` varchar(10) NOT NULL DEFAULT '',
`color` varchar(8) NOT NULL DEFAULT '',
`weekhours` decimal(6,3) NOT NULL DEFAULT 0,
`vacationdays` int(4) NOT NULL DEFAULT 0,
`sahours` decimal(3,1) NOT NULL DEFAULT 0,
`timelog_start` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`id`),
UNIQUE KEY `nick` (`nick`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wahrung` (
`id` tinyint(3) NOT NULL AUTO_INCREMENT,
`wahrung` varchar(20) NOT NULL DEFAULT '',
`kurs` decimal(8,4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `work` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bezeichn` varchar(150) NOT NULL DEFAULT '',
`bezeichn2` varchar(150) NOT NULL DEFAULT '',
`project_id` int(11) NOT NULL DEFAULT 0,
`changetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`changeuser` int(11) NOT NULL DEFAULT 0,
`datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`std` decimal(6,3) NOT NULL DEFAULT 0,
`rechn` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fek_work_partner_id` (`project_id`),
CONSTRAINT `fek_work_partner_id` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `zahlziel` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`ziel` varchar(150) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `zeiten` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ma_id` int(11) NOT NULL DEFAULT 0,
`kommen` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`gehen` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pause_mins` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `fk_zeiten_user_id` (`ma_id`),
CONSTRAINT `fk_zeiten_user_id` FOREIGN KEY (`ma_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;
Reference in New Issue View Git Blame Copy Permalink