Essa estrutura de banco de dados (normalizada) me permitirá pesquisar por tags conforme pretendido?
Eu estou tentando configurar um banco de dados MySQL normalizado contendo as três tabelas a seguir. A primeira tabela contém uma lista de itens que podem ser descritos por várias tags. A terceira tabela contém as várias tags usadas para descrever os itens na primeira tabela. A tabela do meio relaciona as outras duas tabelas entre si. No caso de cada tabela, o ID é uma chave primária de incremento automático (e cada uma é usada como chave estrangeira na tabela do meio)
+---------------+---------------------+---------------+
| Table 1 | Table 2 | Table 3 |
+---------------+---------------------+---------------+
|id item |id item_id tag_id|id tag|
+---------------+---------------------+---------------+
| 1 spaniel| 1 1 4| 1 bird|
| 2 tabby| 2 1 23| 4 pet|
| 3 chicken| 3 1 41|23 dog|
| 4 goldfish| 4 2 4|24 cat|
| | 5 2 24|25 reptile|
| | 6 3 1|38 fish|
| | 7 3 40|40 delicious|
| | 8 4 4|41 cheap|
| | 9 4 38|42 expensive|
| |10 4 41| |
| | | |
+---------------+---------------------+---------------+
Quero executar uma consulta de mais uma ou mais tags nas três tabelas para retornar os itens que correspondem a TODAS as tags.
Assim, por exemplo, consultar "pet" retornaria os itens (1) spaniel, (2) tabby e (4) goldfish, porque todos eles estão marcados como "pet". Consultar "barato" e "animal de estimação" juntos retornaria (1) spaniel e (4) peixe dourado, porque ambos são marcados como "barato" e "animal de estimação". O gato malhado não seria devolvido, pois está marcado apenas como "animal de estimação", mas não "barato" (no meu mundo, os gatos malhados são caros: P)
A consulta de "barato", "animal de estimação" e "cachorro" retornaria apenas o Spaniel (1), pois é o único que corresponde às três tags.
Enfim, esse é o comportamento desejado. Eu tenho duas perguntas.
Essa é a melhor maneira de configurar minhas tabelas para os propósitos pretendidos? Ainda sou novo nas idéias de normalização de bancos de dados e estou aprendendo isso ao longo do processo - qualquer contribuição sobre eficiência ou mesmo se esse é um layout apropriado para o meu banco de dados seria muito apreciado.
Desde que a configuração acima seja viável, como estruturar uma única consulta do MySQL para atingir o objetivo pretendido? * (Isto é, para uma série de tags, retornando APENAS os itens que correspondem a TODAS as tags especificadas). Eu tentei fazer uma variedade de JOINs / UNIONs, mas nenhum deles está me dando o efeito desejado (geralmente retorna TODOS os itens que correspondem a QUALQUER das tags). Passei algum tempo olhando o manual do MySQL online, mas sinto que estou perdendo algo conceitualmente.
* Digo uma consulta única, pois é claro que eu poderia executar uma série de consultas WHERE / JOIN simples, uma para cada tag e depois combinar / classificar os itens retornados em PHP ou algo após o fato, mas parece uma maneira tola e ineficiente de fazer isto. Eu sinto que existe uma maneira de conseguir fazer isso com uma única consulta MySQL, dada a configuração apropriada.