|Keep It Simple, Stupid|
Re^3: Architecture design for full stack development.by sundialsvc4 (Abbot)
|on Jun 24, 2017 at 03:12 UTC||Need Help??|
I believe that you have somehow entirely missed my most-essential(!) point that “defrag code” is not only unnecessary, but specifically should not be pursued.
If you suppose that Primary Keys should need to be “defragmented,” then this indicates that you are attaching to them “some (any ...) intrinsic meaning.” (Why? Because it seems to bother you that the values might not be consecutive!)
If, for any legitimate business reason or otherwise, you need some sort of identifier that is “consecutive” and that therefore might from time-to-time need to be “defragmented” ... (although I certainly do not recommend such a thing) ... “feel free.™” However, do not make this value “your primary key!” Instead, make it “just another column.”
(For a more thorough background of what I am talking about, let me please now refer to Database Normalization in WikiPedia.)
The “primary key” of any and every(!) table within your database should be a perfectly-arbitrary, yet guaranteed-unique, “identifier ... and nothing more™ ...” of a particular row. All such values should be entirely internal to the database, and never exposed to, therefore also never of material interest to, the outside world.
If you feel any need whatsoever to “defragment” those values, then this necessarily proves(!) that you are violating this precept ... and you should immediately redesign your database architecture accordingly.
“Do you really care what the exact numeric value of ‘your credit-card number’ is?” “No, as long as it is unique!” (And, but of course, as long as it works.) Otherwise, it’s just a well-behaved “primary key.” It uniquely identifies your account, but does nothing more. And the credit-card company doesn’t give a damn whether it is “consecutive.” Any valid but otherwise-random string of numbers is equally satisfactory ... as any good primary-key should always be.