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
author_id primary key

books table
book_id primary key
author_id foreign key - link to author_id of authors table

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

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