I hope my english is enough to explain my problem and i hope someone has an answer for me.
Say we have a lot of Links for cars. Say we have cats for 120 different colors.
Say every Link is in around 20 till 40 categories, means could have these colors.
Now i have the following ideas how to search with a form:
1. Save all catnames in the dataset of a Link and use the search.cgi, my form sends the color names and everthing runs like normal.
b) Instead of words for the colors i save shortnames like col1, col2 to save chars.
2. Make a SELECT like: Select from Links where Link is in the following cats. SELECT FROM Links, CatLinks, Category where ... CategoryID IN [..]
3. Instead of Names of the cats i store only the ids of the cats in Links.colors and make a query (with php) like SELECT FROM Links where x,y,z is in Links.colors.
My idea:
1. I will have a lot of chars in my table; for 1000 cars with 20-40 colors and in my language 5-15 chars per color.
1b. The same but less chars in the table.
2. This query seems to be a hard one, i believe in 10.000 visitors and more in a day and they will use this search very often.
3. Lighter query i think, but there is no indexing for the textfield. (i have used this for a smaller site, where i have only 20 cars, so the search never has to watch for more than this 20.)
I hope someone has an idea what i should do. :)
Say we have a lot of Links for cars. Say we have cats for 120 different colors.
Say every Link is in around 20 till 40 categories, means could have these colors.
Now i have the following ideas how to search with a form:
1. Save all catnames in the dataset of a Link and use the search.cgi, my form sends the color names and everthing runs like normal.
b) Instead of words for the colors i save shortnames like col1, col2 to save chars.
2. Make a SELECT like: Select from Links where Link is in the following cats. SELECT FROM Links, CatLinks, Category where ... CategoryID IN [..]
3. Instead of Names of the cats i store only the ids of the cats in Links.colors and make a query (with php) like SELECT FROM Links where x,y,z is in Links.colors.
My idea:
1. I will have a lot of chars in my table; for 1000 cars with 20-40 colors and in my language 5-15 chars per color.
1b. The same but less chars in the table.
2. This query seems to be a hard one, i believe in 10.000 visitors and more in a day and they will use this search very often.
3. Lighter query i think, but there is no indexing for the textfield. (i have used this for a smaller site, where i have only 20 cars, so the search never has to watch for more than this 20.)
I hope someone has an idea what i should do. :)