Recently I worked on an interesting use case from auditing which requires to roll up status of lower level tables into higher level. We already have a tree hierarchical design in our data model.
There are two options: one is to handle the roll-up pair by pair; another is to design a context table which presents the tree structure and use Oracle hierarchical query to go through tree traversal based on depth first search. The second option is obviously an optimized approach to go. Since it may easily handle larger size model – as long as we have the context table ready. Let’s dive in and see how we handled this.
First, I created a context table which presents the context id and parent context id relationship. It’s a self-reference table.
create table ls_context_info ( context_id numbe...Read More
in a SOA environment, facing the pressure to move to cloud, but not quite ready.