How to Handle 2 Million Products

Question

I work for a web company and we currently use a highly modified version of OSCommerce as our primary ecommerce application, but recently we have been approached by a number of companies with over 2 million individual product models they want to sell online.

Essentially my question is - are there any prebuilt PHP/MySQL shopping applications that are going to handle this many products gracefully or am I out of luck on that front? Am I going to need to create a custom app? What are my options here?

Would a nosql database be better than MySQL?

Answer 1

Essentially my question is - are there any prebuilt PHP/MySQL shopping applications that are going to handle this many products gracefully

No. If there was these companies with the 2,000,000 products would be using it.

Am I going to need to create a custom app?

You already have. You started with OS Commerce as a base and built a custom application on top of it. You may not think of it as an application, but it is.

What are my options here?

Accept that you'll need a decent IT/Development team to chase after this work, and asses if that cost and R&D is worth going after this new business.

Would a nosql database be better than MySQL?

No. But a MySQL database wouldn't be better than a "nosql" database either.

Answer 2

You definitely want to build a custom solution, and you definitely want to charge a lot more than you normally would because there's a lot of risk and due diligence needed.

As for your architecture, using NoSQL is possible and there are some compelling reasons behind using NoSQL for ecommerce - the main reason being that it is schemaless and if you have a ton of categories and a ton of products which all need to be sold differently (ie you sell computers differently than you sell watches) because the product attributes are different, managing database complexity becomes really important.

This video will show you what a really forward thinking start-up in NYC is doing. They're using MongoDB for their entire product database. This video should be a real eye opener as it outlines a lot of the pitfalls in MySQL for big ecommerce sites, and alot of the game-changing potential of NoSQL:

http://engineering.shopopensky.com/topics/mongodb

As for handling payments, you definitely do NOT want to store those in NoSQL. Keep your users, sessions, and payment data in MySQL and make sure it's highly secured. Here is a great (even though old) piece on securing sessions in PHP applications:

http://www.troubleshooters.com/codecorn/php/persist.htm

As a note, this last link should help you understand the theory better. Most PHP frameworks support this type of session handling out of the box. CodeIgniter, Yii, and ZendFramework are among the best.