Re: PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 9:27 AM, Melvin Davidson <melvin6925@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.

​