Albert/Ben, Thanks. SET LIGHTBULB ON
That certainly would dramatically change our application. Lots of work in code, forms, & reports. Something to consider as we move our stuff to RB7. Some ongoing problems would be automatically fixed. Presently, some manufacturers have a part number bigger than our 15 char max. This would be a lot easier to manage changes using your approach. And, when we print barcodes, we use the actual part number where special characters violate the Code 39 allowable character set. Using the autonum/integer part number for the bar code, would resolve this issue. I'm afraid to look at what's involved in doing this, but there certainly are some long term benefits. Running the "conversion program" at all the plants sounds a little scary. Dennis ***** At 05:25 PM 8/8/2003 -0400, you wrote: >Dennis, I would use a double key on the primary part table: > >PartID INTEGER NOT NULL PRIMARY KEY >PartNo TEXT (8) NOT NULL UNIQUE >PartDesc TEXT (40) NOT NULL ><other columns> > >All references would be to the integer value PartID, and all that would happen to the user is that the part number would change when he/she reopened the display. The only place in the system PartNo should appear is in the parts master table. Everywhere else it should be solely for display. All Foreign key references should be to the Primary Key, not the Unique column, even if you can reference the Unique constraint. > >My 3 (Canadian) cents worth. > > >Dennis Fleming <[EMAIL PROTECTED]> wrote: > >>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 >> >> > > >-- >Albert Berry >Full Time Consultant to >PSD Solutions >350 West Hubbard, Suite 210 >Chicago, IL 60610 >312-828-9253 Ext. 32 > > >__________________________________________________________________ >McAfee VirusScan Online from the Netscape Network. >Comprehensive protection for your entire computer. Get your free trial today! >http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 > >Get AOL Instant Messenger 5.1 free of charge. Download Now! >http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 > > > Dennis Fleming IISCO http://www.TheBestCMMS.com Phone: 570 775-7593 Fax: 570 775-9797
