Too Few Tables

September 21, 2010

More Database Design Mistakes To Avoid

One program that I inherited just wasn’t making sense.

The load of this program loaded data into a single table.  It retrieved data into a few fields.  Then did loop de loops, reading these initial fields, and compared them to hard coded values in the program.  Finally updated the remaining fields, based on the hard coded values in the program.

Eventually it dawned on me.  There was a functional dependency, violating 2NF.  The first field, determined the second field.  This of course should have been moved into another table, but it wasn’t.
Read the rest of this entry »


Binary Trees As The Database Design!

September 21, 2010

More Database Design Mistakes To Avoid

One design that I saw at an interview, was highly unusual.   There was a diagram on the white board.  Inside the database were a number of other boxes, each representing a different subsystem.  I’d never seen anything like it.

We talked about designing the database model for good performance.  We certainly seemed to be on the same page. But then he sat me down at a computer, and had me do a test.  There were a lot of Recursive Ids and parent Ids.  For a given ID, find all the other keys associated with it.

The database “design” was a binary tree, and the test was to traverse the tree!
Read the rest of this entry »


Circular or Cyclical References

September 21, 2010

Another Database Design Mistake To Avoid

One very bizarre design that I saw actually had circular references between tables.

The primary/foreign keys of two tables, were actually found in the other table.
Read the rest of this entry »


Using the Wrong Object

September 20, 2010

More Database Design Mistakes To Avoid

Given that you need more information in or from the database, what do you do?

Do you create one of the following?
Read the rest of this entry »


Defining Recursive Relationships In Lookup Tables!

September 20, 2010

Another Database Design Mistake to Avoid

Recursive programming is used in a few places in computer science. Most notably in stack processing.  The big picture looks something like:

x program starts  (a)
which calls itself again (b)
which calls itself again (c )

then iteration c finishes
iteration b finishes
iteration a finishes

Recursive relationships are also sometimes used in database design.

The classic example is an organization that has employees.  And managers.   The managers are also employees.

Unfortunately, I’ve seen the recursion pulled out into another table!  A lookup table.
Read the rest of this entry »


Simple Rule Of Thumb To Determine the Number of Tables In A Relationship

September 19, 2010

More on Database Design Mistakes to Avoid

Here is my rule of thumb for the number of tables, for each relationship.
Read the rest of this entry »


Combining Identifying Data, Transaction Data, and LOBs in a Single Table

September 19, 2010

Another Database Design Mistake to Avoid

In another post, I mentioned that as a rule of thumb, if data has a 1:1 relationship, all the fields should be in the same table.

Of course, with many rules, you can go overboard.  Blindly following rules is the mark of a beginner.  Search for:
The Dreyfus Model of Skills Acquisition

Here is a caveat.
Read the rest of this entry »


%d bloggers like this: