Allowing Key Changes To DB without duplicates

In an app I am working on, on some of the forms I would like to allow the user to be able to change the values of fields that are part of a unique constraint in the database.

for example one of my forms allows a user to change the part numbers in a parts list. the part number has a unique constraint in the database.
With this being the case the user could possibly swap the part numbers of two records like so:

Old | New
Part A | Part C
Part B | Part B
Part C | Part A

So when the user commits his changes to the database, the database would throw an exception when trying to update Part A to Part C because PartC would be duplicated.

With that being said, I have come up with the following solution, which will work, but seems so cumbersome and I was wandering if anyone had a more elegant solution:

So here is the process I came up with:

1) In the objects I use to store the partnumbers I keep track of the original partnumbers
2) When the user commits their changes I extract the list of objects that have partnumbers that are different from the original
3) with the list I match up objects where there is a match between a new and old partnumber - this is marked as an update
4) for new partnumbers with no matching old partnumber - this is marked as an insert
5) for old partnumbers with no matching new partnumber - this is marked as a deletion
6) Then I group all the pairs of objects into groups with old or new partnumbers that are relative to each other so that I can wrap the database
commit into a transaction

To illustrate:
this is what the user would see, they start out with the old values and end up with new values:

To the user, it simply appears as if they are updating the partnumbers:
Old | New
Part A | Part B
Part B | Part C
Part C | Part Z
Part D | Part A
Part E | Part E

with my procedure this is what actually happens

