{"id":393,"date":"2008-08-19T00:50:07","date_gmt":"2008-08-19T05:50:07","guid":{"rendered":"http:\/\/www.juixe.com\/techknow\/index.php\/2008\/08\/19\/database-best-practices\/"},"modified":"2008-08-19T00:50:07","modified_gmt":"2008-08-19T05:50:07","slug":"database-best-practices","status":"publish","type":"post","link":"http:\/\/juixe.com\/techknow\/index.php\/2008\/08\/19\/database-best-practices\/","title":{"rendered":"Database Best Practices"},"content":{"rendered":"<p>Every production application I have been involved with has made use of a database.  As a programmer, you don&#8217;t have to be a DBA, but just as with software development you need to adhere to a core set of database best practices.  Below are a few of the best practices I try to follow.<\/p>\n<ul>\n<li>Use a database modeling tool that is vendor neutral.<\/li>\n<li>Decide upon a database naming convention, standardize it across your organization and be consistent in following through with it.  Always be consistent with the use of case and plural vs singular.<\/li>\n<li>Have a similar naming between your table columns and object properties.<\/li>\n<li>Try not to use system tables directly as they are not SQL standard and may change between releases.<\/li>\n<li>Do not use SELECT * in your queries, explicitly write the required columns names in the select statement.<\/li>\n<li>Always use a column list in your insert statements.  This helps in avoiding problems when the table structure changes, like adding or removing columns.<\/li>\n<li>Try to avoid wildcard characters at the beginning of the word while searching using the LIKE keyword.<\/li>\n<li>Use the more readable ANSI standard join clause instead of he told style join.<\/li>\n<li>If you have a choice, do not store binary files in the database.  Instead store the path to the binary file in the database and store the file in the disk, preferably another disk drive.<\/li>\n<li>Offload tasks like string manipulations, concatenations, row numbering, case conversions, type conversions, etc to the front-end application.<\/li>\n<li>Store computed string manipulations, concatenation, case conversion, type conversions, etc in the database so you don&#8217;t have to waste CPU cycles recalculating.<\/li>\n<li>Avoid leaking your database design details, such as primary keys.<\/li>\n<li>Just like your code, be sure to comment your database design.<\/li>\n<li>Use the bulk\/multi insert statement.<\/li>\n<li>Study the SQL execution plan prior to a major release.<\/li>\n<\/ul>\n<p>Again, as a software developer you don&#8217;t have to be a database tuning expert but you also can&#8217;t be excused for not following the above best practices.<\/p>\n<p>Technorati Tags: <a href=\"http:\/\/technorati.com\/tag\/database\" rel=\"tag\">database<\/a>, <a href=\"http:\/\/technorati.com\/tag\/db\" rel=\"tag\"> db<\/a>, <a href=\"http:\/\/technorati.com\/tag\/sql\" rel=\"tag\"> sql<\/a>, <a href=\"http:\/\/technorati.com\/tag\/best+practices\" rel=\"tag\"> best practices<\/a>, <a href=\"http:\/\/technorati.com\/tag\/join\" rel=\"tag\"> join<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Every production application I have been involved with has made use of a database. As a programmer, you don&#8217;t have to be a DBA, but just as with software development you need to adhere to a core set of database best practices. Below are a few of the best practices I try to follow. Use [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","footnotes":""},"categories":[10,23,3],"tags":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p902K-6l","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts\/393"}],"collection":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/comments?post=393"}],"version-history":[{"count":0,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/posts\/393\/revisions"}],"wp:attachment":[{"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/media?parent=393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/categories?post=393"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/juixe.com\/techknow\/index.php\/wp-json\/wp\/v2\/tags?post=393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}