Recursive hierarchy flattering: backfilling or not?
2 posters
Page 1 of 1
Recursive hierarchy flattering: backfilling or not?
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
2. Backfilling
Which one is easier to use and to query?
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
Re: Recursive hierarchy flattering: backfilling or not?
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
Similar topics
» Recursive Hierarchy Data Modelling
» Further reading for Recursive Hierarchy Data Modelling
» One Hierarchy in two dimensions
» Recursive Relationships in Source System
» Recursive Dimensions in Dimensional Model
» Further reading for Recursive Hierarchy Data Modelling
» One Hierarchy in two dimensions
» Recursive Relationships in Source System
» Recursive Dimensions in Dimensional Model
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum