>What then if it is discovered that the keyed in value was mis-typed? That is why SQL has UPDATE and DELETE statements. If a primary key is incorrect, it can be fixed, be it one method of another.
On Mon, Aug 24, 2015 at 10:04 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Aug 24, 2015 at 9:27 AM, Melvin Davidson <melvin6...@gmail.com> > wrote: > >> 9. >> >1) What happens if someone mis-types the account-id? >> > To correct that, you also need to correct the FK field in the other >> dozen tables. >> >2) What happens when your company starts a new project (or buys a >> competitor) >and all the new account numbers are alpha-numeric? >> >> I would reply that in good applications, the user DOES NOT type the key, >> but rather selects from a drop down list, or the app looks it up / enters >> it for them. Besides, it's just as easy to miskey an integer as it is an >> aplha numeric. The point is, do not create two primary pkey's when one will >> do. >> > > Your missing the point. The existing "Account ID" that you refer to is > apparently externally defined. Pretend it is a social security number. > How would one create a new user in your system, and record their > account_id/social-security-number, without typing it in. What then if it > is discovered that the keyed in value was mis-typed? > > The "point" is to not introduce redundant information. Creating your own > surrogate identifier in order to avoid using a surrogate identifier value > created by another system does not introduce redundancy but rather provides > the system using the primary key control over its generation and, more > importantly, format. The highly situational nature of this is why "data > modelling" is not something I'd incorporate in a "usage" document. > > David J. > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.