Monday, July 19, 2010

Design MySQL

1) Normalization: carefully eliminate redundancies and other problems that will undermine the integrity of your data.
2) Design Primary key and Foreign key:
A primary key is a unique identifier that has to abide by certain rules. They must
  • Always have a value (it cannot be NULL)

  • Have a value that remains the same (never changes)

  • Have a unique value for each record in the table

Foreign keys are the representation of the primary key from Table A in Table B.

If no logical primary key exists, invent one.

3) Relationships

A relationship between two tables can be one-to-one, one-to-many, or many-to-many.

4) Create Normal Form

create first normal form , second and third normal form.
each normal form should have its primary key, reduce many-to-many
between tables.

5) Design MySQL data type

Ty p e

S i z e

D e s c r i p t i o n

CHAR[Length]

Length bytes

A fixed-length field from 0 to 255 characters long.

VARCHAR(Length)

String length + 1 bytes

A fixed-length field from 0 to 255 characters long.

TINYTEXT

String length + 1 bytes

A string with a maximum length of 255 characters.

TEXT

String length + 2 bytes

A string with a maximum length of 65,535 characters.

MEDIUMTEXT

String length + 3 bytes

A string with a maximum length of 16,777,215 characters.

LONGTEXT

String length + 4 bytes

A string with a maximum length of 4,294,967,295 characters.

TINYINT[Length]

1 byte

Range of -128 to 127 or 0 to 255 unsigned.

SMALLINT[Length]

2 bytes

Range of -32,768 to 32,767 or 0 to 65535 unsigned.

MEDIUMINT[Length]

3 bytes

Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.

INT[Length]

4 bytes

Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.

BIGINT[Length]

8 bytes

Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned.

FLOAT

4 bytes

A small number with a floating decimal point.

DOUBLE[Length, Decimals]

8 bytes

A large number with a floating decimal point.

DECIMAL[Length, Decimals]

Length + 1 or Length + 2 bytes

A DOUBLE stored as a string, allowing for a fixed decimal point.

DATE

3 bytes

In the format of YYYY-MM-DD.

DATETIME

8 bytes

In the format of YYYY-MM-DD HH:MM:SS.

TIMESTAMP

4 bytes

In the format of YYYYMMDDHHMMSS; acceptable range ends inthe year 2037.

TIME

3 bytes

In the format of HH:MM:SS

ENUM

1 or 2 bytes

Short for enumeration, which means that each column can haveone of several possible values.

SET

1, 2, 3, 4, or 8 bytes

Like ENUM except that each column can have more than one ofseveral possible values.

No comments:

Post a Comment