What are the adverse effects of having too many tables in the database?

Question

Before I explain my question, I want to say that I know that this question was asked on SO before, but my question is on a completely different scale, and the situation seems to be fundamentally different from what I read on other issues.

Background:

I am doing some work for a client who has a database containing 2505 tables . These 2505 tables are made up of several hundred WordPress instance tables, so these tables don't need to talk to each other or anything else. It would also be easy to have 250 databases of 10 tables instead of one database of 2,505 tables.

What more: this particular application is currently used in only one US state, and the goal is to be used in all 50. So, presumably this means that ultimately it will be 2500 * 50 = 125,000 common tables . It amazes me as a sign of suboptimal design to express it frivolously.

The problem is that the client developer is so little versed in databases (for example, he does not know about normalization, foreign keys or unique restrictions), that it is a real problem to explain why 2505 tables in the database are not a good database design.

How do you explain to someone who knows little about databases that 2505 tables in one database is a bad idea? (I am looking for concrete, factual, compelling reasons.)

(By the way, I think that the root of the problem is the choice of the platform - WordPress is probably not the most suitable tool for working, but I want to solve the database problem first.)

Answer 1

Why don't you just check it and report the results of your test? Just clone existing production data 50 times into new tables and run some traffic against it.

This is incontrovertible, does not require simplification of metaphors and is not deceptive (as one of the other answers suggests).

Answer 2

If a person does not understand the database, the use of specific technical arguments will probably help little. It would be better to use what an ordinary person could (pretend) to understand, preferably in the presence of his boss (or send them an email with Cc: to his boss).

The boss is likely to react to the fact that storing many tables in one database is unsafe or simply not an industry standard. The resulting database will be slow, it may possibly crash, and this can make customers really pissed off.

Such communication is manipulative, but direct technical conversation is likely to lead to nothing. If the developer does not understand anything and still insists on disagreeing with you, then talking about good or bad design is useless (maybe even worse than useless), noobs sometimes take pride in using poor design because they have the ability to work with poor designs demonstrates their presumably leet skillz). You are going to convince the boss; therefore, you must speak a language that the boss understands. The boss wants to avoid the risk of failure of the project, and they will probably agree that the use of non-standard equipment increases the risk. Accurate technical evidence is probably not needed, expressing strong confidence that what you are saying works better (which is usually sad, but in this particular case it works for you).

Answer 3

Why don't you try and let him know that the database is like a cabinet with each table being a box.

Now ask him to look through a cabinet with 2505 or 125,000 drawers ... To make it more complex, he will not have a ladder or other means to reach the upper drawers.

Answer 4

If you are trying to explain to a non-technical person why such a database is bad, you cannot go into details like so many of us do.

Now, although it may (or may not) be true, you could use a few simple answers:

a) "If you have more tables, the commands (queries) for getting the date will be too complicated / large - this means that your system will not be so responsive / fast / etc."

or

b) "If something goes wrong or you need to change the database, you will have to pay more because it is not optimized, and whoever you pay for it will be harder"

Since I will explain it anyway.

Good luck

Answer 5

Money.

Developers cost money. Maintenance costs money.

Meet with a black board or send an email with something like the following:

  Developer Costs £ 500 per day

 Creating the tables behind the application
 -----------------------------------

 Time to create 1 table = 20 minutes
 Time to create 125,000 tables = 125,000 x 20 = 2,500,000 minutes
 Cost to create 125,000 tables = 2,500,000 / 60/8 * 500 = £ 2,604,166


 Updating the application
 -----------------------------------

 Time to update 1 table = 5 minutes
 Time to update 125,000 tables = 125,000 * 5 = 625,000 minutes
 Cost to update 125,000 tables = 625,000 / 60/8 * 500 = £ 651,041

 Cost in 1st month
 -----------------------------------
 £ 654,041 + £ 2,604,166 = £ 3.258m

Transfer to local currency and indicate that the “boss” will save a lot of money on maintenance every time they want to change something. Just to make sure you are not stuck doing this, nothing indicates that this is just one more time over normal procedures.