I have inherited a db, and I need to transform an existing parent-child relationship into all parents.
Let me first explain the existing data.The tables have a structure something like:
Thing Widget ------------ ------------ thing_id widget_id thing_name widget_name thing_rank widget_rank thing_id
The data is presented to the end user as a list of Things
s, and occasionally a Thing
has-a Widget
, and the widget is presented as being “beneath” or “sub” to the Thing
. So there’s some hierarchy. The other key thing is that this presentation is sorted by the thing_rank
and widget_rank
.
Here’s a concrete example with data:
Things:
1001 "thing blue" 1 1002 "thing green" 3 1003 "thing red" 4 1004 "thing yellow" 2
Widgets:
2001 "widget cucumber" 2 1002 2002 "widget bean" 1 1002
This would be presented with the Thing
s ordered by rank, then the two child Widget
s belong to “thing green”, and ordered by their rank:
1 thing blue 2 thing yellow 3 thing green 1 widget bean 2 widget cucumber 4 thing red
The task is to remove the hierarchy, but preserve the rank order. For example, the above data would be assigned a new rank, and all data would belong to the same relation — no longer a parent-child relationship. For example:
1 thing blue 2 thing yellow 3 thing green 4 widget bean 5 widget cucumber 6 thing red
And the single relation would be:
Stuff ------------- stuff_id stuff_type (thing|widget) stuff_name stuff_rank
I’m looking for how to create a Stuff
view that presents both Thing
and Widget
data, with new rank assignments.