audit trail tagged posts

February 28, 2017 Posted in Blogs, LiveSync Automation

We talked how to track database changes with one auditing table last month. If we want to replicate these changes into another database, then we might use multi-threading to gain the high performance in data ingestion. However, based on our previous design, we track changes to column level, which generates a serious concurrency control issue. Let’s think about, we have a data table, with 10 columns updates at once, then all these changes are captured and inserted into my_audit_table. If at the same time, another table gets updated, then there might have some cross inserting in my_audit_table, which makes the multi-threading read even harder.

Is there a better approach that we should go? In this case, it’s worth to re-design the audit_table. Let’s see how it looks like (in Oracle PL/SQL):

c...

January 31, 2017 Posted in Blogs, LiveSync Automation

Often we need track changes for the entire database, so we need design audit system to track changes. There are many best practices, such as separating the auditing table in another database, keep it small and fast, demoralize, etc. But at the end, we are facing how to design the auditing system.

There are a few approaches to design the auditing system:

• Build an auditing table for each base table;
• Build one generic auditing table to track changes of all database tables;
• Write changes to XML files which are stored outside of the database.

Triggers residing on database tables may capture changes and write to auditing table(s) or XML files. There are cons and pros for each one of them. None of them is perfect...

September 29, 2015 Posted in Blogs

During past two decades, I have been working on many database design and migration projects. There are a lot of decisions to make in creating new database schema. Any database schema changes can be very expensive in a later date. Some may even result in rewriting the front end applications. So to understand a few best practices and apply them in your database schema design is vital important.

1. Take the advantages of UML

Most people use ERWin to create ERD, then use forward engineering to build up database schema. If we may move further and think about how to use object-oriented UML to describe the world, it might be very helpful. UML brings lots of benefits like composition relationship and inheritance (generalization relationship)...