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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>
> On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net
> <mailto:ron(dot)l(dot)johnson(at)cox(dot)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.