Mysql – Appending IDs to duplicate descriptions for groups of data and single row data

ddlMySQL

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'

Best Answer

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.

Related Question