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 (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

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 (IMG:http://forum.php.pl/style_emoticons/default/dry.gif)

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 (IMG:http://forum.php.pl/style_emoticons/default/czarodziej.gif)

Teraz musimy złączyć obie tabele:
(IMG:http://www.pjwstk.devtown.net/pliki/schemat.jpg)

[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