Old | New
Part A | Part A (update with Part D's fields)
part B | Part B (update with Part A's fields)
Part C | Part C (update with Part B's fields)
Part D | no match (delete this part)
Part E | no change
no match| Part Z (insert this part)

In this example all except for Part E would be wrapped in a transaction before updating to prevent any unexpected results.

Doing all of this achieves my goal of allowing the user to "update" the part numbers, but it requires a lot of processing and I thought someone else may have come up with a
better solution. thanks for any ideas

to me, this is not the way you should going about it. If the part number changes, then it's a different part. An existing part should not be "renamed" to another part number. You delete the old part record, and insert the new part record.

deleting and then inserting would certainly work - and so much easier for me to deal with concurrency issues - BUTTTTTT
I am going for maximum ease of use for the user. I want the user to be able to switch up part numbers without having to re-enter all the fields related to the old part number.

Quote:

---

Originally Posted by JoeBuntu

deleting and then inserting would certainly work - and so much easier for me to deal with concurrency issues - BUTTTTTT
I am going for maximum ease of use for the user. I want the user to be able to switch up part numbers without having to re-enter all the fields related to the old part number.

---

they don't have to re-enter the fields. To them, it looks like they are "renaming" the part. However, in the database, it is deleting the old part and creating a new part with all of the old part's data(except for the part number of course). To the database, it's a completely new part. To the user, they see it as "renaming".

What the user sees and what actually happens doesn't have to be the same thing.

what you just said is what my procedure does. No matter what you do it never updates the actual part number, but does inserts and deletes even though the user might see it as simply changing the part number.

simply doing inserts and deletes will not work:

Say you start with Part A and Part B:

the user swaps Part A to Part B and vise versa

Just doing inserts and deletes if you updated the part A record first you would delete the part A record and then reinsert it as Part B - Boom you just duplicated Part B before you had a chance to delete the Part B and reinsert it as Part A and the database throws an exception.

What my procedure does is look for these key swaps and where they exist, instead of of deleting or inserting records it updates the records with the fields of the key that it was swapped with.

for those records that have had changes to their partnumber and there isn't another record with that has it's old part number- then I simply delete and insert

here's the code:

if it helps:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExelInventory.Source.Classes.General;
using ExelInventory.Source.Classes.General.DbItems;
using ExelInventory.Source.Classes.General.KeyManagement;


/* Will match old keys to new keys as I means of not updating keys but deleting/inserting them instead */
namespace ExelInventory.Source.Testing
{
class test<TKey, TDbItem>
where TDbItem : AbstractDbItem, IEditableKey<TKey>, IHasKey<TKey>
where TKey : IComparable
{

protected static int GroupNumber = 0;
protected static List<db<TKey, TDbItem>> pairs { get; set; }

public static void getItems(IHasDbItems<TDbItem> Items)
{

//get a list of items that have key changes (compare to result not equal to zero)
IEnumerable<TDbItem> jack = from item in Items.GetUpdatedItems()
where item.OldKey.CompareTo(item.NewKey) != 0
select item;


//----------------------------- With the Items with key changes ------------------------------------

//queries list for matching keys
//IEnumerable<db<TKey, TDbItem>> query = from newKeys in jack
// join oldKeys in jack on newKeys.NewKey equals oldKeys.OldKey
// select new db<TKey, TDbItem>(oldKeys, newKeys);

//queries for matching keys and new keys without matches
IEnumerable<db<TKey, TDbItem>> query = from newKeys in jack
from oldKeys in jack.Where(x => x.OldKey.CompareTo(newKeys.NewKey) == 0).DefaultIfEmpty()
select new db<TKey, TDbItem>(oldKeys, newKeys);

//now query for old key values without a matching new and add them to the list
IEnumerable<db<TKey, TDbItem>> query2 = from values in
(from oldKeys in jack.Where(x => x.OldKey.CompareTo((long)0) != 0)
from newKeys in jack.Where(x => x.NewKey.CompareTo(oldKeys.OldKey) == 0).DefaultIfEmpty() //do not match new items
select new { oldKeys, newKeys }).Where(x => x.newKeys == null)
select new db<TKey, TDbItem>(values.oldKeys, values.newKeys);


pairs = new List<db<TKey, TDbItem>>(query.Union(query2));
GroupNumber = 0; //reset group number
foreach (db<TKey, TDbItem> item in pairs)
{
FindMatches(item);
}

}

protected static void FindMatches(db<TKey, TDbItem> item)
{

db<TKey, TDbItem> match = null;
//if the item is not a deleted part
if (item.NewItem != null)
{
var query = from values in pairs.Where(
x => !(x.Equals(item)) && //do not match the same item
x.GroupId == 0 && //item has not already been matched
x.OldItem != null && //do not invoke a property on a null reference!
x.OldItem == item.NewItem) //if the new item matches the old- bingo
select values;
if (query.Count() > 0)
{
match = query.First();
if (item.GroupId == 0)
{
GroupNumber++;
item.GroupId = GroupNumber;
}
match.GroupId = item.GroupId;
FindMatches(match); //recursively find
}

}
else if (item.OldItem != null)
{
var query = from values in pairs.Where(
x => !(x.Equals(item)) && //do not match the same item
x.GroupId == 0 && //item has not already been matched
x.NewItem != null && //do not invoke a property on a null reference!
x.NewItem == item.OldItem) //if the new item matches the old- bingo
select values;

if (query.Count() > 0)
{
match = query.First();
if (item.GroupId == 0)
{
GroupNumber++;
item.GroupId = GroupNumber;
}
match.GroupId = item.GroupId;
FindMatches(match); //recursively find
}
}
}
}
}

namespace ExelInventory.Source.Testing
{
class db<Tkey, TDbItem> where TDbItem:AbstractDbItem, IHasKey<Tkey>
{
public TDbItem OldItem { get; set; } //reference to the item with the old key
private TDbItem m_NewItem; //reference to the item with the new key
private TDbItem m_TempItem = Activator.CreateInstance<TDbItem>(); //used to store values until transaction completes successfully
public int GroupId { get; set; } //used as an id to group related updates together

public db(TDbItem oldItem, TDbItem newItem)
{
this.OldItem = oldItem;
this.NewItem = newItem;
}

public TDbItem NewItem
{
get { return m_NewItem; }
set
{
if (value != null)
{
//use reflection to set all properties of the temp item to that of the new key
m_NewItem = value;
foreach (PropertyInfo info in TempItem.GetType().GetProperties())
{
if (info.CanRead && info.CanWrite)
{
m_TempItem.GetType().GetProperty(info.Name).SetValue(m_TempItem, info.GetValue(NewItem, null), null);
}
// now set the record as unchanged
}
}
}
}

public TDbItem TempItem
{
get { return m_TempItem; }
}

public DatabaseAction DbAction
{
get
{
DatabaseAction result;
if(NewItem == null)
result = DatabaseAction.delete;
else if(OldItem == null)
result = DatabaseAction.insert;
else
result = DatabaseAction.update;
// insert logic here to decide what action to take
return result;
}
}

public MySqlDateTime TimeStamp
{
get{
switch(DbAction)
{
case DatabaseAction.insert:
return NewItem.Timestamp;
default:
return OldItem.Timestamp;
}
}
}
public String User
{
get{
switch(DbAction)
{
case DatabaseAction.insert:
return NewItem.User;
default:
return OldItem.User;
}
}
}
public Tkey Key
{
get
{
switch (DbAction)
{
case DatabaseAction.insert:
return NewItem.Key;
default:
return OldItem.Key;
}
}
}

public void CommitChanges()
{
//use reflection to set all properties of the new key to that of the temp item
foreach (PropertyInfo info in TempItem.GetType().GetProperties())
{
if (info.CanRead && info.CanWrite)
{
NewItem.GetType().GetProperty(info.Name).SetValue(NewItem, info.GetValue(TempItem, null), null);
}
// now set the record as unchanged
}
NewItem.SetRecordAsUnchanged();
}

}

public enum DatabaseAction
{
delete, update, insert
}

How about adding a field to the record for CHANGED/DIRTY.

It's a terrible idea to change part numbers. What that can do to old records isn't even worth considering. A part number should have a unique key set up ONCE. Users can call it whatever they want, but it is that GUID that stays the same.

I would plan to use this concept on multiple facets of my application but for the example I am referring to:

The parts list I speak of is the list of components that go into a Finished Product - Bill of Materials (BOM). The form allows the user to edit the Parts that go into a product as well as other things such as quantities, etc

when I speak of changing the parts- the actual value is an Id number that is constrained by referential integrity to a table of parts- so the user is not just entering free form text into a box or something.

As far as data integrity issues- for this example I am not concerned with changing partnumbers,
because like I said, the underlying Part_Id of the record would never be updated- the values of the other fields could be, but never the part_id, it could only be deleted and replaced with another record. Sooooo - if this list of parts is referenced elsewhere, the database would not allow the part number to be changed because it will not allow the record to be deleted due to the constraints

my tables look like this:

tbl_bom: Bom_Id, Part_Id
Foreign Key : [Part_Id] references [tbl_materials].[Part_Id]

tbl_bom_items: Detail_Id, Finished_Part_Id, Component_Part_Id, other fields
Foreign Keys : [Finished_Part_Id] references [tbl_bom].[Part_Id],
[Component_Part_Id] references [tbl_materials].[Part_Id]
Unique index: [Finished_Part_Id] & [Component_Part_Id] (so parts are not duplicated in a BOM)

tbl_Materials: Part_Id, PartNumber, other fields