Дизайн базы данных для включения нескольких тегов, таких как Stackru?

Question

У меня есть следующие таблицы.

Таблицастатей
a_id INT первичный уникальный
имя VARCHAR
Описание VARCHAR
c_id INT

Таблицакатегорий
id INT
cat_name VARCHAR

А пока я просто использую

SELECT a_id,name,Description,cat_name FROM Articles LEFT JOIN Category ON Articles.a_id=Category.id WHERE c_id={$id}

Это дает мне все статьи, которые относятся к определенной категории вместе с названием категории.
Каждая статья имеет только одну категорию.

И я использую подкатегорию подобным образом (у меня есть другая таблица с именем sub_cat).
Но у каждой статьи нет необходимости иметь подкатегорию. Она может принадлежать нескольким категориям.

Теперь я думаю о том, чтобы пометить статью несколькими категориями, точно так же, как помечены вопросы в stack overflow (например, с несколькими тегами, такими как PHP,MYSQL,SQL и т. Д.).

И позже я должен отобразить (отфильтровать) всю статью с определенными тегами (например, с тегами php,php +MySQL), а также я должен отобразить теги вместе с названием статьи,Description.
Может ли кто-нибудь помочь мне изменить базу данных?(Я использую php + MySQL на сервере)

Answer 1

Создайте новую таблицу:

CREATE TABLE ArticleCategories(
    A_ID INT,
    C_ID INT,
    Constraint PK_ArticleCategories Primary Key (Article_ID, Category_ID)
)

(это синтаксис сервера SQL, может немного отличаться для MySQL)

Это называется " Соединительная таблица " или " Таблица сопоставления ", и именно так вы выражаете отношения "многие ко многим" в SQL. Поэтому, когда вы хотите добавить категорию в статью, просто INSERT строка в этой таблице с идентификаторами статьи и категории.

Например, вы можете инициализировать его так:

INSERT Into ArticleCategories(A_ID,C_ID)
    SELECT A_ID,C_ID From Articles

Теперь вы можете удалить c_id из вашей таблицы статей.

Чтобы вернуть все категории для одной статьи, вы должны использовать запрос, подобный следующему:

SELECT a_id,name,Description,cat_name 
FROM Articles 
LEFT JOIN  ArticleCategories ON Articles.a_id=ArticleCategories.a_id 
INNER JOIN Category ON ArticleCategories.c_id=Category.id 
WHERE Articles.a_id={$a_id}

---

В качестве альтернативы, чтобы вернуть все статьи, которые имеют категорию, как определенную строку:

SELECT a_id,name,Description
FROM Articles 
WHERE EXISTS(   Select * 
                From ArticleCategories 
                INNER JOIN Category ON ArticleCategories.c_id=Category.id 
                WHERE Articles.a_id=ArticleCategories.a_id 
                  AND Category.cat_name LIKE '%'+{$match}+'%'
             )

(Возможно, вам придется настроить последнюю строку, так как я не уверен, как передаются строковые параметры MySQL+PHP.)

Answer 2

Хорошо RBarryYoung вы спросили меня о рекомендации / проанализировать, вы получите один

Эта ссылка / анализ основана на анализе документации / исходного кода с сервера MySQL

INSERT Into ArticleCategories(A_ID,C_ID)
    SELECT A_ID,C_ID From Articles

В большой таблице Articles со многими строками эта копия отодвинет одно ядро ​​от ЦП до 100% нагрузки и создаст временную таблицу на диске, что снизит полную производительность MySQL, поскольку диск будет перегружен этой копией. Если это однократный процесс, это не так уж плохо, но посчитайте, если вы запускаете это каждый раз...

SELECT a_id,name,Description
FROM Articles 
WHERE EXISTS(   Select * 
                From ArticleCategories 
                INNER JOIN Category ON ArticleCategories.c_id=Category.id 
                WHERE Articles.a_id=ArticleCategories.a_id 
                  AND Category.cat_name LIKE '%'+{$match}+'%'
             )

Примечание: не принимайте время выполнения на sqlfriddle по-настоящему занятым сервером, а время может отличаться, чтобы сделать хорошее заявление, но посмотрите, что должен сказать View Execution Plan

см. http://sqlfiddle.com/ для демонстрации

Оба запроса всегда инициируют полное сканирование таблицы для статей Articles и двух ЗАВИСИМЫХ ПОДПИСЕЙ, что не очень хорошо, если у вас есть большая таблица Articles со многими записями. Это означает, что производительность зависит от количества строк статей, даже если вы хотите только статьи, которые находятся в категории.

Select * 
                From ArticleCategories 
                INNER JOIN Category ON ArticleCategories.c_id=Category.id 
                WHERE Articles.a_id=ArticleCategories.a_id 
                  AND Category.cat_name LIKE '%'+{$match}+'%'

Этот запрос является внутренним подзапросом, но при попытке его выполнить MySQL не может быть запущен, поскольку он зависит от значения таблицы Articles, поэтому это коррелированный подзапрос. тип подзапроса, который будет оцениваться один раз для каждой строки, обрабатываемой внешним запросом. не очень хорошо

Есть больше способов переписать запрос RBarryYoung, я покажу один. Путь INNER JOIN гораздо эффективнее даже с оператором LIKE. Заметьте, что я вывел из него привычку, что я начинаю с таблицы с наименьшим числом отключенных записей и продолжаю свой путь вверх, если вы начнете с таблицы Articles, выполнение которой будет то же самое, если оптимизатор MySQL выбирает правильный план..

SELECT 
   Articles.a_id
 , Articles.name
 , Articles.description
FROM 
 Category

INNER JOIN
 ArticleCategories
ON
 Category.id = ArticleCategories.c_id

INNER JOIN
 Articles
ON 
 ArticleCategories.a_id = Articles.a_id

WHERE 
 cat_name LIKE '%php%';
;

см. http://sqlfiddle.com/ для демонстрации. Обратите внимание, что это выглядит хуже, потому что похоже, что нужно проверить больше строк

Обратите внимание, что если в таблице Article мало записей о записях, способ RBarryYoung EXIST и способ INNER JOIN будут выполнять более или менее то же самое в зависимости от времени выполнения, а также больше доказательств того, что способ INNER JOIN лучше масштабируется при увеличении количества записей.

http://sqlfiddle.com/ EXISTS теперь требует больше записей статей, которые необходимо проверять (даже если они не связаны с таблицей ArticleCategories), поэтому запрос теперь менее эффективен http://sqlfiddle.com/ INNER JOIN тот же план объяснения, что и в первом демо

Дополнительные замечания по поводу масштабирования становятся еще хуже, когда вы также хотите, чтобы ORDER BY или GROUP BY NOT EXIST имели больше шансов, что они создадут временную таблицу на диске, которая снизит производительность MySQL.

Давайте также проанализируем LIKE '%php%' vs = 'php' для способов EXIST и INNER JOIN.

Существующий способ

http://sqlfiddle.com/ / http://sqlfiddle.com/ (больше статей) объяснение говорит мне, что оба шаблона более или менее одинаковы, но 'php 'должно быть немного быстрее, потому что от типа const против ref в столбце TYPE, но LIKE %php% будет использовать больше ЦП, потому что должен выполняться алгоритм сравнения строк.

ВНУТРЕННИЙ ПУТЬ

http://sqlfiddle.com/ / http://sqlfiddle.com/ (больше статей) объясните мне, что LIKE '% php%' должен быть медленнее, потому что Необходимо проанализировать еще 3 строки, но не медленнее шокировать в этом случае (вы можете видеть, что индекс на самом деле используется не лучшим образом).

RBarryYoung работает, но не поддерживает производительность по крайней мере на сервере MySQL, см. http://sqlfiddle.com/ или http://sqlfiddle.com/ для примеров, которые будут масштабироваться на больших столах с большим количеством записей это то, что нужно начинающему