Może najpierw cała struktura:
-- phpMyAdmin SQL Dump -- version 2.11.4 -- <a href="http://www.phpmyadmin.net" target="_blank">http://www.phpmyadmin.net</a> -- -- Host: localhost -- Czas wygenerowania: 31 Lip 2010, 19:58 -- Wersja serwera: 5.0.45 -- Wersja PHP: 5.2.5 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Baza danych: `fansite` -- -- -------------------------------------------------------- -- -- Struktura tabeli dla `chance` -- CREATE TABLE `chance` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `content` varchar(40) collate utf8_polish_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Struktura tabeli dla `comments` -- CREATE TABLE `comments` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `news_id` smallint(6) NOT NULL, `author` varchar(40) collate utf8_polish_ci NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `subject` varchar(60) collate utf8_polish_ci NOT NULL, `content` text collate utf8_polish_ci NOT NULL, PRIMARY KEY (`id`), KEY `news_id` (`news_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Struktura tabeli dla `groups` -- CREATE TABLE `groups` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(40) collate utf8_polish_ci NOT NULL, `access` smallint(6) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3 ; -- -------------------------------------------------------- -- -- Struktura tabeli dla `guides` -- CREATE TABLE `guides` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `author_id` smallint(6) NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `subject` varchar(50) collate utf8_polish_ci NOT NULL, `content` text collate utf8_polish_ci NOT NULL, PRIMARY KEY (`id`), KEY `author_id` (`author_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Struktura tabeli dla `items` -- CREATE TABLE `items` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(50) collate utf8_polish_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Struktura tabeli dla `loot` -- CREATE TABLE `loot` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `monster_id` smallint(6) NOT NULL, `item_id` smallint(6) NOT NULL, `chance_id` smallint(6) NOT NULL, PRIMARY KEY (`id`), KEY `monster_id` (`monster_id`,`item_id`,`chance_id`), KEY `item_id` (`item_id`), KEY `chance_id` (`chance_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Struktura tabeli dla `monsters` -- CREATE TABLE `monsters` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(60) collate utf8_polish_ci NOT NULL, `health` int(11) NOT NULL, `experience` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Struktura tabeli dla `news` -- CREATE TABLE `news` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `subject` varchar(60) collate utf8_polish_ci NOT NULL, `content` text collate utf8_polish_ci NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `author_id` smallint(6) NOT NULL, PRIMARY KEY (`id`), KEY `author_id` (`author_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Struktura tabeli dla `users` -- CREATE TABLE `users` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(40) collate utf8_polish_ci NOT NULL, `password` varchar(40) collate utf8_polish_ci NOT NULL, `group_id` smallint(6) NOT NULL, PRIMARY KEY (`id`), KEY `group_id` (`group_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=2 ; -- -------------------------------------------------------- -- -- Stand-in structure for view `users_view` -- CREATE TABLE `users_view` ( `id` smallint(6) ,`name` varchar(40) ,`password` varchar(40) ,`group_name` varchar(40) ,`access` smallint(6) ); -- -------------------------------------------------------- -- -- Structure for view `users_view` -- DROP TABLE IF EXISTS `users_view`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `fansite`.`users_view` AS SELECT `fansite`.`users`.`id` AS `id`,`fansite`.`users`.`name` AS `name`,`fansite`.`users`.`password` AS `password`,`fansite`.`groups`.`name` AS `group_name`,`fansite`.`groups`.`access` AS `access` FROM (`fansite`.`users` LEFT JOIN `fansite`.`groups` ON((`fansite`.`users`.`group_id` = `fansite`.`groups`.`id`))); -- -- Ograniczenia dla zrzutów tabel -- -- -- Ograniczenia dla tabeli `comments` -- ALTER TABLE `comments` ADD CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`); -- -- Ograniczenia dla tabeli `guides` -- ALTER TABLE `guides` ADD CONSTRAINT `guides_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `news` (`id`); -- -- Ograniczenia dla tabeli `loot` -- ALTER TABLE `loot` ADD CONSTRAINT `loot_ibfk_3` FOREIGN KEY (`chance_id`) REFERENCES `chance` (`id`), ADD CONSTRAINT `loot_ibfk_1` FOREIGN KEY (`monster_id`) REFERENCES `monsters` (`id`), ADD CONSTRAINT `loot_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`); -- -- Ograniczenia dla tabeli `news` -- ALTER TABLE `news` ADD CONSTRAINT `news_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`); -- -- Ograniczenia dla tabeli `users` -- ALTER TABLE `users` ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`);
Jak widać tabele zostały już powiązane - chyba jest dobrze.
Utworzyłem dodatkowo widok, który pozwoli ułatwi mi tworzenie zapytań w php.
I tutaj pierwszy problem:
Będę potrzebował wyciągnąć danego potwora z bazy z całym lootem.
Tabele: monsters, loot, items, chance
Teraz pytanie jak złączyć te tabele, aby się zbytnio w php nie narobić i zrzucić większość na MySQL, myślałem o widoku, jednak dla jednego potwora loot może zawierać od kilku do kilkunastu elementów.
Drugie jest pytanie:
Czy można zaprojektować to lepiej? W inny sposób? Może inne typy pól? Starałem się wszystko starannie przemyśleć i ocenić +/- ile danych gdzie będę przechowywał.