{"id":24,"date":"2008-07-21T14:58:33","date_gmt":"2008-07-21T19:58:33","guid":{"rendered":"http:\/\/www.peteonsoftware.com\/?p=24"},"modified":"2008-12-22T16:05:13","modified_gmt":"2008-12-22T20:05:13","slug":"sql-reference-tables","status":"publish","type":"post","link":"https:\/\/www.peteonsoftware.com\/index.php\/2008\/07\/21\/sql-reference-tables\/","title":{"rendered":"Sql Reference Tables"},"content":{"rendered":"<p><img decoding=\"async\" src=\"https:\/\/www.peteonsoftware.com\/images\/July2008\/ReferenceBooks.jpg\" alt=\"Reference Book Image from http:\/\/www.sxc.hu\/photo\/1022436\" title=\"Reference Book Image from http:\/\/www.sxc.hu\/photo\/1022436\" style=\"float:left; padding: .5em;\" \/><br \/>\n<em>Disclaimer: Personally, I prefer to use uniqueidentifier as my primary key data type. It cuts down on improper joins (similar looking values aren&#8217;t in every table, ie 1, 2, 3) and is infinitely portable among environments.  I think the extra space and overhead is worth it.<\/em><\/p>\n<p>We have some reference tables in our database at work.  This itself isn&#8217;t abnormal, everyone has these.  What made these tables special was that the IDENTITY Primary Key for the table started at 0.  At first I thought that this was awesome.  I know it is a debate for another day, but I love that my arrays, counters, etc start at 0 in C#.  I know it may not be necessary anymore or whatever, but it comforts me, okay? \ud83d\ude42 <\/p>\n<p>Things soon changed for the worse when I realized that 0 being a significant data value was a problem.  &#8220;Why?&#8221;, you may ask.  Well, when you just generically &#8220;new up&#8221; an int, what is the default value?  That&#8217;s right, 0.  So, if someone doesn&#8217;t set a property when trying to identify some reference value, in a 1-based table (or a GUID one) you will get an error because that isn&#8217;t a valid value.  However, in a 0-based table, that &#8220;forgetfulness&#8221; will match right up with a value and go in no questions asked.  You can&#8217;t validate it, because 0 is perfectly fine to have.<\/p>\n<p><strong>Lesson Learned:<\/strong> Zeroes are fine for arrays and loops, but keep them out of reference tables!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Disclaimer: Personally, I prefer to use uniqueidentifier as my primary key data type. It cuts down on improper joins (similar looking values aren&#8217;t in every table, ie 1, 2, 3) and is infinitely portable among &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24,87],"tags":[92,4],"class_list":["post-24","post","type-post","status-publish","format-standard","hentry","category-rant","category-sql","tag-rant","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts\/24","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/comments?post=24"}],"version-history":[{"count":0,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts\/24\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/media?parent=24"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/categories?post=24"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/tags?post=24"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}