Menu
I've been searching stackoverflow for about an hour now and couldn't find any topics related, so I apologize if this is a duplicate question.
My inquiry is this. Is there a point at which there are too many tables in a database? Even if the structure is well organized, thought out, and perfectly facilitates the design intent? I have a database that is quickly approaching 40 tables - about 10 main ones, and over 30 ancillary tables (junction tables, 'enumeration' tables, etc).
Am I just a bad developer - or should I be trying something different? It seems like so many to me, I'm really afraid at how it will impact the performance of the project. I have done a lot of condensing where possible, grouped similar things where possible, etc.
The database is built in SQL Server 2008.
495
The problem with many tables is that each one multiplies the number of possible plans for the optimizer to evaluate (actually it's the number of Joins, not tables per se). At some point the optimizer runs out of time and will just use the best plan that it has so far, which can be pretty bad. So where is this point?
If the reason is that you think the limit on tables isn't that high, you're just wrong. The number of tables is limited only by the number of database objects, currently 2, 147, 483, 647. A couple of hundred tables isn't going to make a difference to anything except the clarity of your data model.
A common problem is the creation of tables that hold many different types of objects. If I look at one of your tables, and ask you what it holds, your answer shouldn't start with "It depends".
If you have a bunch of tables with the same prefix, chances are you're using the prefix to group them. That should be a schema instead. (BI people: I'm looking at you with all your DimSomething and FactSomething tables too). If the reason is that you think the limit on tables isn't that high, you're just wrong.
You should have exactly as many tables as you need; no more, no less.
One of the systems I'm working on these days has 143 tables - because that's exactly the number required to solve the problem.
199
LOL our main db has over 700 tables, I haven't worked with a database so tiny it only had 40 tables in years and years.
As long as you have the tables you need and they are correclty normalized, you are fine.
I've seen more performance problems caused by too few tables than too many.
44
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
