Defining Recursive Relationships In Lookup Tables!


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.

Recursive Relationship Incorrectly Defined in a Lookup Table

I’m convinced that the lookup table was created because someone learned how to normalize into first normal form.  And they came to believe that 1NF and making lookup tables is everything you need to know about database design.  However, database design is actually a pretty deep subject, and 1NF is just a start.

Correction:

Correct Way To Define a Recursive Relationship

Define recursive relationships in a single table.

Advertisements

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: