Thursday, March 12, 2009
Normalization and smoking
Salle gave the first answer. Converting the tables to MyISAM would only exacerbate the problem. Instead of having one file per table, the user would have 3 per table. Any operating system would choke with such a number of files concentrated in a handful of directories.
My answer focused on normalization. I explained that, if 10 different table structures are used, then the user should create only 10 tables, add a column for user ID, and export the 40,000 tables into the 10 tables. When I explained that this reorganization would improve performance a lot, but it would require changing the application, the user tried to protest, asking for some silver bullet solution. Then I delivered the punch line: "Your business is growing, and as it grows, you are adding new table for every new user. If you continue like this, there will be so many tables that your operating system will collapse. Your database is doomed. It's like if your doctor tells you that if you don't quit smoking, you will die in six months. The same for your database. If you don't quit creating tables, your database will die unpleasantly. Quit smoking, now!"
That made quite an impression on the audience!