0

I'm in need of some guidance with this problem I'm working on. First I'll need to explain the type of table I'm working with.

The table has a description field for each record. Each record has a primary key and a "for the client key", we'll call it the "compID". Each record can be duplicated in one click through versioning, and the only difference between the versioned record and the original record is their primary key. For example, this is what versioned records will look like:

PK | compID | version | description
1    |   10       |   v1        | 'test'
2    | 10         | v2          | 'test'
3    | 10         | v3          | 'test'
4    | 10         | v4          | 'test'

Now, records that were created via versioning process are allowed to have the same description as records with the same compID, but, if the compID is different the description needs to be unique. This is new logic added to the app and I am working on the script to fix the bad data in the table by appending the record's compID to the end of the description, but keeping all records with "compID = x" to still have the same description.

Another twist is that if there is existing data where 2 different compIDs have the same description, the earliest/smallest compID will not have their description updated, only the subsequent compIDs will have their compID added as a suffix to the description.

So, if we have data that looks like this:

PK | compID | version | desc
1    |  10         |  v1        | 'test'
2    | 10          | v2         | 'test'
3    | 10          | v3         | 'test'
4    | 11          | v1         | 'test'
5    | 11          | v2         | 'test'
6    | 11          | v3         | 'testNum2'
7    | 12          | v1         | 'test'

It needs to be correct through the script to look like this:

PK | compID | version | desc
1    |  10         |  v1        | 'test'
2    | 10          | v2         | 'test'
3    | 10          | v3         | 'test'
4    | 11          | v1         | 'test11'
5    | 11          | v2         | 'test11'
6    | 11          | v3         | 'testNum2'
7    | 12          | v1         | 'test12'

  • what happens if ID=7 has the same description of ID=3? – McNets Jan 21 '17 at 18:39
  • In that case, since the compID of 7 is different from the compID of 3, the description from ID 7 will have its compID appended to the description (like in the last table) – ineedasickname Jan 21 '17 at 19:09
0

I think you need to have 2 tables: One for the current state of compID, complete with a UNIQUE(desc) constraint. The other is the "history" of the versioning.

When 'saving' a record, first copy the state row to history, then replace the state row.

Do the pair of statements in a transaction so that if you get "duplicate key" on the second statement, all can be atomically ROLLBACK'd.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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