Re: Enforce primary key on every table during dev?

On 03/01/2018 02:44 PM, Daevor The Devoted wrote:
>
>
> On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
>     On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson <ron.l.johnson@cox.net
>     <mailto:ron.l.johnson@cox.net>>wrote:
>
>         Why have the overhead of a second unique index?  If it's "ease of
>         joins", then I agree with Francisco Olarte and use the business
>         logic keys in your joins even though it's a bit of extra work.
>
>
>     ​The strongest case, for me, when a surrogate key is highly desirable
>     is when there is no truly natural key and the best key for the model
>     is potentially alterable.  Specific, the "name" of something.  If I
>     add myself to a database and make name unique, so David Johnston, then
>     someone else comes along with the same name and now I want to add the
>     new person as, say David A. Johnston AND rename my existing record to
>     David G. Johnston.  I keep the needed uniqueness ​and don't need to
>     cobble together other data elements.  Or, if I were to use email
>     address as the key the same physical entity can now change their
>     address without me having to cascade update all FK instances too.
>     Avoiding the FK cascade when enforcing a non-ideal PK is a major good
>     reason to assign a surrogate.
>
>     David J.
>
>
> This is exactly my point: you cannot know when a Business Rule is going to 
> change. Consider, for example, your Social Security number (or ID number 
> as we call it in South Africa). This is unique, right?.

No, the SSN is not unique. 
https://www.computerworld.com/article/2552992/it-management/not-so-unique.html

> Tomorrow, however, data of people from multiple countries gets added to 
> your DB, and BAM! that ID number is suddenly no longer unique. Business 
> Rules can and do change, and we do not know what may change in the future. 
> Hence, it is safest to have the surrogate in place from the start, and 
> avoid the potential migraine later on.
>
> Disclaimer: this is just my opinion based on my experience (and the pain I 
> had to go through when Business Rules changed). I have not done any 
> research or conducted any studies on this.


-- 
Angular momentum makes the world go 'round.