53

Should we create a database structure with a minimum number of tables?

Should it be designed in a way that everything stays in one place or is it okay to have more tables?

Will it in anyway affect anything?

I am asking this question because a friend of mine modified some database structure in mediaWiki. In the end, instead of 20 tables he was using only 8, and it took him 8 months to do that (it was his college assignment).

EDIT

I am concluding the answer as: size of the tables does NOT matter, until the case is exceptional; in which case the denormalization may help.

Thanks to everyone for the answers.

9
  • 15
    Minimum number of tables is easy, just serialize the whole to master_table (table_name, col_name, col_type, row_id, value).
    – Inca
    Commented Jun 13, 2011 at 16:42
  • what? i am not getting it
    – Shaheer
    Commented Jun 13, 2011 at 16:45
  • 12
    Since every field in a database is defined by the combination of table name, column name, primary key and value, you can always reduce the number of tables by denormalizing into a single table that stores just that. Not very useful, but entirely possible.
    – Inca
    Commented Jun 13, 2011 at 16:52
  • well i was asking for the sake of knowing, and if something is less useful than the existing one, why bother changing it? i mean will it provide any improvement in anything? performance for example?
    – Shaheer
    Commented Jun 13, 2011 at 16:59
  • 1
    @Hamza: It might provide improved performance. It really depends on the specific circumstances. There's not nearly enough information here for us to provide a concrete answer.
    – FrustratedWithFormsDesigner
    Commented Jun 13, 2011 at 17:13

13 Answers 13

Reset to default
156

IGNORE the number of tables. Worry more about getting the design correct. If your major concern is quantity of tables, you should probably not be designing database systems.

If your friend only needed 8 tables, and the system works fine with that, then 8 is the correct number, and the remaining 12 might not have been necessary for whatever he was doing.

Possible exceptions might be peculiar environments that have hard limits on table numbers, but I can't think of a concrete example of such a system off the top of my head.

10
  • 108
    +1: If your major concern is quantity of tables, you should probably not be designing database systems.
    – Joel Etherton
    Commented Jun 13, 2011 at 15:28
  • 9
    Corollary: A database table doesn't take up [much] extra space. It's the data that takes up space. Normalization = more tables = less repetition = less space used. By trying to minimize the number of tables you not only compromise the design, you actually waste space. This "table golf" is just bad all around, unless some of the tables are literally redundant.
    – Aaronaught
    Commented Jun 13, 2011 at 21:58
  • 1
    +1, though I don't think we know enough to say that the correct number is 8 in his case, as we can't compare the schemas (the original might stand up better to higher transactional volume than the application currently has, for example)
    – Adam Robinson
    Commented Jun 13, 2011 at 22:51
  • 2
    @Hamza: Ok, so he might have good PHP skills and good database skills, and that project might require both - but don't make the assumption that having one automatically implies the other. Many developers may have one skill but not the other.
    – FrustratedWithFormsDesigner
    Commented Jun 14, 2011 at 14:39
  • 4
    @Tom Anderson - Then you still shouldn't be designing database systems.
    – Joel Etherton
    Commented Jun 20, 2011 at 14:16
72

A database should have exactly as many tables as it needs. No fewer, no more.

1
  • 3
    english.stackexchange.com/questions/495/less-vs-fewer Not to turn this into a discussion, but here is an interesting discussion on the "less" vs. "fewer" debate, including its origins, from the English Language SE, since it seems to enthuse you guys ;)
    – Corey
    Commented Jun 14, 2011 at 15:45
17

Database tables should adhere to the Single Responsibility Principle, just as classes should. Each table should deal with no more than one group of related data to start with. Performance aside, this make the whole beast easier to manage, because the tables themselves will be smaller. This gives you better performance as well, because smaller tables are faster to search and join.

Don't worry about the number of tables any more than you worry about the number of classes - don't worry at all. Focus on making good, clean, readable code, not on how much space it takes up. Refactor aggressively once you have a working product to make it better - and I do mean the database, too! You will see columns that should be in other tables, or aren't needed, etc. Profile to see what queries are taking the longest and why, and address those issues if they're really a problem.

