Re: PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 9:27 AM, Melvin Davidson <melvin6925(at)gmail(dot)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.
​