Is there such a thing as too many tables?

views:

186

answers:

+7 Q:

Is there such a thing as too many tables?

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.

+1 A:

It really depends on the complexity of the application you're trying to implement. Things such as accounting systems are pretty intense had easily reach 40+ tables.

blcArmadillo 2010-03-20 05:11:18

+9 A:

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.

Steven A. Lowe 2010-03-20 05:12:55

I agree with your first statement, but to be a bit pedantic, I find it hard to believe that you **couldn't** have addressed the problem with 142 -- perhaps not as elegantly or with the same extensibility or performance or whatever, but still! :)

Chris Farmer 2010-03-20 05:16:28

@Chris That makes no sense. By your logic, you could say that about your 142 tables, saying it should be done in 141. You could continually say that until when .... you are down to 1 table?

Kevin Crowell 2010-03-20 05:18:45

@Kevin: an EAV table :)

Stefano Borini 2010-03-20 05:22:29

@Kevin: yes, any RDB design can be modeled in 1 table. horribly and inefficiently; but mathematically possible.

Javier 2010-03-20 05:24:01

Well, sure. You **could** do that. You could have a very wide table with a bunch of redundancy. You could implement the functionality you wanted with views on that one table. Is it a good idea? Of course not. But could it conceivably solve a business problem? I think so, at least until the requirements slightly change. Doesn't everyone know a db developer who liked to put everything into a big spreadsheet like this?!I want to make it clear that I think this is a dumb idea, and I hate myself for arguing this point, but I think it's wrong to say that it's the fewest # of tables required.

Chris Farmer 2010-03-20 05:25:10

I think he means "exactly the number required to solve the problem" with certain constraints, such as normalization.

Matthew Flaschen 2010-03-20 05:26:33

@Matthew -- that's the reason I said I was being pedantic. I agree that Steven's answer is a good one. He is correct that the business problem defines the boundaries here.

Chris Farmer 2010-03-20 05:30:15

@Chris Less than 143 tables would violate 3NF. More that 143 tables would be unnecessary/redundant. Until the requirements change ;-)

Steven A. Lowe 2010-03-20 05:46:05

Make that 144 tables - the requirements just changed slightly.

Steven A. Lowe 2010-03-20 05:54:16

Is it bad if the first thing I thought was "ooh, 143. One more and it'd be a perfect square!", and then, after that last comment, "YES! 144 TABLES!"?

Carson Myers 2010-03-20 05:55:52

@Carson no that's perfectly normal. For a calculator! ;-)You may also be excited to know that one of the other applications I'm working on has exactly 9 tables. [If you're really enthusiastic about math, does that make you a square rooter?]

Steven A. Lowe 2010-03-20 07:09:41

@Steven, I'm not as excited about 9 tables, usually it's the whole 16, 32, 64, 128, 256, 512, 1024, etc thing. But 144 did it too for some reason. Generally I'm not THAT enthusiastic about math... Though I did enjoy logarithms...

Carson Myers 2010-03-20 07:13:36

@Carson if you're excited about 144 but not 9, that's just gross ;-)

Steven A. Lowe 2010-03-20 07:26:04

@Steven uh oh... I feel like I missed an innuendo somewhere

Carson Myers 2010-03-20 07:44:00

@Carson see second definition at http://www.mathsisfun.com/definitions/gross.html ;-)

Steven A. Lowe 2010-03-20 08:01:19

@Steven I did not know that meaning of gross

Carson Myers 2010-03-20 17:30:09

+1 A:

Without knowing anything about your specific database, I'd say that no, you are not using too many tables. Real world problems and business needs can easily point to a schema that's at least as large as yours. I think the real question to ask is whether your design is right for your problem.

Chris Farmer 2010-03-20 05:13:13

Well, most of my tables are junction tables in an effort to psuedo-inherit from other 'types'. For example, We have a "Products" table and an "Items" table - but they are very similar, so I make another table called "Details", map both products and items to individual junction tables to it, and then put the unique items in Products and Items tables. This kind of scenario is where MOST of my extra tables come from.

Stacey 2010-03-20 21:15:14

If you think there might be some unnecessary redundancy between Products and Items, I suggest you start another question with those specific issues.

Chris Farmer 2010-03-20 21:48:49

Alright, I've posted another question about it. Thanks again. http://stackoverflow.com/questions/2485253/unnecessary-redundancy-with-tables

Stacey 2010-03-20 23:59:11

+2 A:

It seems like you're making your best effort to normalize your database. That's a good thing. Many times problems arise because there are not enough tables.

Giovanni Galbo 2010-03-20 05:18:53

Good point about problems from not enough tables.

HLGEM 2010-03-20 15:59:49

+1 A:

There is such a thing as too many tables, but 40 is nothing like that number. And when people start butting into product limits, then it's usually the point when they need to rethink their design.

For SQL server, the maximum capacity limits tell you that a DB can contain ~2000000000 tables (if it contains nothing else, has no PKs or constraints of any kind, etc). Needless to say, if you hit this limit, then you're doing something wrong (e.g. you've decided to have 1 table per customer, and somehow you've actually gained a lot of customers)

Damien_The_Unbeliever 2010-03-20 05:27:27

In my own job i have about 60 tables and it seems not much) I think that main thing is how datastore organized (relations beetween tables, etc..), how much queries you need to retrieve needed information and how simple you data can be represent as buisness objects in your application.

Alexander 2010-03-20 05:32:58

You may put all the data from your actual tables in one table. To the second table may hold just your 'table names', and the application will still work.

But this is not the point here.
Tables are some kind of organization structure. They are some kind of drawers.

Do I have too many drawers?

It depends…

takeshin 2010-03-20 05:43:36

+2 A:

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.

HLGEM 2010-03-20 15:59:13

2147483648 tables or more might be problematic with some engines. 9223372036854775808 tables or more might be problematic with certain others.

(But if your question meant whether there exists a certain number n such that a database design with >n tables must necessarily be flawed, then no.)

Erwin Smout 2010-03-23 22:32:51

ansaurus

tags:

views:

answers:

Is there such a thing as too many tables?

related questions