I’m installing and modifying WordPress MU (Multi-user) for a client and am amazed at the poor database design. For each blog you set up, it generates 8 new tables for that blog, which have an identical design to the same 8 tables for every other blog it creates. This is extremely poor design according to any database design standards.  Despite the poor design, they do have a good reason for doing it. Quoted from http://mu.wordpress.org/faq/
Does it scale? (Also: The way you do your databases and tables doesn’t scale!)
WordPress MU creates tables for each blog, which is the system we found worked best for plugin compatibility and scaling after lots of testing and trial and error. This takes advantage of existing OS-level and MySQL query caches and also makes it infinitely easier to segment user data, which is what all services that grow beyond a single box eventually have to do. We’re practical folks, so we’ll use whatever works best, and for the 400k and counting on WordPress.com, MU has been a champ.
The main reason for doing this is that it makes compatibility with existing WordPress plugins much easier. I guess the real source of the problem was poor planning and foresight during the development of the original WordPress application. They claim it works well, so even though I cringe every time I see it, I guess I’ll just have to live with it and complain.
Hi,
I recently installed WP Mu on my site and had the exact same thoughts when I saw the DB design. However, I am not a DB programmer. I would love to know your thoughts on what WOULD be a good design for WP Mu. And how it might work!
Thanks!
Aoleon.
Instead of having a bunch of separate tables, I’d generalize each of the tables, add add a ‘blog_id’ column that would associate a particular comment/user/etc, to a specific blog.
With that being said, the way theirs currently works does actually scale well with hundreds of thousands of users spread across multiple database servers. If you were to do it the ‘traditional’ way, you’d end up needing a very hefty database server because it would would be searching through tens of millions of rows sometimes.
Its funny going back and reading my own blog posts 15 years later.
I’ve built a lot of systems now using the “sharded” structure that I was annoyed at with this blog post! It scales amazingly well to spread out customers into their own databases and separate database servers!