8
  • 4
    In a normalized data model yes this is the best approach, however if the database is meant for reporting or primarily read access then denormalized "flattened" tables will perform better on large sets of data. A smaller number of tables in this case will result in less joins and better performance.
    – maple_shaft ♦
    Commented Jun 13, 2011 at 15:19
  • 2
    @maple Absolutely agree. You have to profile to determine what sets of data need to be grouped though, so IMO you need to start normalized. YMMV, experts probably can do it off the top of their heads :) Jeff has a post about denormalizing you may find interesting, too.
    – Michael K
    Commented Jun 13, 2011 at 15:24
  • 1
    Good and succint post, I have read this one before! Sometimes you can leverage the best of both worlds. If reporting doesn't need to be 100% real-time then maintain two schemas, one main schema being the transactional normalized schema for application use, and the other a denormalized schema that is streamed regularly and tailored for reporting data access.
    – maple_shaft ♦
    Commented Jun 13, 2011 at 15:33
  • 1
    More information on the subject with an explanation of Star Schema: publib.boulder.ibm.com/infocenter/rbhelp/v6r3/…
    – maple_shaft ♦
    Commented Jun 13, 2011 at 15:34
  • 1
    @maple_shaft, I agree that reporting database are often denomalized for performance, but they are not something I would expect a student or junior programmer to be allowed to take on. I know I certainly would not allow my data warehouses to be handled by anyone who did not have proven expertise.
    – HLGEM
    Commented Jun 13, 2011 at 16:20
7

A production database for a business application may contain hundreds or even thousands of tables. You need the number of tables you need for the business requirements. Trying to reduce the number of tables just for the sake of having fewer tables will usually result in a database that is harder to query, has data integrity issues and is much harder to maintian than a normalized database.

There are times when denormalization is needed. This should only be done by someone who knows exactly what she/he is doing and why. It is very easy to muck up denomalizing so it should only be done by a database specialist or senior application developer with years of database experience. An inexperienced person should be striving to, at a minimum, reach the third normal form (unless you are doing data warehousing which is an area that I would not consider hiring an inexperienced person for) in any database he/she designs.

When people say reduce the tables because joins are expensive, they generally are ignorant or have badly designed databases that are missing critical indexes or use large mulit-column natural keys. Relational databases are designed to use joins and joins can be quite efficient if the FKs are properly indexed and they use small fields to join on (integers are most efficient). You will note that the large businesses that have terrabyte-sized databases somehow manage to get excellent performance and use joins.

No serious database designer ever tries to reduce the number of tables just because they want fewer tables. You reduce the number of tables because the data is no longer needed or you have a performance issue you can't solve any other way (and there are lots of ways to try before taking on the extensive risk to your data of denormalizing a table).

3
  • Google designed BigTable and deliberately excluded the joins since it isn't parallelizable.
    – Lie Ryan
    Commented Jun 13, 2011 at 18:02
  • 2
    @Lie Ryan, BigTable is a special case that is NOT appropriate for most business applications as data integrity is not a huge concern. Google does not not need very many complex business rules for searching. I would bet their corporate financial application doesn't use BigTable. Nonetheless, most business applications that have large databases can, in fact, use joins and perform well if the designer is knowledgable. Enterprise databases have lots of ways to improve performance (including partitioning) and thus don't need to lose the data integrity features of a relational database.
    – HLGEM
    Commented Jun 13, 2011 at 18:57
  • +1 for you, @HLGEM, both for the answer and the comment; it's such a shame to see to many developers that jump onto the document database bandwagon because they think "joins=slow", only to go and try to solve relational problems that were solved by relational databases 20 years ago.
    – Adam Robinson
    Commented Jun 13, 2011 at 22:44
5

Since every field in a database is defined by the combination of table name, column name, primary key and value, you can always reduce the number of tables by denormalizing into a single table that stores just that. Not very useful, but entirely possible.

Tables are a an abstract layer that helps with the issues of dealing with data. That is why they are created. I made it a joke but understanding that you can reduce every set of data to one master table immediately points out why you shouldn't: because tables bring you something. On a conceptual level they bring you a structure that is easier to understand for humans than serialized data. On the inbetween level they bring the concept of normalization: to avoid saving redundant data and give a single point for changes, rather than changing something on several places. On a technical level databases bring most of the things you want to do with data, numerous tools, and implemented them and tested them more than you probably will by yourself. Think of data types, default values, user rights, indices, foreign key constraints etc. It has been tested, used by many, optimized, debugged. (Not into perfection, but still.)

