[MySQL]Kilka pytań odnośnie struktur tabel,relacji między tabelami Opcje

[Ulysess]

witam
Prowadzę pewną grę via www, pisząc ją uczyłem się php,mysql
aktualnie jestem w trakcie wprowadzania pewnych zmian w grze co wiąże się ze zmianą struktur tabel no i przy okazji wolał bym już zrobić wszystko aby było bardziej prawidłowo napisane ale nie jestem w stanie sam stwierdzić co wypada poprawić/ zmienić dlatego proszę userów tych bardziej zorientowanych w mysql o pomoc

1) Przy rejestracji dane użytkownika są dodawane do 2 tabel
konto - tutaj trzymam podstawowe dane typu login haslo email itp
postacie - nazwa postaci i inne podstawowe dane
oraz id postaci jest dodawane do kilku tabel, w momencie gry dane są w tych tabelach aktulizowane

na chwile obecną chcę wprowadzić możliwość posiadania kilku postaci na jednym koncie dlatego widzę to w taki sposób:
Przy rejestracji dodawane są dane tylko do tabeli konto + ewentualnie jakieś innej gdzie też są informacje o koncie
po zalogowaniu gracz może się wcielić lub utworzyć nową postać (o ile nie ma maksymalnej ilości postaci) i dopiero przy ewentualnym stworzeniu postaci dodawane są dane do tabeli postacie + pozostałych innych tabel związanych z postacią czyli około dodatkowych 6.
Oczywiście do tabeli postacie powinienem dodać pole id konta aby było wiadomo pod które konto jest podpięta postać.

czemu opisałem dosyć szczegółowo jak to wygląda ? ponieważ liczę się z tym że można bardziej wydajnie to zrobić.

2) w każdej tabeli o postaci/koncie mam pole
ID - primery,auto_increment
id konta,postaci - index (oraz z nie wiadomych mi przyczyn unique)
tutaj zastanawiam się po co trzymać 2 takie same pola bo na chwile obecną id = id konto,postac

3) Index jak zweryfikować czy warto na dane pole zakładać index (oprócz na id konta/postaci bo to rzecz oczywista)

4) typ tabeli to MyISAM , może warto przejść na INNODB oraz pozakładać klucze obce na pola w tabelach id konta,postaci (IMG:style_emoticons/default/questionmark.gif)

5) jaka jest różnica w jeżeli dla loginu bym utworzył oddzielny index a dla e-mail oddzielny od tego widocznym na zdjęciu.

(IMG:http://img804.imageshack.us/img804/9605/capture12262011173447.th.jpg)

Uploaded with ImageShack.us

z góry dziękuje za pomoc i proszę o wyrozumiałość (IMG:style_emoticons/default/smile.gif)

[Daimos]

1) zabłądziłem czytając (IMG:style_emoticons/default/smile.gif)
2) Tzn. w tabeli z użytkownikami i postaciami masz pole auto_increment?
Jeżeli masz unikalny identyfikator w tabeli użytkownika, to w tabeli postaci możesz go pominąć i zostawić tylko obcy klucz, bo i tak łączysz te pola za pomocą jednego id prawda?!
3) "Index jak zweryfikować czy warto na dane pole zakładać index (oprócz na id konta/postaci bo to rzecz oczywista)"
Wszędzie gdzie przeszukujesz tabelę w poszukiwaniu jakiegoś pola - tzn że warto tam umieścić indeks. To tak w skrócie, ogólnie poczytaj sobie o indeksach w bazie
4) W tabelach MyISAM nie zrobisz relacji, za to szybsze są wyszukiwania z wykorzystaniem like... ogólnie też poszukaj o różnicach w tych typach, jest bardzo dużo informacji na ten temat
5) tutaj odpowiedź taka jak w 3)

[mortus]

KONTA:
id AUTO_INCREMENT PRIMARY KEY,
login UNIQUE KEY,
email UNIQUE KEY

POSTACIE:
id AUTO_INCREMENT PRIMARY KEY,
id_konta FOREIGN KEY (nie może być UNIQUE, jeśli użytkownik może posiadać kilka postaci)

W każdej innej tabeli powiązanej z postacią korzystasz z wartości kolumny id z tabeli POSTACIE.

Wszystkie pola po których wyszukujesz powinny mieć założony index, jeżeli tylko nic nie stoi na przeszkodzie.

Jeśli chodzi o pytanie 5, to utworzenie wspólnego indeksu UNIQUE dla kolumn login i email powoduje, że wartości w obu tych kolumnach jednocześnie muszą być unikalne. Oznacza to, że będziesz mógł dodać dwóch użytkowników o takim samym loginie, ale innym adresie e-mail, jak i dwóch użytkowników o takim samym adresie e-mail, ale innym loginie. Oczywiście jest to przykład nieprawidłowego zastosowania indeksu UNIQUE i powinieneś to zmienić. Nałożenie osobnego indeksu UNIQUE na kolumnę login i osobnego indeksu UNIQUE na kolumnę email spowoduje, że wartości w obu tych kolumnach niezależnie będą musiały być unikalne i tak powinno być.

[Ulysess]

jeżeli chodzi o moje pyt 1 na przykładzie wygląda tak
Przy rejestracji w tabeli A jest dodawany rekord następnie dzięki mysql_insert_id(); mam ID konta i w tabelach B C D E F G tworzone są rekordy z id_user = mysql_insert_id();
minusy tego rozwiązania (z mojego punktu widzenia)
1. istnieje ryzyko że przy rejestracji padnie serwer i zostaną rekordy dodane tylko do części tabel
2. na chwile obecną zastanawiam się jaki jest sens w każdej tabeli (oprócz tabeli konto) trzymania pola ID -primery key auto,auto_increment skoro pole ID ma te same wartości w tych tabelach co pole ID_user.
oczywiście w przypadku pozostania przy myisam.

jeżeli chodzi o pytanie 5 to zrobiłem screena złego przykładu (ale przy okazji dostałem odpowiedz za która bardzo dziękuje (IMG:style_emoticons/default/smile.gif) )

chodziło mi o sytuacje z poniższego zdjęcia:

(IMG:http://img715.imageshack.us/img715/6298/capture12262011204230.th.jpg)

Uploaded with ImageShack.us

rozumiem że to rozwiązanie jest prawidłowe w przypadku jeśli w 1 zapytaniu w warunku WHERE korzystam z tych wszystkich pól ?

[mortus]

1. Dlatego wymyślono transakcje, które służą do zapobiegania takim sytuacjom.
2. Chyba źle myślisz. Kolumna id w każdej tabeli jest unikalnym identyfikatorem tego jednego wiersza w tej właśnie tabeli. Nie powinna być kluczem obcym, chyba że realizuje relację jeden do jednego, ale wtedy nie powinna być AUTO_INCREMENT. Przykład:
Mamy grę, w której jesteśmy Panami jednej lub kilku osad. Nasze dane (login i hasło) przechowywane są w tabeli UŻYTKOWNICY, natomiast dane o naszych osadach w tabeli OSADY. Kolumna id AUTO_INCREMENT PRIMARY KEY w tabeli UŻYTKOWNICY to unikalny identyfikator użytkownika, kolumna id AUTO_INCREMENT PRIMARY KEY w tabeli OSADY, to unikalny identyfikator osady. Dodatkowo w tabeli OSADY powinna znaleźć się kolumna przeznaczona na identyfikator użytkownika. Teraz rodzi się pytanie, czy id użytkownika i id osady, to takie same wartości. Może we wczesnych etapach gry tak, ale w sytuacji, gdy ktoś może i zakłada kolejną osadę, już nie. A co w sytuacji, gdy np. planujemy się przenieść i opuszczamy jedną ze swoich osad, przecież wtedy zmieniamy identyfikator osady.
Podobnie jest z Twoimi postaciami, jeśli jeden użytkownik może mieć kilka postaci, to wartości kolumn id w tabelach użytkowników i postaci nie będą się zgadzać i postacie muszą być powiązane z użytkownikiem inną wartością, która z kolei jest kluczem obcym (powiązaniem z tabelą użytkowników).
Ogólnie tylko niektóre sytuacje "usprawiedliwiają" brak klucza głównego w tabeli i wszystko zależy od całego systemu.
5. Tak to rozwiązanie jest prawidłowe, a czasem nawet wskazane.

[Ulysess]

operując na Twoim przykładzie czyli
Tabela Uzytkownicy ->
id_konta
login
haslo
email
sq
itd

Tabela Osady ->
id_osady (primery key + auto nr)
id_konta
itd

jaki jest sens aby w kolejnych tabelach gdzie są przetrzymywane dodatkowe informacje o osadach trzymanie pola primery ket z auto numerowaniem ?
aktualnie właśnie w takich dodatkowych tabelach trzymam wszędzie pole ID które do niczego nie wykorzystuje

[mortus]

Jeśli masz kilka tabel, w których przechowujesz informacje o osadzie, to pole AUTO_INCREMENT musi się znaleźć w tabeli "głównej", natomiast w pozostałych tabelach nie jest potrzebne i możesz posługiwać się identyfikatorem konkretnej osady. Inny przykład:
UŻYTKOWNICY:
id AUTO_INCREMENT PRIMARY KEY,
login UNIQUE KEY,
password,
email UNIQUE KEY

DANE ADRESOWE:
id_uzytkownika FOREIGN KEY powiązane z UŻYTKOWNICY(id)
ulica,
miejscowosc
W takiej sytuacji nie ma potrzeby dodawania kolumny id AUTO_INCREMENT PRIMARY KEY do tabeli DANE_ADRESOWE.
Wspominałem już o tym, bo taka struktura tabel służy do realizacji relacji jeden do jednego (patrz kilka postów wyżej).

Ten post edytował mortus 27.12.2011, 21:03:30

[Ulysess]

w tygodniu dysponuje dosyć małą ilością wolnego czasu stąd tak wolno odpowiadam

nie wiem czy dobrze to zrobiłem ale dla testu stworzyłem sobie 4 tabele
Tabela A - tutaj dane o koncie typu login ,haslo
Tabela B - tutaj w momencie założenia postaci będą jej podstawowe dane typu ID , id konta pod które zostało podpięte oraz kilka dodatkowych danych
Tabela BA oraz BB - dodatkowe dane o postaci

->

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `a` (
  `id_konto` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` char(20) collate utf8_polish_ci NOT NULL,
  `haslo` char(20) collate utf8_polish_ci NOT NULL,
  PRIMARY KEY  (`id_konto`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ;
 
CREATE TABLE IF NOT EXISTS `b` (
  `id_postaci` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_konto` mediumint(8) UNSIGNED NOT NULL,
  `dane_a` char(20) collate utf8_polish_ci NOT NULL,
  `dane_b` char(20) collate utf8_polish_ci NOT NULL,
  `dane_c` char(20) collate utf8_polish_ci NOT NULL,
  `dane_d` char(20) collate utf8_polish_ci NOT NULL,
  PRIMARY KEY  (`id_postaci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ;
 
 
CREATE TABLE IF NOT EXISTS `ba` (
  `id_postaci` mediumint(8) UNSIGNED NOT NULL,
  `dane_e` char(20) collate utf8_polish_ci NOT NULL,
  `dane_f` char(20) collate utf8_polish_ci NOT NULL,
  `dane_g` char(20) collate utf8_polish_ci NOT NULL,
  UNIQUE KEY `id_postaci` (`id_postaci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
 
CREATE TABLE IF NOT EXISTS `bb` (
  `id_postaci` mediumint(8) UNSIGNED NOT NULL,
  `dane_h` char(20) collate utf8_polish_ci NOT NULL,
  `dane_i` char(20) collate utf8_polish_ci NOT NULL,
  `dane_j` char(20) collate utf8_polish_ci NOT NULL,
  UNIQUE KEY `id_postaci` (`id_postaci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
[SQL] pobierz, plaintext 

wydaje mnie się że poprawnie wygląda struktura tabel tylko zastanawia mnie czy w tabelach BA oraz BB pole id_postaci ma być INDEXem czy unique a może to bez różnicy w tym wypadku

co do samego klucza obcego , nie wiem czy poprawnie jest założony, z założenia miało to wyglądać tak że jeśli jest usuwana postać (czyli z tabeli (IMG:style_emoticons/default/cool.gif) rekordy są również usuwane z tabel BA,BB

[SQL] pobierz, plaintext 
ALTER TABLE `ba`
  ADD CONSTRAINT `ba` FOREIGN KEY (`id_postaci`) REFERENCES `b` (`id_postaci`) ON DELETE CASCADE;
[SQL] pobierz, plaintext 

i jeszcze coś , jeżeli powyższe zapytania są prawidłowe to jak "zrobić" aby w tabelach BA,BB pojawiły się rekordy

[mortus]

Pole id_postaci w tabelach BA i BB powinno być unikalne UNIQUE, żeby nie "mieszały się" dane postaci (de facto tylko wtedy będzie to określać relację jeden do jednego). UNIQUE KEY to specyficzny INDEX, zatem masz odpowiedź na jedno z pytań.
Zależność CONSTRAINT dodana jak najbardziej prawidłowo.
Dane/rekordy do tabel BA i BB powinny być dodane już w momencie tworzenia postaci, jeśli tylko dana postać będzie kiedykolwiek korzystać z informacji z tych tabel. Skoro korzystasz z silnika InnoDB to wykorzystaj możliwości jakie daje i użyj transakcji podczas dodawania postaci. Alternatywą jest TRIGGER AFTER INSERT, który dodaje do tabel BA i BB domyślne wartości pod nowym id_postaci, a później zapytanie aktualizujące (UPDATE), które aktualizuje dodany rekord, używając danych użytkownika.

EDIT: Napomnę jeszcze, że taki podział tabel bazy danych ma sens tylko wtedy, kiedy danych/informacji o postaci jest naprawdę bardzo dużo jak i wtedy, gdy możemy je podzielić na pewne charakterystyczne "grupy".

Ten post edytował mortus 29.12.2011, 20:40:43

[Ulysess]

triggerow na razie nie będę wykorzystywał z racji ze nic o nich nie wiem (wszystko w swoim czasie)

danych o postaci jest dosyć sporo ~60 , mam zamiar jeszcze stworzyć dodatkowe tabele i tam przenieść cześć danych z aktualnych po to aby zmniejszyć ilość operacji na tych tabelach

co do kluczy obcych tworząc tabele które we wcześniejszym poście zaprezentowałem , zakładając taki klucz obcy zauważyłem kilka rzeczy które nie podobają mnie się..
1) mogę usunąć rekord z tabeli BA oraz zmienić jego id
2) jeżeli zmienię id postaci w tabeli A to w tabeli B pozostaje takie samo (może źle mnie się wydaje ale powino id w pozostałych tabelach na które jest założony klucz obcy zostać zmienione ID)

z założenia miało to wyglądać tak że gdy dodam do tabeli B rekord są tworzone rekordy w tabelach BA BA BC ... z takim samym ID postaci jak w tabeli B , z tabel BA BB BC.. nie można usunąć rekordu ani zmienić jego ID postaci ale jeżeli zostanie rekord usunięty z tabeli B to zostaną usunięte rekordy z tabel BA BB BC.. lub jeśli zmienię id postaci w B zostają również zmienione id w pozostałych tabelach.

z Transakcjami do tej pory nie miałem styczność ale wykorzystując InnoDB jak najbardziej wypada a nawet trzeba wykorzystać transakcje dlatego i tu pojawia się pytanie

[PHP] pobierz, plaintext 
mysql_query("start transaction;");
mysql_query("INSERT INTO `test`.`b` (`id_postaci`,`id_konto` ,`dane_a` ,`dane_b` ,`dane_c` ,`dane_d`)VALUES (NULL , '7', 'a', 'b', 'c', 'd');");
mysql_query("INSERT INTO `test`.`ba` (`id_postaci` ,`dane_e` ,`dane_f` ,`dane_g`)VALUES ('5', 'e', 'f', 'g');");
mysql_query("commit;");
[PHP] pobierz, plaintext 

wykorzystując powyższy kod dane zostaną dodane do tabeli B oraz BA lub żadnej czy muszę zastosować jakiś warunek że jeśli zostały dodane to jest commit a jeśli coś poszło nie tak to rollback ?

[mortus]

W sytuacji oznaczonej 1) no niestety musisz dobrze oprogramować skrypt, albo kombinować z TRIGGER-ami (choć to raczej nie pomoże).
W sytuacji 2) do definicji klucza obcego dodajesz ON DELETE CASCADE ON UPDATE CASCADE i wszystko działa.

[SQL] pobierz, plaintext 
CONSTRAINT `fk` FOREIGN KEY `id_postaci` REFERENCES `postacie` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
[SQL] pobierz, plaintext 

W transakcji nie potrzebujesz żadnego ROLLBACK, jeśli transakcja nie zostanie potwierdzona, żadne dane nie powinny być wprowadzone.

Jeśli chcesz w pełni korzystać z transakcji, to przerzuć się na PDO.