I'm curious to find out what kind of database schema craigslist and kijiji uses. They have many categories and cities that grow all the time. The levels also get deeper sometimes (sub-categories, and areas/neighborhoods).
The part I don't understand the most is how they manage their categories. What is the database structure behind their setup? They have so many categories and sub-categories, let's say somebody posts an item inside Cars -> Parts & Accessories > Racing Parts
A few days later, people request more sub-categories under "Racing Parts":
* Accessories * Auto Racing Parts * Fasteners, Fluids & Gaskets * Kart Racing Parts * Safety Equipment * Other
So now they have a new level for Racing parts that go like this:
* Motors -> Parts & Accessories > Racing Parts > Accessories * Motors -> Parts & Accessories > Racing Parts > Fasteners etc..
What happens with the existing listings that have been posted prior to adding the new subcategories? do they get moved to a sub-category table? What happens to the "Racing Parts" table, does it still store listings or does it just point to new tables now? Or maybe that's not how they do things at all. Thanks for your help!
A site with high traffic like that is not going to be the best example of well-normalized relational database structure, since it will be full of optimizations that are actually poor practice for smaller-scale systems.