When do you have too many tables?

Question

Two of my colleagues and I are building a system to do all sorts of hydrology and related stuff. It has a lot of requirements and have a good number of tables.

We are handling all sorts of sampling that it is done within this scope (hydrology) and we are trying to figure out a way to do it in a less painful way.

Sometimes we need to get all that sampling together and I'm starting to think we are over-complicating our database design.

How or when do you know that you are over-designing a database? Of course we are considering a lot of Normal Form Rules and other good practices, but when it is OK to drop one of those rules, e.g. not normalizing something?

What are your opinions on this?

Answer 1

We have a system with literally hundreds of tables - its no big deal, its just that a lot of different things are stored in the database.

Answer 2

Short Answer

You can't, worry about something else.

Long Answer

This sounds like yet another form of premature optimization. (YAFPO?)

You should design your schema using third normal form (3NF). Once designed, you should populate your tables with data and begin profiling.

If a particular query is deemed too costly then you should look into denormalization on a case by case basis.

Technical Answer (for the nitpickers who will inevitably object to: "you can't")

You will reach a limit at some point based on your choice of RDBMS and/or storage engine. Likely ceilings will be memory consumption or open file descriptors.

Answer 3

We have a ton of tables in our system as well. What we did was normalize the database to a good point, then created a few views that encompass the most common table usage needs of our system. Something like that could help you as well.

Answer 4

"When do you have too many tables?"

At the level of logical design, the correct answer is "never".

At the level of physical design (insofar as "having a table" really refers to some concept that pertains to the physical design), the correct answer is "if and when the queries that you need to do, given the restrictions of the DBMS you are using, are causing performance to be unacceptably low.".