Redundancja + normalizacja danych w bazie mysql 5.1, tworzymy model relacyjny Opcje

[Fixer]

Witam
Szukam narzędzie, które przeanalizuje mi wybraną tabelę normalizując w niej dane.
Chodzi o usunięcie zdublowanych wpisów (redundancja) oraz o rozbicie tych powtarzających się danych na dodatkowe tabele tworząc tym samym model relacyjny

Jak na razie to tylko access ma taką opcję ale nie chcę za każdym razem przenosić stosy danych konwertując do i z ms accessa :/ może znacie jakieś tego typu lepsze narzędzia hm?

Na razie naskrobałem taki oto działający kodzik w php na szybko i jeśli możecie to zwróćcie uwagę co by można w nim zmienić/poprawić...

No dobra to jedziemy z tym...

Na początek mamy takie coś:

[SQL] pobierz, plaintext 
SELECT * FROM `kontakty`;
[SQL] pobierz, plaintext 

Kod

+--------+------------+----------+
| imie   | nazwisko   | miasto   |
+--------+------------+----------+
| imie 1 | nazwisko 1 | miasto 1 |
| imie 2 | nazwisko 2 | miasto 1 |
| imie 3 | nazwisko 3 | miasto 2 |
| imie 4 | nazwisko 4 | miasto 2 |
+--------+------------+----------+

*jak widać miasta się powtarzają, ale zaraz to naprawimy

Zrzut tabeli:

[SQL] pobierz, plaintext 
DROP TABLE IF EXISTS `kontakty_redundancja`;
CREATE TABLE IF NOT EXISTS `kontakty_redundancja` (
  `imie` varchar(20) NOT NULL,
  `nazwisko` varchar(20) NOT NULL,
  `miasto` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin2 ROW_FORMAT=DYNAMIC;
[SQL] pobierz, plaintext 

Do bazy zaimportowaliśmy kontakty z pliku *.CSV:

[SQL] pobierz, plaintext 
LOAD DATA LOCAL INFILE 'kontakty.csv' INTO TABLE `kontakty_redundancja` CHARACTER SET latin2 FIELDS TERMINATED BY ';' ENCLOSED BY '''' ESCAPED BY '' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
[SQL] pobierz, plaintext 

kontakty.csv

Kod

"id_wpisu";"id_miasta";"imie";"nazwisko"
"1";"1";"imie 1";"nazwisko 1"
"2";"1";"imie 2";"nazwisko 2"
"3";"2";"imie 3";"nazwisko 3"
"4";"2";"imie 4";"nazwisko 4"

I teraz chcemy uzyskać taką postać:

[SQL] pobierz, plaintext 
SELECT * FROM `kontakty`;
[SQL] pobierz, plaintext 

Kod

+----------+-----------+--------+------------+
| id_wpisu | id_miasta | imie   | nazwisko   |
+----------+-----------+--------+------------+
|        1 |         1 | imie 1 | nazwisko 1 |
|        2 |         1 | imie 2 | nazwisko 2 |
|        3 |         2 | imie 3 | nazwisko 3 |
|        4 |         2 | imie 4 | nazwisko 4 |
+----------+-----------+--------+------------+

*miasta się już nie powtarzają, w zamian mamy nr indeksów czyli dokładnie tak jak chcemy

Teraz musimy złączyć obie tabele:

[SQL] pobierz, plaintext 
SELECT kontakty.id_wpisu, kontakty.imie, kontakty.nazwisko, miasta.miasto FROM miasta INNER JOIN kontakty ON miasta.id_miasta = kontakty.id_miasta;
[SQL] pobierz, plaintext 

Kod

+----------+--------+------------+----------+
| id_wpisu | imie   | nazwisko   | miasto   |
+----------+--------+------------+----------+
|        1 | imie 1 | nazwisko 1 | miasto 1 |
|        2 | imie 2 | nazwisko 2 | miasto 1 |
|        3 | imie 3 | nazwisko 3 | miasto 2 |
|        4 | imie 4 | nazwisko 4 | miasto 2 |
+----------+--------+------------+----------+

A dla tych co dotrwali do końca tego posta kod źródłowy w php, który to sam robi...

Opis działania skryptu:
1. Łączymy się z bazą danych.
2. Wczytujemy dane do bazy z plku *.csv
3. Zmieniamy nazwę tabeli z: "kontakty" na: "kontakty_redundancja"
4. Tworzymy nową tabelę: "miasta" i wypełniamy ją unikalnymi nazwami miast (SELECT DISTINCT)
5. Tworzymy nową tabelę: "kontakty" wypełniając ją danymi z tabeli: "kontakty_redundancja" (tak aby nie naruszać oryginalnej tabeli)
6. Wpadamy w pętlę, która pobiera nazwę miasta z tabeli: "kontakty" i wyszukuje jej odpowiedni numer ID w tabeli: "miasta" (WHERE `miasto` = 'miasto 1';)
7. Aktualizujemy tabelę: "kontakty" zamieniając nazwy miast na ich odpowiedniki numerów ID pobrane wcześniej do zmiennej
8. Modyfikujemy nazwę i typ danych tabeli: "kontakty" w kolumnie: "miasto" na nazwę: "id_miasta" zamienijąc typ z: "VARCHAR(20)" na typ: "INT(10)".
9. Zmieniamy kolejność tabel tak aby kolumny z przedrostkiem: "id_" były na początku
10. Tworzymy relacje (FOREIGN KEY CONSTRAINT) dla MySQL 5.1
11. Rozłączenie się z bazą danych.

PS. pokusi się ktoś o zrobienie kreatora na bazie formularzy do tego?

Ten post edytował Fixer 14.11.2008, 10:57:25