In a one-to-many relationship, each row in the related
to table can be related to many rows in the relating
table. This allows frequently used information to be
saved only once in a table and referenced many times
in all other tables. In a one-to-many relationship between
Table A and Table B, each row in Table A is linked to
0, 1 or many rows in Table B. The number of rows in
Table A is almost always less than the number of rows
in Table B.
To illustrate the one-to-many relationship consider
the sample table design and data below:
authors
table
============
author_id (primary key)
lastname
firstname
book_id (foreign key - link to book_id of
books table)
books table
===========
book_id (primary key)
title
author_id lastname firstname
-> book_id title
--------- -------- ---------
-> ------- ------
0001 henry john
-> 0001
a database primer
0002 building datawarehouse
0003 teach yourself sql
0002 johnson mary
-> 0004
101 exotic recipes
0003 bailey harry
-> 0005
visiting europe
0004 smith
adam
Notice that each row in the authors table is related
to 0, 1 or many rows in the books table. This makes
intuitive sense because an author can write 0, 1 or
more than 1 books. In our example above, John Henry
has written 3 books, Mary Johnson has written 1 book,
Harry Bailey has written 1 book and Adam Smith has not
written any books.
If you notice carefully, the above relationship between
the authors table and the books table is a one-to-many
relationship. Turning around, the relationship between
the books table and the authors table is a many-to-one
relationship.