Since a database is a tool, the main thing is deciding how to use the tool. The number of tables are not important. Minimizing is always possible but at the cost of throwing out the benefits. (If you read more about normalization, you'll come across the few cases for denormalizing - but even then it is all about the right decisions rather than just blindly reducing the number of tables.)

1
  • thanks, it is a lot clear now!, and i have read about normalization btw, i do it it even in cakePHP databases, which encourages another and somewhat different approach.
    – Shaheer
    Commented Jun 13, 2011 at 17:55
3

You should use the right number of tables. You could in theory make do with a single table table by denormalising the entire database, but the database would be unusable. Your friend sounds like he has too much time on his hands.

2

Having the minimum number of tables strikes me as a very peculiar goal.

Certainly reducing a schema from 20 tables down to 8 might be a good thing (if done well it might reduce joins and increase performance, remove unused columns and so on) but it could equally make it harder to understand and enhance going forward.

To think of it another way do you think normalisation is a good thing? Normalisation usually leads to larger numbers of tables but also leads to more maintainable solutions, reduced data duplication and easier data management.

Of course it can also lead to slower performance (assuming the denormalised database was well designed).

Ultimately you need to think about what your requirements are in these areas but as a default starting position I'd say go for a reasonable level of normalisation and then look at whether that's causing specific problems where fewer tables might be a solution.

0

Number isn't important. Design is. Look at some systems out there. Magento, PHPBB, etc. They have dozens of tables in their systems and work just fine.

0

Along with concerns for normalization and performance, you can use "that will require another table" as a way to manage the scope of an application. That feature will require a new table and all the time, energy and effort to design, build, test, manage in the upgrades, and all the other coding involved. Adding 5 fields to existing table(s) (where appropriate) is much easier than a 5 column table.

0

If you design a database with trying to minimize table creation, then you will soon see the abrupt difficulty and err in your ways.

Table count should not be in the forefront of your mind when creating a database design. Put things where they need to logically and relationally go.

0

I think the number of tables matters and can have a great impact on performance if you chose to split data that should, for all business intents and purposes, stay together, into multiple tables (i.e. so you'd have a normalize database). Usually when you do this, you'll be forced to to JOIN Operations (or non-SQL equivalent) in order to get all the data that you need and for large enough tables structured like this, the performance bogs down fast.

I'm not gonna get into details, but I think that the very real fact that the number of tables can influence performance is one of the reasons why noSQL databases such as Cassandra, Mongo, and Google BigTable (sic!) have been invented, and that's also why they encourage de-normalization of data (and consequently avoiding large number of tables/collections, etc).

Same could be said for search servers such as Apache's Solr which doesn't really encourage or easily facilitates splitting your documents into multiple "tables" or "types of entries" encouraging you instead to have a "one encompasses all" schema that has fields common to all the document types that you want to index (and consequently avoid having to do JOIN-like operations).

I'm not saying that the simple fact of having x tables in a schema will necessarily make it slower than a schema with x/2 tables all the times, but there are certain contexts in which it can lead to slow-downs due to consequent extra operations needed to aggregate the data in all those tables. Continuing on this I also don't think that it's ok to say "any number of tables and extreme normalization of the data has no impact what so ever on performance".

0

Uncle Bob would argue that More is Simpler.

See http://c2.com/cgi/wiki?FearOfAddingTables

"a good design is generally simplified by adding tables"

I believe that almost all entities are many-to-many, which requires more tables.

Make a countries table with the continent code in it. Oh, you can't because there are actually 8 transcontinental countries. Same with currencies. Panama uses two.

-2

Then answer is YES.

But depend what is the true meaning of "minimum" number of tables.

For example (an anti-example).

If i have the next objects

  1. users
  2. customers

and both share the same states (fields) and there are not a security restriction then, it way more suited to do a single table

  1. table_persons

rather two different tables

  1. table_users
  2. table_customers

the cons is than in the table_persons we will need to add a new field (type_of_person).

Other mistake (mistake if it is not really needing to do) is to "split" a table, read as :separate a single table in two.

  1. table_persons

in two tables

  1. table_info_persons
  2. table_extra_info_persons

because you are forcing to some queries to join two tables and it is bad.

6
  • hey your answer is very descriptive and helping, thanks
    – Shaheer
    Commented Jun 13, 2011 at 20:59
  • 2
    This gives me flashbacks to my first enterprise application and the database behind it and how much of a nightmare the DBA made it from being a table nazi on stuff like this. I would absolutely never stick customers and users together those are entirely disparate business entities.
    – user7550
    Commented Jun 13, 2011 at 21:31
  • -1: Users and customers have different fields; If not at this moment, they will have at some point in the future. So they deserve separate tables.
    – Sjoerd
    Commented Jun 13, 2011 at 22:23
  • 1
    @Sjoerd, @Chris: While that may often be the case, that's not necessarily true. Things like that are application-dependent. That being said, I do agree with the sentiment. Too often database developers will see "common field names" means it's the same data. This becomes especially easy to do when you look at the database from the ORM first (in other words, backwards). While OO concepts can be modeled in the database, databases are rows and relations, not objects.
    – Adam Robinson
    Commented Jun 13, 2011 at 22:49
  • 1
    +1 for "databases are rows and relations, not objects", i will add it to my fav quotes!
    – Shaheer
    Commented Jun 14, 2011 at 9:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.