Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Web Design & Development > PHP/MySQL/Scripting

  • Print
  • + Share This
From the author of

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.

  • + Share This
  • 🔖 Save To Your Account