One-to-many relationship

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

column
author_id	primary key
lastname
firstname

books table

column
book_id	primary key
title
author_id	foreign key - link to author_id of authors table

author_id	lastname	firstname
0001	henry	john
0002	smith	adam
0003	johnson	mary
0004	bailey	harry

book_id	title	author_id
0001	A database primer	0001
0002	Building a datawarehouse	0001
0003	Teach yourself SQL	0001
0004	101 exotic recipes	0002
0005	Visiting europe	0004

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, Adam Smith has written 1 book, Mary Johnson has not written any book and Harry Bailey has written 1 book.

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.


See also: One-to-One relationships and Many-to-Many relationships