Re: Searching Titles Based on Keywords

Someone just asked for other folks $.02 - so I'm tossing in what I sent to the original requestor. cheers j. } } > } > 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; } } NEVER, EVER, EVER combine different data elements into one field. } } What I would suggest is: } } Master table } Title } Book_Id } } Dependent Table } Book_Id } Keyword } } Index Book_id in both tables and potentially keyword as well. } } Whenever you add a keyword to a title you link it using the Book_id - } you are then not limited to 5 keywords. Your search will be a whole lot } faster too. Example: } } Select title } from master_table, dependent_table } where master_table.book_id = dependent_table.book_id } and dependent_table.keyword matches 'Whatever'; } } If you turn this into a view then you don't need to worry about that } either: } } create book_view as } select * } from master_table, dependent_table } where master_table.book_id = dependent_table.book_id; } } Select title from book_view } where keyword matches 'Whatever'; } } cheers } j. ________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@boi.hp.com Currently on loan to PLD/PE ________________________________________________________________________ Outside of a dog a book is a man's best friend. Inside of a dog it's too dark to read. (Groucho Marx) ________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. ________________________________________________________________________