Albert said it pretty well... but I'll add my take if it might help.
> 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 your Parts table I'd add an integer column that would be the primary key.
All foreign keys would refer to it. The part number associated with it
becomes arbitrary... it doesn't really matter what the user does with it. The
only down-side is if the user should change the entire nature of the record.
Say that the original entry is for "bolts" and then somewhere along the line
someone changes it to "oil", the record would still be associated with all the
prior "bolt" transactions. I've not yet experienced this as an issue though.
Under this logic your supervisor could change the part number at anytime
and not effect any other part of the system because the Parts table is the
only one effected.
> 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
Would be replaced by:
Parts:
IntPartNum Autonumber Primary Key
P/N Unique
Parts/Vendor
IntPartNum Foriegn Key
IntVendId Foriegn Key
(or, I guess)
(ctxt(IntPartNum)+'-'+ctxt(VendID))
Vendors
IntVendID Primary Key
VendId Unique
Ben Petersen
