Too Many Tables For a 1:1 Relationship


More Database Design Mistakes to Avoid

I’ve seen a few “designs” where a number of 1:1 relationships were actually split up into a number of tables.

Too Many Tables for a 1:1 Relationship

Results:

The tables of one design (in Oracle 7) were key tables in the system.  Now a table join was required for almost every subsequent query.  Querying on a single table would take 10 to 15 seconds.  After joining to the second table to get the few more fields, the response time slowed down to about 45 to 60 seconds!  About 4 times as long.

Table joins can be really expensive.  Much of Oracle’s optimizer is concerned with the  optimization of table joins.

As a standard rule of thumb, the fewer table joins, the faster the query.  This is why in data warehousing, there is the concept and practice of denormalization.

This also required a certain amount of people time to add the second table, and create the join condition.  I wondered, how many hours of people time and dollar value did this add up to, over the lifespan of the system?  And what was saved?  Perhaps a few minutes of thinking time.

Possible causes for such a “design”:

- “let’s make a table” in response to every request for a new field in the database.

- no analysis of the existing table structures to see if another table with the same PK already exists.

- Speculation that splitting a single table up into multiple tables will give big improvements in “performance”, but failing to consider table joins, etc.

Some believe that by eliminating a few VARCHAR and NUMBER fields from key tables that there will be big increases in performance.  But so far, I have only seen this as a speculative theory.  I have never seen any structured time tests against realistic rows of data, and work load scenarios, where the only independent variable was the two different data models.

I’m doubtful that there would be any significant savings in space, or increases in speed.  Perhaps I’ll eventually get around to some actual tests of my own.  If someone has already done some tests, please send a link to the results.

Solution:

Correction of Too Many Tables in a 1:1 Relationship

When the primary key of the proposed new table is the same one as an existing table, just add the fields to the table with the same primary key.  The design will be much more intuitive, and you won’t need to do any more table joins.

If you really want a number of different objects, consider a single table, and using views with subsets of the fields.

About these ads

3 Responses to Too Many Tables For a 1:1 Relationship

  1. This is really mistake unless you have 1:[0-1] relations.
    In that case it is hard to avoid NULL values in main table and it may also become not even 1NF. Imagine you have a table “car” and then “track”, “wan”, etc. Every subcategory may have its own properties.

    Overall, thank you for good article.

    • rodgersnotes says:

      Thanks for reading and the response. It’s odd.
      DB Design is soooo important. But hardly anyone is reading about the subject!!!

      I’m not quite sure what you are referring to in your post.

      What I’m referring to, is not a 1:0/1 relationship. Or a 1:M.
      It is an absolute, not debatable, 1:1 relationship.

      If my idea is not clear, pick any table in one or your schemas, and split it into 2, 3, 4, or more tables.
      Rationalize that it is for “performance” and that joining 5 more tables “shouldn’t slow things down”.
      Change all your code so that joins are required, when they were not required before.
      Do you get the idea?

      See some other post that also touch on the subject:

      Simple Rule Of Thumb To Determine the Number of Tables In A Relationship
      https://rodgersnotes.wordpress.com/2010/09/19/simple-rule-of-thumb-to-determine-the-number-of-tables-in-a-relationship/

      Using the Wrong Object
      http://rodgersnotes.wordpress.com/2010/09/20/using-the-wrong-object/
      This discusses an example of Too Many Tables, except that it went to a series of views and tables, 5 objects altogether, when a single field would have sufficed.

      HTH

  2. ABLsaurusRex says:

    This sometimes pops up in software where there is a rule which says you can’t change a table until a major release. Some feature is added, but it’s not considered a major release so a table gets added to use the new feature data and then during the major release, the tables get merged. I’m not saying it’s a good thing, I’m saying I’ve seen it done.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: