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.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> wrote:
On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson <ron.l.john...@cox.net
<mailto:ron.l.john...@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.
