{"id":9,"date":"2007-09-21T01:24:57","date_gmt":"2007-09-21T06:24:57","guid":{"rendered":"http:\/\/www.efsavage.com\/blog\/posts\/database_naming_conventions\/"},"modified":"2007-09-21T16:16:45","modified_gmt":"2007-09-21T21:16:45","slug":"database_naming_conventions","status":"publish","type":"post","link":"https:\/\/efsavage.com\/blog\/posts\/database_naming_conventions\/","title":{"rendered":"Database Naming Conventions"},"content":{"rendered":"<p>Time for another naming convention. This time it&#8217;s something people care more about than <a href=\"http:\/\/www.efsavage.com\/blog\/posts\/java_package_conventions\/\">Java packages<\/a>, we&#8217;re talking about databases. Here are the rules, and the reasons behind them.<\/p>\n<h3>Use lowercase for everything<\/h3>\n<p>We&#8217;ve got 4 choices here:<\/p>\n<ul>\n<li>\n\t\tMixed case<\/p>\n<ul>\n<li>Some servers are case sensitive, some are not. MySQL for example, is case-insensitive for column names, case-insensitive on Windows for table names, but case-sensitive on Linux for table names.<\/li>\n<li>Error prone<\/li>\n<\/ul>\n<\/li>\n<li>\n\t\tNo convention<\/p>\n<ul>\n<li>Same reasons as mixed case<\/li>\n<\/ul>\n<\/li>\n<li>\n\t\tUpper case<\/p>\n<ul>\n<li>SQL is easier to scan when the reserved words are uppercase. This is valuable when scanning log files looking for things like WHERE statements and JOINs.<\/li>\n<li>MySQL will always dump table names on Windows in lowercase.<\/li>\n<\/ul>\n<\/li>\n<li>\n\t\tLowercase<\/p>\n<ul>\n<li>Works everywhere. Some servers, like Oracle, will appear to convert everything to uppercase, but it\u00e2\u20ac\u2122s just case-insensitive and you can use lowercase.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>Only use letters and underscores and numbers (sparingly)<\/h3>\n<ul>\n<li>Most servers support other characters, but there are no other characters which all the major servers support.<\/li>\n<li>\n\t\tNumbers should be used as little as possible. Frequent use is typically a symptom of poor normalization.<\/p>\n<ul>\n<li>address1, address2 is OK<\/li>\n<\/ul>\n<\/li>\n<li>Whitespace isn\u00e2\u20ac\u2122t allowed on most servers, and when it is you have to quote or bracket everything, which gets messy.<\/li>\n<\/ul>\n<h3>\n\tTable and column should be short, but not abbreviated.<br \/>\n\t<\/h2>\n<ul>\n<li>You&#8217;ve seen the same thing abbreviated every way possible, like cust_add, cus_addr, cs_ad, cust_addrs, why not just customer_address? We&#8217;re not writing code on 80 character terminals any more, and most people aren&#8217;t even writing SQL, so let&#8217;s keep it clear, OK?<\/li>\n<li>30 characters is considered the safe limit for portability, but give some serious thought before you go past 20.<\/li>\n<\/ul>\n<h3>Table names should be singular.<\/h3>\n<p>\tYes, singular! Oh yes, I went there. I used to use plural names, because it\u00e2\u20ac\u2122s more semantically accurate. After all if each record is a person, then a group of them would be people, right? Right, but who cares. SELECT * FROM person isn&#8217;t any less clear than people, especially if you&#8217;ve got a solid convention. You don&#8217;t use plurals when you&#8217;re declaring class names for a vector of generics do you? Also:<\/p>\n<ul>\n<li>\n\t\t\tEnglish plurals are crazy, and avoiding them is good.<\/p>\n<ul>\n<li>user -> users<\/li>\n<li>reply -> replies<\/li>\n<li>address -> addresses<\/li>\n<li>data -> data (unless its geographic, then it\u00e2\u20ac\u2122s datum -> data)<\/li>\n<\/ul>\n<\/li>\n<li>Singular names means that your primary key can always be tablename_id, which reduces errors and time.<\/li>\n<\/ul>\n<h3>Double Underscores for Associative Tables.<\/h3>\n<p>\tYou&#8217;ve got your person table, and your address table, and there&#8217;s a many-to-many between them. This table should be called address__person. Why? Well what if you have a legacy_customer table that also ties to address. Now you&#8217;ve got address__legacy_customer. A new developer can easily pick up this convention and will be able to break down the names accordingly. Remember, no matter what the Perl\/Lisp\/Ruby\/etc guys say, clarity of code is judged by how someone reads it, not how they write it.<\/p>\n<h3>Component Names of Associative Tables in Alphabetical Order.<\/h3>\n<p>\tThis rule is somewhat arbitrary, but still beneficial. There&#8217;s no good way to determine which goes first. Table size, &#8220;importance&#8221;, age, who knows what else, and those assessments may change over time. Or, you might find that your manager assigned the same task to two people, and now you&#8217;ve got an address__person and a person__address table co-existing peacefully, when you only need one. Everyone putting them in the same order makes reading and writing queries easier.<\/p>\n<p>\tThat&#8217;s all I&#8217;ve got for now, but I encourage you to offer your own, or even refute some of the ones above (with some reasoning, of course).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Time for another naming convention. This time it&#8217;s something people care more about than Java packages, we&#8217;re talking about databases. Here are the rules, and the reasons behind them. Use lowercase for everything We&#8217;ve got 4 choices here: Mixed case Some servers are case sensitive, some are not. MySQL for example, is case-insensitive for column&hellip; <\/p>\n<p class=\"simppeli-read-more\"><a href=\"https:\/\/efsavage.com\/blog\/posts\/database_naming_conventions\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Database Naming Conventions<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14,8,3],"tags":[],"series":[],"class_list":["post-9","post","type-post","status-publish","format-standard","hentry","category-conventions","category-databases","category-software"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p1AkJt-9","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/posts\/9","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/comments?post=9"}],"version-history":[{"count":0,"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/posts\/9\/revisions"}],"wp:attachment":[{"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/media?parent=9"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/categories?post=9"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/tags?post=9"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/efsavage.com\/blog\/wp-json\/wp\/v2\/series?post=9"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}