(window.webpackJsonp=window.webpackJsonp||[]).push([[3297],{3705:function(e,t,a){"use strict";a.r(t);var v=a(31),_=Object(v.a)({},(function(){var e=this,t=e.$createElement,a=e._self._c||t;return a("ContentSlotsDistributor",{attrs:{"slot-key":e.$parent.slotKey}},[a("h1",{attrs:{id:"table-design"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#table-design"}},[e._v("#")]),e._v(" Table Design")]),e._v(" "),a("h2",{attrs:{id:"properties-of-a-well-designed-table"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#properties-of-a-well-designed-table"}},[e._v("#")]),e._v(" Properties of a well designed table.")]),e._v(" "),a("p",[e._v("A true relational database must go beyond throwing data into a few tables and writing some SQL statements to pull that data out."),a("br"),e._v("\nAt best a badly designed table structure will slow the execution of queries and could make it impossible for the database to function as intended.")]),e._v(" "),a("p",[e._v("A database table should not be considered as just another table; it has to follow a set of rules to be considered truly relational.  Academically it is referred to as a 'relation' to make the distinction.")]),e._v(" "),a("p",[a("strong",[e._v("The five rules of a relational table are:")])]),e._v(" "),a("ol",[a("li",[e._v("Each value is "),a("strong",[e._v("atomic")]),e._v("; the value in each field in each row must be a single value.")]),e._v(" "),a("li",[e._v("Each field contains values that are of the same data type.")]),e._v(" "),a("li",[e._v("Each field heading has a unique name.")]),e._v(" "),a("li",[e._v("Each row in the table must have at least one value that makes it unique amongst the other records in the table.")]),e._v(" "),a("li",[e._v("The order of the rows and columns has no significance.")])]),e._v(" "),a("p",[a("strong",[e._v("A table conforming to the five rules:")])]),e._v(" "),a("table",[a("thead",[a("tr",[a("th",[e._v("Id")]),e._v(" "),a("th",[e._v("Name")]),e._v(" "),a("th",[e._v("DOB")]),e._v(" "),a("th",[e._v("Manager")])])]),e._v(" "),a("tbody",[a("tr",[a("td",[e._v("1")]),e._v(" "),a("td",[e._v("Fred")]),e._v(" "),a("td",[e._v("11/02/1971")]),e._v(" "),a("td",[e._v("3")])]),e._v(" "),a("tr",[a("td",[e._v("2")]),e._v(" "),a("td",[e._v("Fred")]),e._v(" "),a("td",[e._v("11/02/1971")]),e._v(" "),a("td",[e._v("3")])]),e._v(" "),a("tr",[a("td",[e._v("3")]),e._v(" "),a("td",[e._v("Sue")]),e._v(" "),a("td",[e._v("08/07/1975")]),e._v(" "),a("td",[e._v("2")])])])]),e._v(" "),a("ul",[a("li",[e._v("Rule 1: Each value is atomic.  "),a("code",[e._v("Id")]),e._v(", "),a("code",[e._v("Name")]),e._v(", "),a("code",[e._v("DOB")]),e._v(" and "),a("code",[e._v("Manager")]),e._v(" only contain a single value.")]),e._v(" "),a("li",[e._v("Rule 2: "),a("code",[e._v("Id")]),e._v(" contains only integers, "),a("code",[e._v("Name")]),e._v(" contains text (we could add that it's text of four characters or less), "),a("code",[e._v("DOB")]),e._v(" contains dates of a valid type and "),a("code",[e._v("Manager")]),e._v(" contains integers (we could add that corresponds to a Primary Key field in a managers table).")]),e._v(" "),a("li",[e._v("Rule 3: "),a("code",[e._v("Id")]),e._v(", "),a("code",[e._v("Name")]),e._v(", "),a("code",[e._v("DOB")]),e._v(" and "),a("code",[e._v("Manager")]),e._v(" are unique heading names within the table.")]),e._v(" "),a("li",[e._v("Rule 4: The inclusion of the "),a("code",[e._v("Id")]),e._v(" field ensures that each record is distinct from any other record within the table.")])]),e._v(" "),a("p",[a("strong",[e._v("A badly designed table:")])]),e._v(" "),a("table",[a("thead",[a("tr",[a("th",[e._v("Id")]),e._v(" "),a("th",[e._v("Name")]),e._v(" "),a("th",[e._v("DOB")]),e._v(" "),a("th",[e._v("Name")])])]),e._v(" "),a("tbody",[a("tr",[a("td",[e._v("1")]),e._v(" "),a("td",[e._v("Fred")]),e._v(" "),a("td",[e._v("11/02/1971")]),e._v(" "),a("td",[e._v("3")])]),e._v(" "),a("tr",[a("td",[e._v("1")]),e._v(" "),a("td",[e._v("Fred")]),e._v(" "),a("td",[e._v("11/02/1971")]),e._v(" "),a("td",[e._v("3")])]),e._v(" "),a("tr",[a("td",[e._v("3")]),e._v(" "),a("td",[e._v("Sue")]),e._v(" "),a("td",[e._v("Friday the 18th July 1975")]),e._v(" "),a("td",[e._v("2, 1")])])])]),e._v(" "),a("ul",[a("li",[e._v("Rule 1: The second name field contains two values - 2 and 1.")]),e._v(" "),a("li",[e._v("Rule 2: The DOB field contains dates and text.")]),e._v(" "),a("li",[e._v("Rule 3: There's two fields called 'name'.")]),e._v(" "),a("li",[e._v("Rule 4: The first and second record are exactly the same.")]),e._v(" "),a("li",[e._v("Rule 5: This rule isn't broken.")])]),e._v(" "),a("h4",{attrs:{id:"remarks"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#remarks"}},[e._v("#")]),e._v(" Remarks")]),e._v(" "),a("p",[e._v("The Open University (1999) Relational Database Systems: Block 2 Relational Theory,\nMilton Keynes, The Open University.")])])}),[],!1,null,null,null);t.default=_.exports}}]);