Category Database

November 19, 2018 Posted in Blogs, Database, Enterprise Integration

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...

November 1, 2017 Posted in Blogs, Database

PostgreSQL, as one of the best open source object-relational DBMS, is quite popular today. However, it doesn’t provide built-in auditing function, which is required by most production systems.

Let’s see how we generate generic auditing function for a table in PostgreSQL schema. Then we should be able to generate auditing function for the entire schema easily.

Let’s assume we have an input parameter table name , and we have a pre-built auditing table, which contains all fields from source table, in line 5 below, plus all other auditing fields:

 CREATE TABLE schema_name.sample_audit
 (
 	audit_id bigint NOT NULL DEFAULT nextval('schema_name.sample_audit_id_seq'::regclass),
 	id bigint NOT NULL,
 	source table columns ........