Oracle - create changelog query

Question

I came up with an interesting Oracle-db problem and keep thinking about a feasible solution to solve this task. Lets say we got three tables (bold columns are PKs):

• A (Name, GroupID, versionID, Column1, Column2)
• B (Name, GroupID, versionID, Column1, Column2)
• C (versionID, date_created)

So it works as follows:

• When a new batch of data is imported into the db, each record is tracked with a new unique versionID in table C
• Table B holds all imports / changes while Table A only has one record (the newest)

What I'm trying to do right now, is get a sort of diff / changelog for the different batch uploads. This includes any differences in:

Name, GroupID, ColumnA, ColumnB, ColumnC between two specific dates.

I do this with two inner joins over A,B with Name&GroupID and then B and C over versionID and two dates. This works fine if the PKs Name & GroupID stay the same, see output 1:

|A.Name(new)|B.Name(old)|A.GroupID(new)|B.GroupID(old)|A.Column1(new)|B.Column1(old)|A.Column2(new)|B.Column2(old)|date_created(new)|date_created(old)
|Test1      |Test1      |37            |37            |Dog           |Cat           |Mouse         |Mouse         |2015-08-10       |2015-08-20 
|           |Test3      |              |38            |              |Cat           |              |Mouse         |2015-08-10       |2015-08-20 
|New record |           |38            |              |Mouse         |              |Mouse         |              |2015-08-10       | 

1. output - A.Column1 changed from Cat to Dog
2. output - a record gets deleted
3. output - new record is added which did not exist before

My output works well for output scenario 1, however if the name or groupID gets changed / deleted, or a new record is insert this change obviously won't show up because the join does not work anymore. Does anyone have an idea to deal with this problem. My output should work as shown above in output 1 & 2 & 3. I am sadly not able to implement any triggers to the db and my goal is to try to solve this via sql queries.

Thanks in advance for your input

Answer 1

There has to be some invariant between two versions to say the versions relate to the same thing. This is the job of the primary key. It exists to differentiate this thing from that thing and to say that all that stuff over there relates to this one thing over here, via the foreign key of stuff.thing_id. If you change the primary key - Name, or GroupID in your case - you have a new thing, not an old thing with a new name. This is one of the reasons we have impersonal identification like social security numbers.

You have a couple of options. A) re-design your tables so there is an invariant identifier. An integer surrogate key works well for this. B) add two columns Previous_Version_Name and Previous_Version_GroupID. Populate these as you accumulate values for the new version. Use them in your join. "A" would be better.

You may also want to think about using a partitioned table and / or views rather than move data around between TableA and TableB yourself.