{"id":82023,"date":"2007-10-07T14:56:53","date_gmt":"2007-10-07T14:56:53","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73171"},"modified":"2019-05-22T09:24:11","modified_gmt":"2019-05-22T09:24:11","slug":"normalizations-other-little-side-effect","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/normalizations-other-little-side-effect\/","title":{"rendered":"Normalization&#8217;s other little side effect&#8230;"},"content":{"rendered":"<p>It isn&#8217;t that I don&#8217;t like a challenge, really it isn&#8217;t.\u00a0 I like puzzles, mazes, Suduko, video games with challenging levels where I have to really think about a problem to solve it.\u00a0 So why don&#8217;t I like poorly\u00a0designed databases, where finding a value that you want can be just as challenging, or maybe more?\u00a0 Glad you asked (or maybe you didn&#8217;t, what do I care, this is my blog \ud83d\ude42\u00a0\u00a0 And for any of you who might be asking: &#8220;Shouldn&#8217;t I be able to use SQL Server like I want to?&#8221;\u00a0\u00a0It is a good thing you didn&#8217;t actually ask that out loud, or\u00a0I would have to ban you \ud83d\ude42<\/p>\n<p>When someone creates a puzzle, maze, or video game, they design in the challenge purposefully.\u00a0<a href=\"https:\/\/www.itprotoday.com\/author\/itzik-ben-gan\" target=\"_blank\" rel=\"noopener\">Itzik Ben-Gan<\/a>\u00a0frequently sets up SQL puzzles on SQL Server Magazine that can be kind of fun.\u00a0 But when someone builds a poorly designed database, then don&#8217;t leave you breadcrumbs to find your way around.\u00a0 Quite the opposite.\u00a0 If they had spent time trying to think about how other people might get around in their database, they would have naturally started normalizing.\u00a0\u00a0 No, usually a poorly designed database is an act of &#8220;selfishness&#8221; or &#8220;ignorance&#8221; by a person who is not thinking at all.\u00a0 Or even worse, was.<\/p>\n<p>This all comes up because I spent a good amount of my week this week digging through a database.\u00a0First I would identify some data, go back to some other people with some knowledge of the data, have a meeting, then go back and try again.\u00a0\u00a0 What\u00a0should have been a quick, painless task to identify 20 columns from this database took much longer than necessary.\u00a0 Not 100s of tables worth, no, just a few measly columns.\u00a0 So what was wrong?<\/p>\n<ul>\n<li>Columns that had meaning in one row, but not another<\/li>\n<li>Columns named FieldName1, FieldName2&#8230;<\/li>\n<li>Two related tables that were not related by key, but by a concatenation of columns<\/li>\n<li>One database per day of activity, not a key that denotes a different day<\/li>\n<li>Data with embedded values, and not always the same format.\u00a0<\/li>\n<li>Domain value with no apparent meaning<\/li>\n<\/ul>\n<p>I could go on, but I would just be making stuff up.\u00a0 And frankly this is enough mess for a Sunday afternoon.\u00a0 The problem is, had someone taken the time to design this database using proper normalization techniques, none of these problems would be evident.\u00a0 So what is the side effect?<\/p>\n<p><strong>Documentation<\/strong><\/p>\n<p>Not that it is impossible to build an incomprehensible normalized database (lots of companies do stuff to make it hard for competitors to understand their data (like having column names be non-sensical without their data dictionary).\u00a0 But the problem here is that yet another person spent time creating a &#8220;general purpose&#8221; database.\u00a0 It never ceases to amaze me the lengths that people will go to never change tables in SQL.\u00a0 So they do all of this nasty mapping in their code.\u00a0<\/p>\n<p>But you know what.\u00a0 It turns out that SQL Server has a lot of really cool stuff that lets you customize data storage.\u00a0 For example:<\/p>\n<p>ALTER TABLE allows you to add columns<\/p>\n<p>CREATE TABLE allows you to create tables<\/p>\n<p>sp_addextendedproperty allows you to add documentation to these properties<\/p>\n<p>Note that I am not necessarily suggesting that the answer to all &#8220;open schema&#8221; type problems necessarily should\u00a0(or even could) be solved by simply adding to the schema.\u00a0 In many cases you would not want that at all.\u00a0 But in this case, I am not talking about that situation.\u00a0 Why?\u00a0 Because the code had to change&#8230;<\/p>\n<p>If the code that accesses the data needs to change based on the structure, extend the data structures.\u00a0 It will\u00a0save you code if your database is cleanly created and normalized. \u00a0<strong><em>Use SQL to do the job it was made for.<\/em><\/strong>\u00a0 Then, it will be easy to go to one table that has a name that matches what I think I want, see what it is used for, then go to related tables, and to their related tables and so on.\u00a0 Legal values for a column will be documented and checked.\u00a0 Names given to columns will reflect their meaning.\u00a0 You might even have descriptions stored in extended properties.<\/p>\n<p>And then a job that took a week could have taken an hour.\u00a0 (On the other hand, I get another anecdote for my presentation next weekend at devlink\u00a0and for my next book.\u00a0 And this blog.\u00a0 Maybe I do like poorly normalized databases&#8230;Nah!)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It isn&#8217;t that I don&#8217;t like a challenge, really it isn&#8217;t.\u00a0 I like puzzles, mazes, Suduko, video games with challenging levels where I have to really think about a problem to solve it.\u00a0 So why don&#8217;t I like poorly\u00a0designed databases, where finding a value that you want can be just as challenging, or maybe more?\u00a0&#8230;&hellip;<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82023","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82023","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82023"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82023\/revisions"}],"predecessor-version":[{"id":84350,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82023\/revisions\/84350"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82023"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82023"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82023"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}