Database Normalization

The main goal of normalization is to eliminate data redundancy.

First Normal Form (1NF)

Each attribute in the entity has a single value for each instance of the entity.  For example, if a CD entity can have more than one song, then “songs” or “song1,” “song2,”, etc. should NOT be attributes of the CD entity but Song should be its own entity.  Each entity of song would then have information about that song (such as title and length) and a unique identifier for a particular instance of a CD entity.

Second Normal Form (2NF)

1NF plus each attribute must be entirely dependent on the entity’s unique identifier.  Any attribute whose data will not be unique to the entity must be placed in a different entity or have a new entity created for it.  For example, if a CD entity has the attribute Band Name but more than one CD can have the same Band Name, an entity called Band should be created and given a unique identifier attribute by which CD entities can link to it.  This would not be in first normal form, however, if more than one Band Name could be associated with the CD: CD entities would need more than one attribute holding Band Name unique identifiers.  Assuming each song has only one band, the Song entity should hold the one Band Name identifier attribute.

Third Normal Form (2NF)

2NF plus no non-identifying attributes are dependent on any other nonidentifying attributes.  Attributes that are dependent on other nonidentifying attributes are normalized by moving both the dependent attribute and the attribute on which it is dependent into a new entity.  In other words, two related pieces of information—related such that when one is a particular thing the other will be a specific other thing—must be given their own entity.  For example, if both a State Name and State Abbreviation are going to be kept track of, the State Abbreviation depends on the State Name (or vice versa) so there should be a State entity with the attributes Name and Abbreviation.

Pages 15 to 25

Adapted from MySQL and mSQL by Randy Jay Yarger, George Reese, and Tim King, published by O’Reilly & Associates, Inc., 1999.

Also from this book: MySQL database maintenance

when adding data often, run isamchk -a often, about every time size of database doubles.

run isamchk -d once or twice a year.  If you find that the number of deleted blocks used by your tables is a signifcant portion of your disk space run isamchk -r to rebuild your tables without the unneeded space.

(always back up data before running isamchk)

And: PHP

addslashes

$escaped_string = addslashes($string); Returns a copy of string with any $ \ or ' characters escaped.