I guess I missed that thread. I understand what you said, but I miss the rational.
In your system, the key column, say a part number which must be unique, is just data. In order to assure the part number is not duplicated, then a constraints would point to the part number column (which would of course need to be indexed). It seems like more overhead to do the same thing. In our app, we have a part, vendor, and part/vendor cross reference table. The key columns would be: Tables: Parts Parts/Vendor Vendors Keys: P/N (P/N + Vendor ID) Vendor ID The two primary tables, and the many-to-many cross reference table, all control uniqueness by the user defined column value. So when you say, "Much easier to manage", I'm missing the "easier" part. TIA, Dennis ***** At 10:27 AM 8/8/2003 -0700, you wrote: >> 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 >> > > > Dennis Fleming IISCO http://www.TheBestCMMS.com Phone: 570 775-7593 Fax: 570 775-9797
