Creating the Tables
A full search engine works by creating an index that associates three things: the unique pages on a site, the unique words on a site, and how many times each word appears on each page. Those tables can be defined as:
CREATE TABLE se_pages ( page_id INT UNSIGNED NOT NULL AUTO_INCREMENT, page_url VARCHAR(100) NOT NULL, PRIMARY KEY (page_id), UNIQUE (page_url) ); CREATE TABLE se_words ( word_id INT UNSIGNED NOT NULL AUTO_INCREMENT, word VARCHAR(25) NOT NULL, PRIMARY KEY (word_id), UNIQUE (word) ); CREATE TABLE se_pages_words ( page_id INT UNSIGNED NOT NULL, word_id INT UNSIGNED NOT NULL, frequency TINYINT UNSIGNED NOT NULL, PRIMARY KEY (page_id, word_id) );
Each unique page on the site gets represented as a record in the se_pages table. Each unique word gets represented as a record in the se_words table. And the se_pages_words table is an intermediary between those two, reflecting the number of times a word appears on a given page. This table will allow the system to rank search results appropriately.