I've inherited a company application that uses 58 tables per "object", and the application can have N objects. We are looking at an install of 75 - 100 objects for an application, which is 4300-5000 tables.
What we are trying to figure out is do we want to use one database and prefix the table names per object, or use one database per object (the application supports both). The only difference would be for each install of the application, we'd need additional mysql instances on different ports if we were to do per database.
Has anyone done anything similar? Are there any issues (outside of management) of having 4000+ tables in a database?
Edit
Thanks for the updates. As to a bunch of the comments
1) The company pays very well...I'd be dumb not to be taking this job. I wish just writing great code put the $$ in my bank account
2) Our clients are happy with the product. We've thought about re-writing it, but aside from the costs, we'd miss the market. While the structure is bad, the app works better than what most clients have.
3) Object isn't the best term...it's not like an object/class, but objects inside the application. I guess I can just say bucket instead.
If you're repeating 58 tables a hundred times, I'd suggest that normalization rules have been trampled on. It's not likely that your company will revisit the schema design for this product, but I'd recommend it based on the information you've provided.
Don't make it worse by distributing the databases. How can latency help?
MySql stores each table as a file, and there is no limit aside from your OS and your hard drive. However, there's a reason that's not commonly discussed- having thousands of tables is almost certainly the wrong thing to do, your database schema is probably badly in need of redesign.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With