Requirements:
- MySQL user access, with privileges to create a database
- (optional) MySQL WorkBench
The first step in creating the database to store information for searching, is deciding on the desired features of the search engine. This is also a good time to figure out exactly what the search query will be, so that the database can be constructed in a way that makes it efficient.
The feature list decided upon for this tutorial is as follows:
- The search engine should take as input a string of space delimited keywords.
- The keywords will each be compared to page contents individually (no capability of searching for phrases)
- Keywords being searched for will be considered matched if the word is contained on the page in any form. (Similar to the common text editor "find" function, with "match whole word only" unchecked)
- Results will be sorted according to the number of word matches, with duplicate words on the page, or in the search string ignored. (The search string "search this" would return the same order as "search this this")
- Results of a search will be a table, ordered as described above, with columns containing the urls, titles, and descriptions of pages on which matches were found.
With the feature list created, it's now possible to determine the requirements of the database. Following is an intuitive breakdown of deciding how to create the data structure of this database:
- The 4 pieces of data needed to perform a search are: Page URL, Page Title, Page Description, and Words Contained on the Page.
- Page URL is unique to each page, and can be used to determine the other 3 properties, so 'page_url' will be the Primary Key of the table PAGE. Page Title and Page Description are values that only occur once per page, and are usually unique on each page. They can simply be added to table PAGE as columns 'page_title' and 'page_desc'.
Database Looks Like:
PAGE(page_url, page_title,page_desc)
PAGE(page_url, page_title,page_desc)
- "Words contained on the Page" is more complicated, because it has multiple words per page, and each word be contained on more than one page. A new table called TERM is created with columns page_url and keyword. Also, now that page_url is contained in both tables, an id column will be added to PAGE, and page_url in table TERM will be replaced with an id.
Database Looks Like:
PAGE(page_id, page_url, page_title, page_desc)
TERM(page_id, keyword)
PAGE(page_id, page_url, page_title, page_desc)
TERM(page_id, keyword)
- Now the table TERM will have a large amount of duplicate data in each column, because many words occur on more than one page, and many pages contain more than one word. To fix this, a table called KEYWORD is created, and the column 'keyword' in table TERM is replaced with an id that points to an entry in KEYWORD.
Database Looks Like:
PAGE(page_id, page_url, page_title, page_desc)
TERM(page_id, key_id)
KEYWORD(key_id, keyword)
PAGE(page_id, page_url, page_title, page_desc)
TERM(page_id, key_id)
KEYWORD(key_id, keyword)
Pros and Cons of Database:
The database is now the ideal structure to meet our search requirements. There are some definite advantages of splitting up (normalizing) the database this way:
- When a search is performed, search terms can be compared to the words in KEYWORD, if 'keyword' had been left as a column in TERM then multiple duplicate comparisons would have been made.
- Using number id's for words and pages will cut down the size of the database by eliminating VARCHAR's stored in multiple places.
- Using number id's limits the max number of unique pages and words that can be stored to the max size of the number used. On a personal scale site this shouldn't be an issue, so it is an acceptable limitation.
- The id's in this database are foreign keys (they are used to point to data in another table). In MySQL, InnoDB is the only database engine that supports foreign key constraints. More on this later.
The following commands can be entered into a MySQL command client to create the page_search database. Alternatively, the file 'page_search.sql' can be found here. It can be loaded and used from the MySQL command client using the command '\. [filename]' (without quotes).
CREATE SCHEMA IF NOT EXISTS `page_search` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;This tutorial won't go into depth on what this MySQL code means because it is just standard SQL and it follows the plan for the requirements laid out in this tutorial. One thing to mention, if InnoDB is not enabled for your MySQL Server, the FOREIGN KEY statements will be ignored. Because this series of tutorials doesn't do any deleting of values or updating of foreign keys it should behave the same way out of InnoDB. However if you expand the code, keep in mind that MySQL will not use the foreign key constraints unless the table engine is InnoDB.
USE `page_search`;
CREATE TABLE IF NOT EXISTS `page_search`.`PAGE` (
`page_id` INT NOT NULL AUTO_INCREMENT ,
`page_url` VARCHAR(255) NOT NULL ,
`page_title` VARCHAR(255) NULL ,
`page_desc` VARCHAR(255) NULL ,
UNIQUE INDEX `UNIQUE` (`page_url` ASC) ,
PRIMARY KEY (`page_id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `page_search`.`KEYWORD` (
`key_id` INT NOT NULL AUTO_INCREMENT ,
`keyword` VARCHAR(26) NOT NULL ,
PRIMARY KEY (`key_id`) ,
UNIQUE INDEX `UNIQUE` (`keyword` ASC) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `page_search`.`TERM` (
`page_id` INT NOT NULL ,
`key_id` INT NOT NULL ,
PRIMARY KEY (`page_id`, `key_id`) ,
INDEX `KEY_F` (`key_id` ASC) ,
INDEX `KEY_P` (`page_id` ASC) ,
CONSTRAINT `KEY_F`
FOREIGN KEY (`key_id` )
REFERENCES `page_search`.`KEYWORD` (`key_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `KEY_P`
FOREIGN KEY (`page_id` )
REFERENCES `page_search`.`PAGE` (`page_id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
Finally, you can download the sql and MySQL Workbench files for this tutorial here.
The source code for all 5 tutorials in this series can be found here.
Tutorials in this Series:
1. Defining a MySQL Database to be Populated / Searched Using PHP
2. Filling the MySQL Search Keyword Database
3. Parsing Webpage Content Into Keywords
4. Creating the Search Query Class
5. Creating the User Interface
