MySQL - spojení tabulek

[Jiří Taypan]

Dobrý den,
mám dvě tabulky:

Kód: [Vybrat]

CREATE TABLE IF NOT EXISTS `komponenty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `typ` enum('cpu','gpu','mb','ram','hdd','cool','fan','power') COLLATE utf8_czech_ci NOT NULL,
  `jmeno` varchar(500) COLLATE utf8_czech_ci NOT NULL,
  `cena` int(9) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=17 ;

--
-- Vypisuji data pro tabulku `komponenty`
--

INSERT INTO `komponenty` (`id`, `typ`, `jmeno`, `cena`) VALUES
(1, 'cpu', 'AMD Phenom II X4 @ 3 GHz', 666),
(2, 'gpu', 'ATI HD5770 1GB', 456),
(3, 'mb', 'Asus M4A79XTD EVO 790X', 54),
(4, 'ram', 'Kingston DIMM 4096MB DDR III', 45),
(5, 'hdd', 'Samsung SpinPoint F3 - 1TB', 456),
(6, 'cool', 'CoolerMaster Elite', 786),
(7, 'fan', 'Zalman CU Cooler', 486),
(8, 'power', 'PSU Seasonic 80+', 486);

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

--
-- Struktura tabulky `sestavy`
--

CREATE TABLE IF NOT EXISTS `sestavy` (
  `typ` enum('game','pro','office','home') COLLATE utf8_czech_ci NOT NULL,
  `level` enum('1','2','3','4') COLLATE utf8_czech_ci NOT NULL,
  `cpu` int(11) NOT NULL,
  `gpu` int(11) NOT NULL,
  `mb` int(11) NOT NULL,
  `ram` int(11) NOT NULL,
  `hdd` int(11) NOT NULL,
  `cool` int(11) NOT NULL,
  `fan` int(11) NOT NULL,
  `power` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

--
-- Vypisuji data pro tabulku `sestavy`
--

INSERT INTO `sestavy` (`typ`, `level`, `cpu`, `gpu`, `mb`, `ram`, `hdd`, `cool`, `fan`, `power`) VALUES
('game', '1', 1, 2, 3, 4, 5, 6, 7, 8);


jedna tabulka obsahuje sestavy (sloupce: `typ`, `level`, `cpu`, `gpu`, `mb`, `ram`, `hdd`, `cool`, `fan`, `power`)  a druhá komponenty ze kterých se sestavy skládají (sloupce: `id`, `typ`, `jmeno`, `cena`). A např. sestavy.cpu obsahuje id odpovídající komponenty.
Otázka je následující: Jak by měl vypadat dotaz vracející sestavy ovšem s id nahrazenými jmény komponent?

Předem děkuji za radu už jsem z toho zoufalej...

[Reklama]

[Martin Štekl]

Ahoj,
tady přikládám ten SQL dotaz 

Kód: [Vybrat]

SELECT
ts.typ,
ts.level,
tk1.jmeno AS cpu,
tk2.jmeno AS gpu,
tk3.jmeno AS mb,
tk4.jmeno AS ram,
tk5.jmeno AS hdd,
tk6.jmeno AS cool,
tk7.jmeno AS fan,
tk8.jmeno AS power
FROM sestavy AS ts
INNER JOIN komponenty AS tk1 ON (ts.cpu = tk1.id)
INNER JOIN komponenty AS tk2 ON (ts.gpu = tk2.id)
INNER JOIN komponenty AS tk3 ON (ts.mb = tk3.id)
INNER JOIN komponenty AS tk4 ON (ts.ram = tk4.id)
INNER JOIN komponenty AS tk5 ON (ts.hdd = tk5.id)
INNER JOIN komponenty AS tk6 ON (ts.cool = tk6.id)
INNER JOIN komponenty AS tk7 ON (ts.fan = tk7.id)
INNER JOIN komponenty AS tk8 ON (ts.power = tk8.id)

A dovolím si k němu pár komentářů. Používám INNER JOIN, protože předpokládám, že pro každou sestavu musí existovat ID prvku v komponentách. A nejen protože v tabulce jsou ty sloupce NOT NULL. LEFT JOIN by bylo možné taky použít (takříkajíc pro jistotu), ale INNER JOIN je o něco rychlejší.

Nevím jestli znáš použití AS. Jedná se prostě o jedoduchý alias, pod kterým se bude daný prvek (tabulka, sloupec, spocitana hodnota, ...) dále objejovat buď v dotazu, nebo následně ve výstupní resultu.

Také nemusím pokládat nějaký dotaz s WHERE, protože v tabulce komponent máš primární klíč na ID komponenty, takže by se ti nemělo stát, že si budeš pomocí ID odkazovat na jinou komponentu než jakou chceš. Také index UNIQUE už na ID v komponentách není potřeba, protože to zajistí už samotný primární klíč.

Snad ti to pomůže

[Taypan.]

Nádhera, děkuju moc
i za poučný komentář 

[logikk]

Poučnej komentář to sice je, ale úplně špatnym směrem. Správnej komentář měl znít:

Jdeš na to úplně blbě. Udělej normálně join sestavy a komponenty a nech si komponenty vypsat řádek po řádku a výsledek si seber až skriptem při vytváření stránky. Bude to mít totiž jednu velkou výhodu: až si vzpomeneš, že k sestavě patří i case, tak nebudeš muset upravovat skript někde hluboko v aplikaci, ale jen prostě přidáš danej řádek do patřičnejch tabulek.
Nemluvě o tom, co ti tendle dotaz vyplodí, když některá ze sestav bude mít např. dva harddisky (za chvíli to vzhledem k existenci SSD nebude výjimka) nebo dvě grafiky. (domácí úkol).

[withoutnick]

Muze si ten dotaz predelat na view http://dev.mysql.com/doc/refman/5.0/en/create-view.html a potom uz nebude treba zasahovat dovnitr "scriptu" aplikace, nybrz bude stacit jen upravit tento view

[Reklama]

[karlos]

jen dodam ze je to blbe navrzene. v tabulce sestavy by rozhodne nemely byt sloupce pro kompenenty. pridat treti tabulku (id, id_sestava, id_komponent). proc? protoze >=2 komponenty jednoho typu(harddisky) v jedne sestave. a pak taky princip.

[logikk]

karlos: No jo, já sem to ani detailně nezkoumal, jen jsem viděl ten šílenej výslednej dotaz, tak jsem se proti němu ohradil. Mě ani nenapadlo, že to jde navrhnout takhle blbě... Ale napadnout mě to mělo, protože pokud z něčeho lezou šílený dotazy, pak je to blbě navržený.

withounic: To je sice pravda, ale tim stejně nevyřešíš možnost dvou hdd.  A furt zůstává nevyřešenejch víc stejnech komponent.
Navíc při přidání jednoho typu komponentu muset jít měnit tabulku a z ní generovanej view prostě značí chybu v návrhu. Ať už se tu vynucenou změnu v datovym modelu se povede trochu odstínit.
A navíc ji neodstíníš dokonale, protože např. u filtrů v aplikaci budeš taky muset zavýst novej typ komponent atd. atd.

[Martin Š.]

Snažím se sem už od včerejšího večera vložit další příspěvek, ale databáze mi hlásí pořád chybu že by se to podařilo až teď?

[Martin Štekl]

Že by konečně teda ten příspěvek? Zkusím to rozdělit na části :/
Ještě mě napadlo jedno doporučení, že by možná bylo lepší, kdyby tyto 2 tabulky byly spojeny přes jednu další. Pak by tabulka sestav vedla data opravdu jen o sestavách a každá sestava by měla vlastní ID jako primární klíč.

Tabulka komponent by zůstala tak jak je a třetí tabulka by obsahovala pouze 2 sloupce - ID sestavy a ID komponenty. Na této tabulce by nebyl žádný UNIQUE index, ale obyčejný INDEX přes oba sloupce. Tím by se v tabulce těchto relací mohlo objevit každé ID vícekrát. Zároveň by to umožnilo do jedné sestavy umístit větší počet například pevný disků a dokonce i stejných. Počet stejných komponent v soustavě by se dal také řešit dalším sloupcem v této tabulce a následně by bylo možné vytvořit nad ID sloupci (společně) UNIQUE index, protože pak by každá relace ID_sestavy<--> ID_komponenty byla unikátní už z logiky věci.

Současně by to i trochu zjednodušilo SQL query a starost o to, zda se ti linkují správné komponenty do správného sloupce v tabulce sestav.

[Martin Štekl]

Pokračování
Příklad query uvádím níže:

Kód: [Vybrat]

SELECT
ts.typ,
ts.level,
tk.jmeno,
tk.typ
FROM sestavy AS ts
LEFT JOIN relace AS tr ON (ts.id = tr.sestava_id)
LEFT JOIN komponenty AS tk ON (tr.komponenta_id = tk.id)


[Martin Štekl]

Uff, nechápu, proč mi to nechce ta databáze vzít najednou
Query v původním příspěvku mi podle Admineru trvala asi 0,055 s a tahle jenom 0,001 s. Tudíž je pak třeba zvážit, kolik v systému bude záznamů a co se vyplatí víc. Zda delší query a pak rychlejší zpracování programovacím jazykem, nebo kratší query a pak zpracování v jazyce delší. Sám bych pro malé množství záznamů v tabulkách volil první možnost, pro větší množství dat bych použil spíše tuto druhou variantu.

[Martin Štekl]

A snad už konečně i poslední část
Tento návrh má ale teké jednu chybku. Tato SQL query získá pro každou komponentu samostatný řádek. Takže by bylo nutné v samotném programu řešit něco jako GROUP_BY_sestava.id, protože MySQL by pak vrátila pouze jednu komponentu. Druhou možností by bylo pro každou sestavu provádět samostatný dotaz, ale to by bylo časově náročné.

[Martin Štekl]

Závěrem se omlouvám za rozkouskování, ale až u poslední části jsem zjistil, že fórum asi úplně dobře neescapuje SQL code - skoušel jsem dát to i do značky code, stejně to nepomohlo. Takže proto jsem tam i místo mezer použil podtržítka. A ano, jsem evidentně lama

[Taypan.]

jen dodam ze je to blbe navrzene. v tabulce sestavy by rozhodne nemely byt sloupce pro kompenenty. pridat treti tabulku (id, id_sestava, id_komponent). proc? protoze >=2 komponenty jednoho typu(harddisky) v jedne sestave. a pak taky princip.

Jak by tedy mela vypadat tabulka sestavy? Komponenty by zustaly tak jak jsou?

[Taypan.]

tak mam tohle:

Kód: [Vybrat]

CREATE TABLE IF NOT EXISTS `kombinace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_sestava` int(11) NOT NULL,
  `id_komponenta` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=6 ;

--
-- Vypisuji data pro tabulku `kombinace`
--

INSERT INTO `kombinace` (`id`, `id_sestava`, `id_komponenta`) VALUES
(1, 1, 2),
(2, 1, 3),
(3, 1, 5),
(4, 1, 8),
(5, 1, 6);

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

--
-- Struktura tabulky `komponenty`
--

CREATE TABLE IF NOT EXISTS `komponenty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `typ` enum('cpu','gpu','mb','ram','hdd','cool','fan','power') COLLATE utf8_czech_ci NOT NULL,
  `jmeno` varchar(500) COLLATE utf8_czech_ci NOT NULL,
  `cena` int(9) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=17 ;

