{"id":1002,"date":"2006-03-07T23:17:33","date_gmt":"2006-03-08T03:17:33","guid":{"rendered":"http:\/\/www.hoogervorst.ca\/arthur\/?p=1002"},"modified":"2006-03-07T23:28:09","modified_gmt":"2006-03-08T03:28:09","slug":"varchars-kill-the-integers","status":"publish","type":"post","link":"http:\/\/www.hoogervorst.ca\/arthur\/?p=1002","title":{"rendered":"Varchars kill the integers"},"content":{"rendered":"<p><span class=\"dropcap\">I<\/span>know several professional applications that use varchar fields to store typically invoice and order numbers, just because it makes it easier to fill this field with non-numerical characters (for example when issuing credit notes and that).\n<\/p>\n<p>This is pretty irritating on the part of indexing and sorting results: after all, which comes first when using alphanumerical sorting: &#8216;10001&#8217; or &#8216;200-1&#8217;? Most likely, you&#8217;ll lose a lot of speed too: sorting (and comparing) integers goes a lot faster than comparing sets of characters.\n<\/p>\n<p>Instead of declaring these types of fields varchars, consider making them integer fields: after all, adding dashes (and other non-numerical characters) can be easily made part of a <em>formatting<\/em> mask in the UI of the application: heck, most graphical UI have masked edit field controls (Win 32\/.Net\/KDE\/Gnome).\n<\/p>\n<p>Additionally, on typical transactional databases like Postgres and Oracle, (integer) sequences are created atomically [transaction independent], which can save you a lot more headaches than trying to generate a unique combination of characters in a <em>multi-user<\/em> environment.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Iknow several professional applications that use varchar fields to store typically invoice and order numbers, just because it makes it easier to fill this field with non-numerical characters (for example when issuing credit notes and that). This is pretty irritating &hellip; <a href=\"http:\/\/www.hoogervorst.ca\/arthur\/?p=1002\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[15],"tags":[],"_links":{"self":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/1002"}],"collection":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1002"}],"version-history":[{"count":0,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=\/wp\/v2\/posts\/1002\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1002"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1002"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.hoogervorst.ca\/arthur\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1002"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}