Designing a Sitecore tree to store more than 1Mn items

Question

I have 300+ "Category" (Can go into 4 sub levels) items and 1Mn+ "Product Items" to be stored in the Sitecore database. I am designing the Sitecore content tree.

I am considering two options,

Option1 - Model as "Category" --> Sub category .... --> "Product Items"

Option 2 - Model "Category" tree separately and have "Product Items" separately. (Home -> Categories -> cat 1...) and (Home -> Product Items -> All Product Items)

According to my experience if I go for option 1, it will be painfully slow if I need to modify the category items and publish. (Even with some enhancements)

Option 2 does not create a well balanced tree, but the category nodes will be much faster to edit.

Please help me to sort out this. I am using Sitecore 7.

Answer 1

You are more or less going to have to come up with a solution for storing your in a separate section of the content tree. I would most definitely not advise you to place a million+ product items sitting directly under the individual category items. For a number of reasons, including:

1. Performance, as you say. Changes to categories will happen often, to "follow the market", for "seo reasons". Also "campaign categories" (like "Summer Sale 2014") is bound to pop up from time to time
2. The nature of a category/product relationship is very rarely 1 to 1. Meaning the same product can and will exist in multiple categories (think: Duracell batteries existing in both "Electrical", "Batteries - Non-rechargeable" and "Camera Accessories" categories)

Since you're on Sitecore 7, the built-in solution for your product repository is to put all the products into an Item Bucket. Then you have two (obvious) choices for Building the product/category relationship:

1. On "Product", set up a Multilist field named "Categories", and add the ID of each Category item the product belongs to
2. On "Category", set up a search field to select products from your Item Bucket

It really depends on how this product hierarchy is going to be managed, which of the above fits best.

Information on Item Buckets here: http://sdn.sitecore.net/Reference/Sitecore%207/Developers%20Guide%20to%20Item%20Buckets%20and%20Search.aspx