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
books table
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
To illustrate the one-to-many relationship consider the sample table design and data below:
authors table
column | |
---|---|
author_id | primary key |
lastname | |
firstname |
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 |
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