Fastest search of database

[poab]

Hi,

I have an access database with around 300 products. Each product has around 50 or so characteristics that differenciate it from all the others. I'm searching it with ASP using vbscript.

What I'd like to know is, am I better to have a different column for each characteristic with a simple yes/no, or would it be faster to put all related characteristics in a single olumn and use vbscripts InStr() method to find what's there?

At the moment I'm using the first method, but that's like 50 odd columns which must be a bad idea. On the other hand, using the InStr methed means that some of the columns are going to be really large.

What you you guys/gals think?

cheers.

[ChrisRC]

I've always found that the best way to find out is to actually test it. Run it both ways and see which is faster.

Are you searching on the characteristics to get the products, or the product to get the characteristics?

My inclination would be to have a seperate characteristics table with 1 record per item per characteristic. IOW, if I wanted to find stainless steel widgets: "SELECT widget FROM items INNER JOIN characterics ON items.id=characteristics.id WHERE characteristics.spec=stainless;"

Also, and this may not be news to you but I felt the need to point it out in case it is: Access really isn't appropriate for a production website. It's a great RAD front end, but the db itself wasn't designed for large scale concurrent access. MySQL is free* and runs great under Windows.

Chris
crcdesign.net (http://www.crcdesign.net)

[poab]

Hi,

thanks for the advice. As for access, I know it's a bad idea, but I've not been given the option.

cheers.