Hi everyone,

I was wondering if I would be able to get some of your advice on a site I am designing a searchable database for. Articles and documents are what will be stored in the database primarily, and so I am trying to make it searchable by many different criteria (basically anything I deem practically searchable). I just thought that I would seek the guidance of ye the more experienced when it comes to how I optimize the searchability of my database.

The following are the tables that I have set out as the structure of my database, in striving to make it as searchable as possible, and to optimize how it searches. I am not very learned in the art of indexing, so it is likely that someone will figure out a better way in which it ought to be done. Thanks a bunch, -XGuy. (P.S. - Every field that I want to be searchable has a * to the left-hand side of its name).


Author Table

*AuthorID (Primary Key) ->Type=SmallINT
*AuthorFirstName ->Type=CHAR
*AuthorLastName ->Type=CHAR
(should FirstName and LastName be indexed together? That was my initial instinct)
Suffixes ->Type=SET (e.g.- Mr., Dr., M.D., Ph.D., etc.)
{how would I go about displaying all the matches for the suffix of an author...would I just reference the numbers in the set in order to display it? Say an author is both a Ph.D and an M.D., so that is would come out of the search after their name displaying Ph.D., M.D.}

Article Table

*ArticleID (Primary Key) ->Type=SmallINT
AuthorID -> Type=SmallINT (Indexed)
*PublicationDate -> Type=Year or Date (undecided)
*ArticleType -> Type=ENUM (e.g. - articles, government documents, etc.)
*Country (of origin) -> Type=ENUM

(for both the ArticleType and Country fields, the search results could be displayed by the PHP by referencing to the number of the ENUM value, right? E.g. - ArticleType=2, so it would display government document in the search result for that field?)

DocumentData Table

ArticleID (Primary Key) -> Type=SmallINT
PDF File locations -> Type=CHAR
Links (to any HTML versions of the articles) -> Type=CHAR (would be formatted by the PHP as htmlspecialchars when served up to the client doing the searching)
*Keywords ->Type=SET (various topic keywords that articles could fall under one or more of)
Comments ->Type=CHAR or TEXT (site administrators comments, if any about the article)
Description ->Type=CHAR (brief summary of article)

My sincere thanks again, everyone!!!