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

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice