Some database theory

properly structuring your tables can significantly improve query execution and reduce overall database size by eliminating redundant data

every table should have a primary key (possibly multi-column)

basic organizational principles are called "normal forms" - each level assumes the previous levels have been satisfied as well

Normal forms:

1. every row in a table must have the same number of columns

   no cramming variable numbers of parents into a single column

2. a non-key column should not be a fact about a subset of a key

   if primary key was "name+birthday" then a birth-month column is not advised

3. a non-key column should not be a fact about another non-key column

   adding a first-initial of name column to our bird table is bad form, since it is redundant information

4. a row should not contain two or more independent multi-valued facts about an entity

   adding "friend" and "enemy" columns to our birds table is problematic, since it can create redundant data and lead to uncertainties with maintanence policies

   here are examples of how different ways of dealing with two multi-valued columns can cause problems:

   Id	Name	Friend	Enemy
   1	Peter	Mary	Jack
   1	Peter	Frank	Bob
   1	Peter	Albert	NULL

   Id	Name	Friend	Enemy
   1	Peter	Mary	Jack
   1	Peter	Frank	Bob
   1	Peter	Albert	Bob

   Id	Name	Friend	Enemy
   1	Peter	Mary	NULL
   1	Peter	NULL	Jack
   1	Peter	Frank	NULL
   1	Peter	NULL	Bob
   1	Peter	Albert	NULL

   it is better to separate the friends and enemies out into separate tables which link bird id to friend or enemy id

5. beyond the scope... too long to explain

   deals with situations where specific logical constraints exist