Hi there everyone!
I've written an e-commerce platform and it's time to make the search function more robust(it's currently just a bunch of %like% searches on various fields). I've got some thoughts on the matter but wanted to see if anyone might have some better ideas concerning it's design.
My fuzzy plan:
I would use a table specifically for word matches, and it would have the product id, word and weight cells. I'd change the product insertion to go through the various fields(part number, description, specifications, etc) finding the unique strings to insert into the search table, once it' finds a duplicate of a string, it would increment the previously inserted row by 1. strings like the part number would get an instant weight of 100 or similar to make sure it would go to the top of the list.
On the search side, a search would create an array of part ID's that match the search. The array would get sorted by weight DESC and then saved to a database with a unique ID so I could paginate through it for the user.
This is my (very) rough idea of how to handle this and I may be way off base on the best way to handle this so I thought I'd ask if there's a more accepted method of handling something like this. Anyone's thoughts on the matter are most welcome!