db design - keyword fields
Date: Sun, 05 Sep 2004 23:42:56 GMT
Message-ID: <4wN_c.2148$Va5.1609_at_trnddc01>
I'm sure this is a common and simple situation: Say I have a title, url, description fields, assume they makeup a record in one table named book_info. Say for each of book_info's records I also need a keywords field (of datatype text) and I'll use a fulltext( Mysql) index on this field. Great, now the user does a search (w/mysql/php eg.) the keyword(s) are found and the associated records with hits display - a nice dynamic web page.
I assume it's OK to throw a bunch of keywords into a text field and use mysql's fulltext index on it in this fashion. It seems simple and easy to conceptualize this at least.
Question is, were do I put the keywords field? Do I add it to the end of a book_info record as another field then just query the keywords field in each book_info record and select everything to render except the keywords field when a match is found. Or, have a separate keyword table and have corresponding auto-increment PK fields on the book_info and keyword tables, and search the keyword_keyword field w/fulltext and then do a simple join on the PK's of each table to select the corresponding book_info results. Either way will work, what's better? Thanks, Lee G. Received on Mon Sep 06 2004 - 01:42:56 CEST