Jak spolehlive zkontrolovat integritu dat po navrhu databaze – MySQL – Fórum – Programujte.com

Vzdycky jsem navrhoval jen velmi jednoduche databaze, kde nebyl problem zkontrolovat integritu dat pouze letmym pohledem.

Nyni jsem si vytvoril slozitejsi, kde mi to hlava trochu prestava stihat.

CREATE TABLE `Date` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `date` DATETIME NOT NULL
);

CREATE TABLE `IP` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `address` VARCHAR(200) CHARACTER SET UTF8 NOT NULL
);

CREATE TABLE `Date_IP` (
  `date` INTEGER NOT NULL,
  `ip` INTEGER NOT NULL,
  PRIMARY KEY (`date`, `ip`)
);

CREATE INDEX `idx_date_ip` ON `Date_IP` (`ip`);

ALTER TABLE `Date_IP` ADD CONSTRAINT `fk_date_ip__ip` FOREIGN KEY (`ip`) REFERENCES `IP` (`id`);

ALTER TABLE `Date_IP` ADD CONSTRAINT `fk_date_ip__date` FOREIGN KEY (`date`) REFERENCES `Date` (`id`);

CREATE TABLE `Location` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `path` VARCHAR(200) CHARACTER SET UTF8 NOT NULL
);

CREATE TABLE `Date_Location` (
  `date` INTEGER NOT NULL,
  `location` INTEGER NOT NULL,
  PRIMARY KEY (`date`, `location`)
);

CREATE INDEX `idx_date_location` ON `Date_Location` (`location`);

ALTER TABLE `Date_Location` ADD CONSTRAINT `fk_date_location__location` FOREIGN KEY (`location`) REFERENCES `Location` (`id`);

ALTER TABLE `Date_Location` ADD CONSTRAINT `fk_date_location__date` FOREIGN KEY (`date`) REFERENCES `Date` (`id`);

CREATE TABLE `IP_Location` (
  `ip` INTEGER NOT NULL,
  `location` INTEGER NOT NULL,
  PRIMARY KEY (`ip`, `location`)
);

CREATE INDEX `idx_ip_location` ON `IP_Location` (`location`);

ALTER TABLE `IP_Location` ADD CONSTRAINT `fk_ip_location__ip` FOREIGN KEY (`ip`) REFERENCES `IP` (`id`);

ALTER TABLE `IP_Location` ADD CONSTRAINT `fk_ip_location__location` FOREIGN KEY (`location`) REFERENCES `Location` (`id`);

CREATE TABLE `UserAgent` (
  `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(200) CHARACTER SET UTF8 NOT NULL
);

CREATE TABLE `Date_UserAgent` (
  `date` INTEGER NOT NULL,
  `useragent` INTEGER NOT NULL,
  PRIMARY KEY (`date`, `useragent`)
);

CREATE INDEX `idx_date_useragent` ON `Date_UserAgent` (`useragent`);

ALTER TABLE `Date_UserAgent` ADD CONSTRAINT `fk_date_useragent__useragent` FOREIGN KEY (`useragent`) REFERENCES `UserAgent` (`id`);

ALTER TABLE `Date_UserAgent` ADD CONSTRAINT `fk_date_useragent__date` FOREIGN KEY (`date`) REFERENCES `Date` (`id`);

CREATE TABLE `IP_UserAgent` (
  `ip` INTEGER NOT NULL,
  `useragent` INTEGER NOT NULL,
  PRIMARY KEY (`ip`, `useragent`)
);

CREATE INDEX `idx_ip_useragent` ON `IP_UserAgent` (`useragent`);

ALTER TABLE `IP_UserAgent` ADD CONSTRAINT `fk_ip_useragent__ip` FOREIGN KEY (`ip`) REFERENCES `IP` (`id`);

ALTER TABLE `IP_UserAgent` ADD CONSTRAINT `fk_ip_useragent__useragent` FOREIGN KEY (`useragent`) REFERENCES `UserAgent` (`id`);

CREATE TABLE `Location_UserAgent` (
  `location` INTEGER NOT NULL,
  `useragent` INTEGER NOT NULL,
  PRIMARY KEY (`location`, `useragent`)
);

CREATE INDEX `idx_location_useragent` ON `Location_UserAgent` (`useragent`);

ALTER TABLE `Location_UserAgent` ADD CONSTRAINT `fk_location_useragent__location` FOREIGN KEY (`location`) REFERENCES `Location` (`id`);

ALTER TABLE `Location_UserAgent` ADD CONSTRAINT `fk_location_useragent__useragent` FOREIGN KEY (`useragent`) REFERENCES `UserAgent` (`id`)

Jde o to, ze mam ctyri objekty.

IP adresu

User Agenta

Destinaci

Datum

vsechny tyto objekty maji mezi sebou vztah many to many

jedna IP ma vic user_agentu

jeden user_agent ma vic ip

jedno datum ma vic IP adres

jedna ip adresa ma vic datumu

jedna destinace ma mnoho user agentu

jeden user agent ma mnoho destinaci

...atd

----------------------------------------------------------------

zkratka vsechno se vsim ma vztah many to many.

Pouzivam ORM a naplnilil jsem tu databazi zkusebnimi daty.

Tech tabulek mam 10. No asi neni treba dodavat ze rucne kontrolovat integritu dat neni pro me uplne snadne a navic si nejsem jisty, jestli pri takovem poctu tabulek(10) a tech objektu (4) nevznikne nejaka kombinace, kde se mi to cele sesype.

Verim, ze se bezne navrhuji jeste o nekolik radu slozitejsi databaze a tak by me zajimalo, jak to tam testuji, aby si byli jisti ze navrh a integrita dat je v poradku?

Diky za radu.