What is a relational database?

a database where queries (questions made of the database) can span multiple tables (or the same table used multiple times)

types of relationships used in relational databases:

• one-to-one: just put all info for both "things" in one table
• many to many: need separate table for linkages
• one-to-many: reference the "one" id from the "many" table
• many-to-one: switch viewpoints, apply rule above

take our slightly improved table layout:

Id	Bird Name	Birthday	Male	Parents
1	Peter	2004-11-05	T	Mary, Bob
2	John	2004-12-21	T	Mary
3	Mary	2003-05-02	F	Ellen
4	Jane	2005-07-12	F	Mary, John
5	Bob	2003-06-10	T	Ellen
6	Ellen	2002-11-27	F	NULL

and split it up into two tables to enable relational queries about parents:

Id	Bird Name	Birthday	Male
1	Peter	2004-11-05	T
2	John	2004-12-21	T
3	Mary	2003-05-02	F
4	Jane	2005-07-12	F
5	Bob	2003-06-10	T
6	Ellen	2002-11-27	F

Bird_Id	Parent_Id
1	3
1	5
2	3
3	6
4	3
4	2
5	6

things to note:

• id in bird table is a guaranteed unique positive-definite integer, and is consecutive by default unless explicitly set
• birthday instead of age eliminates need to update on birthday or daily, allows use of all built-in date functions
• boolean male/female with not_null set allows only two possibilities
• the ids in both columns of the new "parents table" refer to bird ids
• parents table has a multiple-column primary key - no repeats in the table are possible
• if the parent of every bird was known there would be no need for a "parents table" - just add two parent id columns in the bird table
• we do not have to deal with complicated unpacking of multiple parents from a parents name string and we are spared the hassle of dealing with NULL or empty parent fields
• At least one parent of John, Mary, Bob and Ellen are unknown, and these facts fit naturally in the table structure using zero storage
• ... and no need to deal with a NULL (or unknown) parent case
• we are "saving" 6 * 256 - 7 * 2 * 2 bytes = 1508 bytes of space (ignoring fixed size table overhead)
• parent queries will be lightning fast, as no string processing is required