Thinking in programming

Things I like to talk about programming

Conventions for Creation of SQL Queries, Indexes Tables and Fields

with 4 comments

  1. Whenever added a new table/field to the database, a comment with his purpose must be included.
  2. Use NOT NULL. Always define columns as NOT NULL unless there is a very good reason not to do so:
    • can save up to a byte per column per row of data
    • nullable columns make indexes, index statistics, and value comparisons more complicated.
  3. Use UNIQUE INDEX. If you have verified that each value for that index will/should not be repeated, then use an unique index (UNIQUE INDEX).
  4. Declaring the data type of a field to the more fixed-value, i. e., if you know the value of an int field is always going to become smaller than 10000, the you should use SMALLINT instead of INT.
  5. Use the smallest possible value for the lengths of the text fields. If you know that a zip code field will always hold not more than 10 characters, then you should use VARCHAR (10) and not VARCHAR (25).
    In cases like encrypted values with the MD5 algorithm, use CHAR (32).
  6. While write reserved keys use always capitalized sustained.

Useful info about the most common MySQL Data Types:

/* Unsigned Numeric Data Types */

TINYINT 0 to 255

SMALLINT 0 to 65535

MEDIUMINT 0 to 16777215

INT 0 to 4294967295

BIGINT 0 to 18446744073709551615

/* Text Data Types */

TEXT 65535 OR 64KB characters

MEDIUMTEXT 16777215 OR 16MB characters

LONGTEXT 4294967295 OR 4GB
characters

Advertisements

Written by roger.padilla

March 31, 2009 at 09:25

4 Responses

Subscribe to comments with RSS.

  1. Great site this rogerpadilla.wordpress.com and I am really pleased to see you have what I am actually looking for here and this this post is exactly what I am interested in. I shall be pleased to become a regular visitor 🙂

    sequewenuanty

    April 5, 2009 at 20:51

  2. great site this rogerpadilla.wordpress.com great to see you have what I am actually looking for here and this this post is exactly what I am interested in. I shall be pleased to become a regular visitor 🙂

    EXCILKY

    April 11, 2009 at 02:23

  3. Here you can contain accomplished dating resources….
    Good Dating Resource

    stockLoAkstuh

    April 14, 2009 at 09:57

  4. formidable site this rogerpadilla.wordpress.com terrific to see you have what I am actually looking for here and this this post is exactly what I am interested in. I shall be pleased to become a regular visitor 🙂

    EXCILKY

    April 18, 2009 at 09:38


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: