Лучший способ хранения и поиска ключевых слов для записи в PHP и MySQL?
Я не прикасался ни к какому коду в течение добрых 4-5 месяцев, поэтому, просто вернувшись к нему сегодня, обычно мне требуется неделя или около того, чтобы снова получить всю информацию, проходящую через мой мозг, как только я возьму такой отпуск. Итак, мой проект, который я собираюсь начать, будет базой данных закладок PHP/MySQL.
Я хочу создать хорошую базу данных с возможностью поиска со всеми моими любимыми сайтами/закладками. Каждой записи будет присвоено несколько ключевых слов, поэтому я могу легко искать все свои закладки для термин "php" и все записи с "php" в столбце ключевых слов или заголовке или иным образом вернутся в результирующий набор.
Вот моя идея для базы данных на данный момент...
auto_id = /*Auto incremented ID number for database*/
name/title = /*Name/title of the Website*/
description = /*brief description of the site*/
URL = /*URL to open when I click a link*/
clicks = /*increments by 1 everytime I click the link*/
date_created = /*datetime that URL bookmark was added*/
date_accessed = /*datetime field for when last clicked on*/
category = /*category name or number to create a folder like structure of bookmarks in groups*/
sub_category = /*some categories will have subcategories (ie programming->c## programming->PHP )*/
keywords = /*Keywords used for searching*/
Для меня это довольно прямолинейно в том, как построить эту систему, за исключением того, что я ищу помощь/совет о наилучшем способе хранения ключевых слов. Каждый веб-сайт/запись, которую я добавляю в базу данных, может содержать от 1 до нескольких ключевых слов на сайт. Эти ключевые слова должны быть в состоянии помочь с поисковой частью моего приложения. Так что как я должен хранить ключевые слова для сайта в своей базе данных? Я знаю, что я мог бы просто иметь строку "ключевые слова" в таблице и хранить ключевые слова для каждой записи, например "php, web и т. Д., keyword4", Поэтому все ключевые слова для каждого сайта сохраняются в 1 столбце, но это не кажется лучшим методом, когда дело доходит до поиска в базе данных.
Пожалуйста, скажите мне, как бы вы сделали эту часть? Спасибо за любую помощь
3 answers
Лучший способ сделать это - создать отдельную таблицу, содержащую ваши ключевые слова, а затем добавить таблицу пересечение (или соединение ), чтобы объединить ключевые слова с закладками.
CREATE TABLE bookmarks (
id INT NOT NULL,
... etc.
)
CREATE TABLE keywords (
id INT NOT NULL,
... etc.
)
CREATE TABLE bookmark_keywords (
bookmark_id INT NOT NULL,
keyword_id INT NOT NULL,
PRIMARY KEY (bookmark_id, keyword_id),
FOREIGN KEY bookmark_id REFERENCES bookmarks (id),
FOREIGN KEY keyword_id REFERENCES keywords (id)
)
Когда вы вставляете закладку, вы также вставляете любые ключевые слова, которые используются и которых еще нет в таблице keywords, а также строку в bookmark_keywords, чтобы соединить ключевое слово с закладкой.
Затем, когда вы хотите запросить, какие ключевые слова содержит закладка:
SELECT k.*
FROM keywords AS k
LEFT JOIN bookmark_keywords AS kb
ON kb.keyword_id = k.id
WHERE kb.bookmark_id = [ID of the bookmark]
И чтобы запросить, какие закладки содержат определенное ключевое слово:
SELECT b.*
FROM bookmarks AS b
LEFT JOIN bookmark_keywords AS kb
ON kb.bookmark_id = b.id
WHERE kb.keyword_id = [ID of the keyword]
Вы правы, хранение списка, разделенного запятыми, в одном столбце не является хорошим способом сделать это (это называется повторяющейся группой и нарушает Первую нормальную форму проектирования реляционных баз данных).
Использование предиката LIKE не является хорошим выбором, потому что он не может извлечь выгоду из индекса. Поиск ключевых слов таким способом в сотни или тысячи раз медленнее, чем создание надлежащей базы данных в обычном виде и добавление индексов.
Вам необходимо сохраните вторую таблицу со списком ключевых слов и третью таблицу "многие ко многим", чтобы связать ключевые слова с соответствующими закладками. Это довольно стандартный дизайн для "пометки" в реляционной базе данных.
В нереляционных базах данных, таких как CouchDB или MongoDB, вы можете сделать одно поле набором ключевых слов и индексировать их, чтобы запросы могли быть эффективными. Но не в реляционной базе данных.
См. также:
Также при просмотре этих вопросов проверьте множество связанных вопросов в столбце справа.
Самый простой и быстрый метод поиска для реализации - это использование оператора LIKE в MySQL. LIKE позволяет вам искать в столбце определенную строку. Рассмотрим следующий пример...
auto_id name description
1 Cool PHP Site you know you love it
2 PLARP! its Ruby gems gems gems!
3 SqlWha sql for the masses
4 FuzzD00dle fun in the sun, with some fuzz
Вы можете найти все строки, содержащие строку "php" в поле "имя" или "описание", используя следующий запрос...
SELECT * FROM bookmarks WHERE name LIKE '%php%' OR description LIKE '%php%';
- "%" - это подстановочный знак.
Ссылка на MySQL, КАК: http://www.tutorialspoint.com/mysql/mysql-like-clause.htm
Вы также можете добавить столбец "ключевые слова" и сохранить ключевые слова в формате, разделенном запятыми (т.Е.: plarp1, plarp2, plarp3), а затем выполнить поиск по нему.