0

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

5
  • So the PKs change and there is no way of working out which old row it was associated with?
    – Philᵀᴹ
    Oct 5, 2015 at 19:49
  • yes, that's basically the case..
    – MoJo3341
    Oct 5, 2015 at 20:18
  • There is no way of working out which old row it is associated with. Think about that :-)
    – Philᵀᴹ
    Oct 5, 2015 at 20:25
  • I know, however it might be possible to display it as a newly created row, as in the 3rd output?! In this case I need a condition that if the join fails for certain rows, those will be displayed as well
    – MoJo3341
    Oct 5, 2015 at 20:48
  • It would help if you added some sample data of the tables themselves, not only the result you want, plus the query you used. (and do tables A and B have a date column?) Oct 6, 2015 at 7:27

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.