I am creating an eCommerce site that needs to have a faceted search tool so that customers can narrow down a product search via categories and classifications in the same style as ebuyer.com and Newegg.com (See left hand menus).
I initially dived straight into designing a database that ended up similar to an EAV structure (I didn't know what this was at the time), this initially seemed ideal as I could create unlimited categories, subcategories and other product classifications (i.e. Colour, Size, Recipient) that customers could use to find specific products. However, when I started trying to create SQL queries using AND conditions, I realised how normal simple queries became much longer and complex to write.
After spending a few hours reading through various posts on SO and articles on Google, I've come to realise the nightmare that would lye ahead if I continued with this method.
Question
How have sites like ebuyer.com and Newegg.com designed their faceted search?
Have I missed an alternative method or have they simply gone ahead with an EAV structure? I'm looking to avoid enterprise solutions like Lucene/Solr.