Re: Searching Titles Based on Keywords

Yudong Sun <ysun@econnect.ca> wrote: :Here is a design question: :I have a database of book titles each associated with several :keywords. How should I organize to have the best performance: :- keywords associated with each title share one field separated :by comma; :- each keyword occupies one field - the BOOK table contains :five keyword fields: KEYWORD1, KEYWORD2, ... :Could someone tell me if the first approach is efficient enough :(assuming it's indexed normally)? Thanks in anticipation. I assume you want someone to search for the books by keyword. You might want to have a separate keyword table containing the primary key from your book table and one keyword field. Your first approach won't work (if there is a significant number of books) because you would have to search with matches "*keyword*" which can't use any index. The order of keywords is also a problem if you allow your users to search on multiple keywords. matches "*keyword1*keyword2*" would not work as the order in the field may be keyword2,keyword1. Your second approach with five fields for keywords isn't exactly a relational database design. It's repeting fields. It also gets impossible as you would allways have to search for every keyword the user types in in every field. My approach with a separate table of course gives a problem if users are allowed to search for two or more keywords that both should be present. It's solvable but you would have to generate some fancy SQL for it. It's still the only approach I can see. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org