> Initially, we forced the Supervisor to be Mutli OFF, in order to avoid > changing part ABC to XYZ while someone was using part ABC or a > table that had ABC as a foreign key. This was too restrictive. So, > now we let 'em.
Dennis, Something I picked up from this list may help. At this point I don't use any user data as keys. Each row has a unique indentifier, and natural relationships (ie header to detail) have an additional integer column in the detail table to link the two. These keys are established at the time the record is created and never need to change after the fact. So things like part numbers, invoice numbers, account numbers etc... have no effect on the relational aspect of the database. You would still use constraints to avoid dups, but the whole issue is much easier to manage. Ben Petersen On 8 Aug 2003, at 12:39, Dennis Fleming wrote: > Managing change... > > We allow our users, in Supervisor mode, to change key columns. Our > application checks every table where a part number, equipment number, etc., is > used and then notifies the user if a conflict will result with the change. For > example, if there is a cross reference table with a multi key index, and the > change will result in a duplicate key, they need to resolve it first. > > Initially, we forced the Supervisor to be Mutli OFF, in order to avoid > changing part ABC to XYZ while someone was using part ABC or a table that > had ABC as a foreign key. This was too restrictive. So, now we let 'em. > > I was surprised that if user A is changing key column ABC to XYZ, and user > B is displaying a screen where ABC is a foreign key, they get no notice > that ABC has just been deleted. If they refresh their screen, the part > description just disappears. If they are on the primary key screen, the > change is done, they save or exit, they see, "Another user has deleted this row. > Accept the change?" If they don't accept the change then all the history > (related tables) has still been changed from ABC to XYZ, but ABC is still in the > primary table. Certainly a surprise to the person who did the change from ABC to > XYZ. > > My thought is to lock just the primary table they are changing the key > column in as a compromise, but I was curious what others are doing? > > TIA, > > Dennis > ***** > Dennis Fleming > IISCO > http://www.TheBestCMMS.com > Phone: 570 775-7593 > Fax: 570 775-9797 >
