You can always have to field in all your relationships, but I would convert to use Serial Numbers or UUID for each record as the relationship key
1 of 1 people found this helpful
I don't think that there's anything wrong with that for display purposes (utilizing old primary key and new primary key). However, you should consider the Asset Number as data. You should also pore over your scripts and update any usage of relationships based on Asset to your new relationships based on the serial number.
One issue you will have is getting the new primary key field correctly posted into any contexts where it is used as a foreign key. you can do this by adding it via the existing relationship using a looping script.
The customer wishes to change to having Serial Numbers (a field we've gathered for years) as the key relationship.
With all due respect, that's like asking the school kids to drive the school bus instead of the driver. A relationship is the "under the hood" part of the database that the customer should never see anyway. You can always use a supplementary field such as the serial number field for searching and sorting out records--the visible function of the database without changing the structure of your database to match by a new field and value. You can always hide the asset number from view while continuing to use it to link records in relationships.
The end result can be that your system appears to link data by serial number when the asset number continues to be the real means of linking data between your various tables.