Best DB design for keyword search 1

[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!

 

[fluteplr]

I would go with the child table approach. Also consider getting rid of Access and converting to MSDE.