SQL and RDBMS Database Design DO's and DON'Ts

SQL and RDBMS Database Design DO's and DON'Ts
------------------------------------------
Generic DOs:

   1. Develop a backup strategy before you encounter a catastrophic database failure, and TEST it regularly so you will know what to do when disaster strikes.
   2. Always sanitize your input from the user to strip out quotes for strings and non-numeric data for number types. URL
   3. Perform table normalization in your DESIGN phase. It is much easier to change your tables 'on paper' than when it is in production use. URL
   4. Choose datatypes which are logical and fit your model. Designating a US Social Security number as a CHAR( 255 ) is very wasteful since they will not exceed 11 characters (XXX-XX-XXXX). CHAR( 11 ) is a perfect match.
   5. Run every query through your RDBMS' query tuning tool and ensure that correct indexes are being used and that a table scan or Cartesian product (in a join) is not occurring when you do not want it to. MS SQL, MySQL, Sybase


Generic DON'Ts:

   1. Do not fall into the trap of what I like to call 'Auto_Increment Induced Insanity'. Not every table needs a sequential integer as the primary key. Design your primary key logically based upon your data, and if need be then consider a sequential integer if traditional primary key design fails.
   2. Do not try and join mismatched datatypes. For instance, if you have an INT as a primary key in one table and a CHAR( 10 ) as the foreign key in another, problems will occur when you try and join the two columns, usually manifesting as a table-scan on one of the mis-matched tables. Or to put it another way -- ensure datatypes match across tables.
   3. Remember queries which use wild cards such as SELECT * WHERE myCharColumn LIKE '%anything' will always result in a table scan, so only use leading wild cards when absolutely necessary.
   4. Don't create redundant indexes. In most, if not all, RDBMS', designating something as a PRIMARY KEY will also create a unique index on the column(s). Creating an additional index on the leading column will be redundant and a waste of space. For example, creating an index on Col1, Col2 will be used on queries of type: WHERE Col1 = 34 AND Col2 = 'Something'. It will also be used on queries such as WHERE Col1 = 123.
   5. Don't use the $dbms_seek() functions in PHP to simulate pagination of a result set. Use LIMIT in MySQL, TOP n in MS SQL, etc. to achieve true pagination.


MySQL Specific DON'Ts:

   1. Do not enclose numeric values in quotes. This is very non-standard and ONLY works on MySQL. For example, WHERE someIntegerColumn = '1';. This also pertains to integer values in CREATE TABLE statements such as CREATE TABLE bob ( bobID INT DEFAULT '0' ) <-- bad. DEFAULT 0 is the correct method.
   2. Do not use the INT( M ) syntax unless you are using the ZEROFILL MySQL proprietary SQL extension.

[Join us @ facebook]

That's a very good article for starters forumlaunch.

Thanks for sharing it with as

[Guest]

[59.828]

Googlebot, fegueroarivka