Posts tagged sql

Also known as recursive relationships, these are unary type relationships. These type of relationships occur when an entity can reference itself.

Recursive relationships occur often in hierarchies, and also in objects that are part of a whole. Below are some scenarios that could be modeled using recursive relationships:

1. A course at a school or university can have 1 or more prerequisites.
2. Organizations have people hierarchies (CEO,VP, Director, Managers, Employees).
3. A finished product can be made of many sub products or parts.
4. Also biological hierarchies(Dad, mom, siblings, son, etc..)

Let’s look at it in action, grab some Java mug, just an advice   We are going to solve this with the four steps as follows:

1. Get a visual of the hierarchy (Extremely important).
2. Create the database model.
3. Fill it with some data.
4. Write some queries.

Step #1: Get a visual of the hierarchy

The University in Illinois has one of the most reputable computer science programs in the world, so I have decided to model a very tiny section of their curriculum to illustrate self-referencing relationships. The following mind map represents two master level computer science classes and its prerequisites:

Step #2: Create the database model

The database table that will support this solution looks as follows:

Alert: Notice how the relationship line is optional at each end, you must do this otherwise the recursion would go in an infinite loop.

Step #3: Fill it with some data

The following screen shot shows all the data in the COURSE table that I have manually entered.

Step #4: Write some queries

You can write the queries in two styles:

1.      By doing a query for each level of the hierarchy and combining them together by doing  union all.

2.      By writing a more generic query and providing an in clause. This step is not recommended if you have many rows in the table as it will drive you more insane than the first step.

Style #1: Query by “union all”:

We are going to do the first query technique to demonstrate what the data looks like for the master level course CS 414 (Refer to the course on the left in the mind map).

select c.id, c.course_code, c.course_name, c.course_category, c.next_course
from course c
inner join course d
   on c.next_course = d.id
where c.id in (1)

union all

select c.id, c.course_code, c.course_name, c.course_category, c.next_course
from course c
inner join course d
   on c.next_course = d.id
where c.id in (2)

union all

select c.id, c.course_code, c.course_name, c.course_category, c.next_course
from course c
inner join course d
   on c.next_course = d.id
where c.id in (3)

union all

select c.id, c.course_code, c.course_name, c.course_category, c.next_course
from course c
inner join course d
   on c.next_course = d.id
where c.id in (4)

The query above produces the resultset as follows:

Style #2: Query by “in” clause

Now we are going to write a shorter query that will demonstrate what the data looks like for the master level course CS 421 (Refer to the courses hanging to the  right of CS 421 in the mind map).

To obtain the dependencies hanging to the left of CS 421 we could write the following query:

select c.id,
         c.course_code,
         c.course_name,
         c.course_category,
         c.next_course
from course c
inner join course d
   on c.next_course = d.id
where c.id in (5,6,8,9)

This query will produce the resultset as follows:

To obtain the dependencies hanging to the right of CS 421 we could write the following query:

select c.id,
         c.course_code,
         c.course_name,
         c.course_category,
         c.next_course
from course c
inner join course d
   on c.next_course = d.id
where c.id in (5,7,10,11,12,13)

This query will produce the resultset as follows:

Improvements

Now that I have finished writing the post i realize that I could improve the queries by replacing the next_course column with a next_course_name column. If you want to do that comment out the c.next_course column from the select clause and replace it with d.course_name as next_course_name.

Best Practices

The best practice is to have a collection of views that give you all the data representation that you need to have for each hierarchy. That way you only have to pull your hair once and hope that no reorganization is going to happen (EVIL LAUGH HERE) . If it does, then you will have to go through a similar painful exercise again.

Conclusion: Recursive relationships can be very tedious and difficult to implement. Hence, the bullet points below:

• documentation to understand the recursion is very important.
• A Mind Map is the best way to document a recursive relationship.
• A business group should be responsible for owning that document and passing it along to IT staff. This document should be revised at least once a year (or more) to ensure the hierarchy has not changed.

Without the mind map I would have not had the right state of mind to solve this problem. The tool I use for mind mapping is called Novamind (it is not free though) but very good for learning, brainstorming, and other purposes.