{"id":729,"date":"2012-08-27T13:01:02","date_gmt":"2012-08-27T13:01:02","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=729"},"modified":"2012-08-27T13:01:02","modified_gmt":"2012-08-27T13:01:02","slug":"database-schema-data-type-consistency","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/database-schema-data-type-consistency\/","title":{"rendered":"Database schema data-type consistency"},"content":{"rendered":"<p>Data-type consistency is not a concern with database schema design alone, it of course applies to any code you write, especially in a &quot;<a href=\"http:\/\/en.wikipedia.org\/wiki\/Strong_typing\">strong typed<\/a>&quot; language.<\/p>\n<p>When I say <strong>data-type consistency<\/strong>, I&#39;m talking about the need for fields or variables which carry the same type of data to have the same data-type.<\/p>\n<p>This can be especially important in a database schema, as we&#39;re more likely to suffer from large amounts of unneeded storage space.<\/p>\n<p>But, the larger issue comes with performing logic operations on that data.<\/p>\n<p>Let&#39;s take, for example, a MySQL database where a <em>price <\/em>column is set as a <strong>DECIMAL(8,2)<\/strong> in one table, and a <strong>FLOAT <\/strong>in another table. A comparison like <\/p>\n<p><code>table1.price = table2.price<br \/>\n\t<\/code><\/p>\n<p>WILL NOT work, even if the value appears to be the same.<\/p>\n<p>Using the above as an example, if a <strong>DECIMAL(8,2)<\/strong> is the most appropriate data-type for your data, it should be used consistently in place of a <strong>FLOAT <\/strong>type, not only for comparison purposes, but because it requires less storage space.<\/p>\n<p>In looking at database schemas over the years, I&#39;d have to say the most common area where I see this is with foreign keys. One table will have a primary key with data-type <strong>SMALLINT<\/strong>, where another table will relate with a foreign key whose data-type is <strong>INTEGER<\/strong>. While logic operations will work properly in this case, there is quite a bit of wasted storage space on the <strong>INTEGER <\/strong>field.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Data-type consistency is not a concern with database schema design alone, it of course applies to any code you write, especially in a &quot;strong typed&quot;&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[13],"tags":[199],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/729"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=729"}],"version-history":[{"count":1,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/729\/revisions"}],"predecessor-version":[{"id":730,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/729\/revisions\/730"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=729"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=729"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=729"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}