Code:
DROP TABLE IF EXISTS `rel_personenadressen`;
DROP TABLE IF EXISTS `rel_bankverbindung`;
DROP TABLE IF EXISTS `tbl_bankleitzahlen`;
DROP TABLE IF EXISTS `tbl_personen`;
DROP TABLE IF EXISTS `rel_adressen`;
DROP TABLE IF EXISTS `rel_plzorte`;
DROP TABLE IF EXISTS `tbl_orte`;
DROP TABLE IF EXISTS `tbl_kontonummern`;
DROP TABLE IF EXISTS `tbl_titel`;
DROP TABLE IF EXISTS `tbl_strassen`;
DROP TABLE IF EXISTS `tbl_postleitzahlen`;
DROP TABLE IF EXISTS `tbl_bundeslaender`;
CREATE TABLE `tbl_bundeslaender` (
`id_bundesland` int(10) unsigned NOT NULL auto_increment,
`str_bundesland` char(63) NOT NULL default '',
`str_abkuerzung` char(2) NOT NULL default '',
PRIMARY KEY (`id_bundesland`)
) TYPE=InnoDB;
CREATE TABLE `tbl_postleitzahlen` (
`id_plz` int(10) unsigned NOT NULL auto_increment,
`int_postleitzahl` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id_plz`)
) TYPE=InnoDB;
CREATE TABLE `tbl_strassen` (
`id_strasse` int(10) unsigned NOT NULL auto_increment,
`str_strassenname` char(255) NOT NULL default '',
PRIMARY KEY (`id_strasse`)
) TYPE=InnoDB;
CREATE TABLE `tbl_titel` (
`id_titel` int(10) unsigned NOT NULL auto_increment,
`str_titel` char(127) NOT NULL default '',
PRIMARY KEY (`id_titel`)
) TYPE=InnoDB;
CREATE TABLE `tbl_kontonummern` (
`id_kontonummer` int(10) unsigned NOT NULL auto_increment,
`int_kontonummer` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`id_kontonummer`)
) TYPE=InnoDB;
CREATE TABLE `tbl_orte` (
`id_ort` int(10) unsigned NOT NULL auto_increment,
`id_bundesland` int(10) unsigned NOT NULL default '0',
`str_ortsname` char(255) NOT NULL default '',
PRIMARY KEY (`id_ort`),
KEY `idx_tbl_orte_id_bundesland` ( `id_bundesland` ),
CONSTRAINT `idx_tbl_orte_id_bundesland` FOREIGN KEY ( `id_bundesland` ) REFERENCES `tbl_bundeslaender` ( `id_bundesland` ) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE `rel_plzorte` (
`id_plzort` int(10) unsigned NOT NULL auto_increment,
`id_ort` int(10) unsigned NOT NULL default '0',
`id_plz` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id_plzort`),
KEY `idx_rel_plzorte_id_plz` (`id_plz`),
KEY `idx_rel_plzorte_id_ort` (`id_ort`),
CONSTRAINT `idx_rel_plzorte_id_plz` FOREIGN KEY (`id_plz`) REFERENCES `tbl_postleitzahlen` (`id_plz`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `idx_rel_plzorte_id_ort` FOREIGN KEY (`id_ort`) REFERENCES `tbl_orte` (`id_ort`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE `rel_adressen` (
`id_adresse` int(10) unsigned NOT NULL auto_increment,
`id_plzort` int(10) unsigned NOT NULL default '0',
`id_strasse` int(10) unsigned NOT NULL default '0',
`int_hausnummer` int(10) unsigned NOT NULL default '0',
`str_hausnummernzusatz` char(31) default NULL,
PRIMARY KEY (`id_adresse`),
KEY `idx_rel_adressen_id_strasse` (`id_strasse`),
KEY `idx_rel_adressen_id_plzort` (`id_plzort`),
CONSTRAINT `idx_rel_adressen_id_strasse` FOREIGN KEY (`id_strasse`) REFERENCES `tbl_strassen` (`id_strasse`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `idx_rel_adressen_id_plzort` FOREIGN KEY (`id_plzort`) REFERENCES `rel_plzorte` (`id_plzort`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE `tbl_personen` (
`id_person` int(10) unsigned NOT NULL auto_increment,
`id_titel` int(10) unsigned default NULL,
`int_geschlecht` int(10) unsigned default NULL,
`str_vorname` char(127) default NULL,
`str_nachname` char(127) default NULL,
`int_geburtsdatum` bigint(20) unsigned default NULL,
PRIMARY KEY (`id_person`),
KEY `idx_tbl_personen_id_titel` (`id_titel`),
CONSTRAINT `idx_tbl_personen_id_titel` FOREIGN KEY (`id_titel`) REFERENCES `tbl_titel` (`id_titel`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE `tbl_bankleitzahlen` (
`id_bankleitzahl` int(10) unsigned NOT NULL auto_increment,
`id_adresse` int(10) unsigned NOT NULL default '0',
`int_bankleitzahl` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`id_bankleitzahl`),
KEY `idx_tbl_bankleitzahlen_id_adresse` (`id_adresse`),
CONSTRAINT `idx_tbl_bankleitzahlen_id_adresse` FOREIGN KEY (`id_adresse`) REFERENCES `rel_adressen` (`id_adresse`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE `rel_bankverbindungen` (
`id_bankverbindung` int(10) unsigned NOT NULL auto_increment,
`id_bankleitzahl` int(10) unsigned NOT NULL default '0',
`id_kontonummer` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id_bankverbindung`),
KEY `idx_rel_bankverbindungen_id_bankleitzahl` (`id_bankleitzahl`),
KEY `idx_rel_bankverbindungen_id_kontonummer` (`id_kontonummer`),
CONSTRAINT `idx_rel_bankverbindungen_id_bankleitzahl` FOREIGN KEY (`id_bankleitzahl`) REFERENCES `tbl_bankleitzahlen` (`id_bankleitzahl`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `idx_rel_bankverbindungen_id_kontonummer` FOREIGN KEY (`id_kontonummer`) REFERENCES `tbl_kontonummern` (`id_kontonummer`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE `rel_personenadressen` (
`id_personadresse` int(10) unsigned NOT NULL auto_increment,
`id_adresse` int(10) unsigned NOT NULL default '0',
`id_person` int(10) unsigned NOT NULL default '0',
`int_adressprioritaet` int(10) unsigned default NULL,
PRIMARY KEY (`id_personadresse`),
KEY `idx_rel_personenadressen_id_person` (`id_person`),
KEY `idx_rel_personenadressen_id_adresse` (`id_adresse`),
CONSTRAINT `idx_rel_personenadressen_id_person` FOREIGN KEY (`id_person`) REFERENCES `tbl_personen` (`id_person`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `idx_rel_personenadressen_id_adresse` FOREIGN KEY (`id_adresse`) REFERENCES `rel_adressen` (`id_adresse`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE `rel_personenbankverbindungen` (
`id_personbankverbindung` int(10) unsigned NOT NULL auto_increment,
`id_person` int(10) unsigned NOT NULL default '0',
`id_bankverbindung` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id_personbankverbindung`),
KEY `idx_rel_personenbankverbindungen_id_person` (`id_person`),
KEY `idx_rel_personenbankverbindungen_id_bankverbindung` (`id_bankverbindung`),
CONSTRAINT `idx_rel_personenbankverbindungen_id_person` FOREIGN KEY (`id_person`) REFERENCES `tbl_personen` (`id_person`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `idx_rel_personenbankverbindungen_id_bankverbindung` FOREIGN KEY (`id_bankverbindung`) REFERENCES `rel_bankverbindungen` (`id_bankverbindung`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
BEGIN;
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Schleswig-Holstein", "SH" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Freie und Hansestadt Hamburg", "HH" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Niedersachsen", "NI" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Freie Hansestadt Bremen", "HB" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Nordrhein-Westfalen", "NW" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Hessen", "HE" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Rheinland-Pfalz", "RP" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Baden-Württemberg", "BW" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Freistaat Bayern", "BY" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Saarland", "SL" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Berlin", "BE" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Brandenburg", "BB" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Mecklenburg-Vorpommern", "MV" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Freistaat Sachsen", "SN" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Sachsen-Anhalt", "ST" );
INSERT INTO `tbl_bundeslaender` ( `str_bundesland`, `str_abkuerzung` ) VALUES ( "Freistaat Thüringen", "TH" );
COMMIT;
Lesezeichen