One of the first things I noticed while installing Wordpress MS and creating my network was that 9 db tables are created for each new site. Obviously this means that if I had, for example, 10,000 users, I would end up with 90,000+ database tables. Since this could obviously be avoided (I think) by instead modifying the existing Wordpress database schema, why was the system designed this way? For compatability, ease of use, etc.? Because they don't expect to be able to support that many sites? I am trying to figure out if I should proceed with the system as-is or start hacking to make the db schema scale well. Should I be worried about potentially ending up with so many tables?
Yes, the code can support thousands of sites, and many users per site. Hardware will be the limiting factor.
To answer your other question of why was it done this way? It keeps the WordPress code base from having to be altered a LOT from when it was created. It is one of the important reasons that plugin authors and theme authors use $wpdb->prefix instead of assuming wp_ for table names.