--
-- Vypisuji data pro tabulku `komponenty`
--

INSERT INTO `komponenty` (`id`, `typ`, `jmeno`, `cena`) VALUES
(1, 'cpu', 'AMD Phenom II X4 @ 3 GHz', 666),
(2, 'gpu', 'ATI HD5770 1GB', 456),
(3, 'mb', 'Asus M4A79XTD EVO 790X', 54),
(4, 'ram', 'Kingston DIMM 4096MB DDR III', 45),
(5, 'hdd', 'Samsung SpinPoint F3 - 1TB', 456),
(6, 'cool', 'CoolerMaster Elite', 786),
(7, 'fan', 'Zalman CU Cooler', 486),
(8, 'power', 'PSU Seasonic 80+', 486);

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

--
-- Struktura tabulky `sestavy`
--

CREATE TABLE IF NOT EXISTS `sestavy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `popis_o` varchar(5000) COLLATE utf8_czech_ci NOT NULL,
  `popis_pro` varchar(5000) COLLATE utf8_czech_ci NOT NULL,
  `cena` int(11) NOT NULL,
  `bar_game` int(11) NOT NULL,
  `bar_pro` int(11) NOT NULL,
  `bar_office` int(11) NOT NULL,
  `bar_home` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=2 ;

--
-- Vypisuji data pro tabulku `sestavy`
--

INSERT INTO `sestavy` (`id`, `popis_o`, `popis_pro`, `cena`, `bar_game`, `bar_pro`, `bar_office`, `bar_home`) VALUES
(1, 'Popis o produktu', 'Pro koho', 25000, 1, 2, 3, 4);
a ted to zajimavejsi jak postavit dotaz aby vracel sestavu v nejakem normalni tvaru?