Best Database Design for Keyword Search

[DanX]

We have an online company directory with 1300 categorized entries. Now we can only search by company name and by drilling through categories. We want to add keyword searching. 1) we are in Access now but contemplating MSSQL. $5000 for the license is no joke however and since this is not a very demanding application otherwise (static, no inserts, only one join - unless we add another for keywords?) do we really need it? Will we start to bog down if we have say 100 words x 1000 companies? Advantages to use MSSQL full text search? 2) Is it better to dump all keywords in one field and search with instr() or construct a "many" table with keyword and company id? 3) Can I use instead just the MSDE, and create an index off-line for it? Thanks for sharing your experience!

 

[ghubbell]

Hi Dan,
You have Access now. Try some tests! How about a "dictionary" table: Keyword (No dups), KeywordID. A second table CustomerID,KeyWordID,CustKeyID. Indexed as best required. I figure to save five grand it's worth a shot. I have some similar searchs running 4000 customers against 15000 items. A bit pokey on some machines, but do-able... Plus, if you haven't already figured out, I like Access and like to push Access as far as it will go! Just ideas!...... Gord
ghubbell@total.net