You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
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
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 |
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
Advertising
Advertising: