I am going to run WordPress Multisite which will have one custom post type. This custom post type will have many taxonomies (like 10-15). I will use this custom post type for product and taxonomies for product specification like color, size, product-specific specifications like RAM, CPU for servers, 3G/2G for mobile phones, etc.
I have done a lot of research on every aspect of my future website and while I've solved most of the tasks via plugins and/or custom solutions, it seems that biggest challenge is how WordPress handles databases.
My priorities are:
1. Search - end-users should be able to filter search results with product specifications, just like Amazon or any other ecommerce website. I've seen WordPress examples of this: Shopp ecommerce plugin supports faceted search, WooThemes have done same with some of their themes (http://demo2.woothemes.com/?name=estate)
2. Flexibility - this metadata should be flexible. For example, I should be able to show them in statistics in future. Chosen plugin supports statistics like Popular products, etc. but I'd like to provide statistics based on custom taxonomies - product specifications. For example - Top YELLOW Shirts.
I have read almost everything worthy about custom post types and when one should use custom database tables to store data as well as pros & cons of each decision.
It seems that Custom Post Types isn't quite flexible to do some complex things with metadata as they are stored in one table. Every single website said that but I wonder what complex means. Is what I need complex or not?
As with custom database tables - they say that you should avoid them unless you have no choice. They found it very complex and, at the same time, it's hard to cache them as they are outside of WordPress territory.
And about speed & performance - does one of these solutions have better effect on speed & performance than other one? For example, if I go with custom database tables, will that reduce load time when user tries to filter search results? What I have understood here (http://podsframework.org/docs/comparisons/compare-storage-types/) is that it does.
Please provide some suggestions no matter if they are basic or they answer to my questions exactly. Few sample questions:
What complexity exactly means in terms of creating custom database tables - is this nearly impossible for WordPress or is it just few more thousand dollars of development costs?
What will be cons (as I am aware with Pros already like built-in caching, etc.) of using Custom Post Types in scenario described above (filtering, etc.)?
Some key information: I will have at very maximum 1000 websites in my multisite but quantity of products (custom post types) might be millions.