Recursive hierarchy flattering: backfilling or not?

View previous topic View next topic Go down

Recursive hierarchy flattering: backfilling or not?

Post  larus on Tue Nov 20, 2012 2:52 am

I have an recursive hierarchy in one parent-child table (adjacent list). What is the right way to flatten this hierarchy (I'm not using hierarchy bridge in this case):

1. Not backfilling
Code:

+-------+-----+--------+--------+--------+--------+--------+
| empid | lvl | level1 | level2 | level3 | level4 | level5 |
+-------+-----+--------+--------+--------+--------+--------+
|    1 |  1 |      1 | NULL  | NULL  | NULL  | NULL  |
|    2 |  2 |      1 | 2      | NULL  | NULL  | NULL  |
|    3 |  2 |      1 | 3      | NULL  | NULL  | NULL  |
|    7 |  3 |      1 | 3      | 7      | NULL  | NULL  |
|    9 |  4 |      1 | 3      | 7      | 9      | NULL  |
|    11 |  4 |      1 | 3      | 7      | 11    | NULL  |
|    12 |  5 |      1 | 3      | 7      | 9      | 12    |
+-------+-----+--------+--------+--------+--------+--------+

2. Backfilling

Code:
+-------+-----+--------+--------+--------+--------+--------+
| empid | lvl | level1 | level2 | level3 | level4 | level5 |
+-------+-----+--------+--------+--------+--------+--------+
|    1 |  1 |      1 |      1 |      1 |      1 |      1 |
|    2 |  2 |      1 |      2 |      2 |      2 |      2 |
|    3 |  2 |      1 |      3 |      3 |      3 |      3 |
|    7 |  3 |      1 |      3 |      7 |      7 |      7 |
|    9 |  4 |      1 |      3 |      7 |      9 |      9 |
|    11 |  4 |      1 |      3 |      7 |    11 |    11 |
|    12 |  5 |      1 |      3 |      7 |      9 |    12 |
+-------+-----+--------+--------+--------+--------+--------+

Which one is easier to use and to query?

larus

Posts : 5
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Recursive hierarchy flattering: backfilling or not?

Post  TheNJDevil on Tue Nov 20, 2012 1:42 pm

I prefer the backfilled version. I hope I'm not the only one who doesn't like Null values in my DW tables if I don't have to have them.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum