Software & Apps Apps 124 124 people found this article helpful One-to-Many Relationships in a Database The why and how of one-to-many relationships by Mike Chapple Writer Former Lifewire writer Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management. our editorial process Twitter Mike Chapple Updated on June 16, 2021 Tweet Share Email Apps Best Apps Payment Services A one-to-many relationship in a database occurs when each record in Table A may have many linked records in Table B, but each record in Table B may have only one corresponding record in Table A. A one-to-many relationship in a database is the most common relational database design and is at the heart of good design. Databases can also implement a one-to-one relationship and a many-to-many relationship. Erik Isakson / Getty Images Example of a One-to-Many Relationship Consider the relationship between a teacher and the courses they teach. A teacher can teach multiple classes, but the course would not have the same relationship with the teacher. Therefore, for each record in a Teachers table, there could be many records in the Courses table. This example illustrates a one-to-many relationship: one teacher to multiple courses. Why Establishing a One-to-Many Relationship Is Important To represent a one-to-many relationship, you need at least two tables. Let's see why. Adherence to First Normal Form Design Perhaps we created a table in which we want to record the name and courses taught. We might design a Teachers and Courses table like this: Teacher_ID Teacher_Name Course Teacher_001 Carmen Biology Teacher_002 Veronica Math Teacher_003 Jorge English What if Carmen teaches two or more courses? We have two options with this design. We could add it to Carmen's existing record, like this: Teacher_ID Teacher_Name Course Teacher_001 Carmen Biology, Math Teacher_002 Veronica Math Teacher_003 Jorge English However, the design above is inflexible and could result in problems later when you insert, edit, or delete data. It makes it difficult to search for data. This design also violates the first principle of database normalization, First Normal Form (1NF), which states that each table cell should contain a single, discrete piece of data. The Second Normal Form Rule Another design alternative might be to add a second record for Carmen: Teacher_ID Teacher_Name Course Teacher_001 Carmen Biology Teacher_001 Carmen Math Teacher_002 Veronica Math Teacher_003 Jorge English This approach adheres to 1NF but is still poor database design because it introduces redundancy and could bloat a large database unnecessarily. More importantly, the data could become inconsistent. For example, what if Carmen's name changed? Someone working with the data might update her name in one record and fail to update it in the second record. This design violates the Second Normal Form (2NF) standard, which adheres to 1NF and must also avoid the redundancies of multiple records. The 2NF rule achieves this by separating subsets of data into multiple tables and creating a relationship between them. How to Design a Database With One-to-Many Relationships To implement a one-to-many relationship in the Teachers and Courses table, break the tables into two and link them using a foreign key. Here, we removed the Course column in the Teachers table: Teacher_ID Teacher_Name Teacher_001 Carmen Teacher_002 Veronica Teacher_003 Jorge And here is the Courses table. Note that its foreign key, Teacher_ID, links a course to a teacher in the Teachers table: Course_ID Course_Name Teacher_ID Course_001 Biology Teacher_001 Course_002 Math Teacher_001 Course_003 English Teacher_003 We have developed a relationship between the Teachers and the Courses table using a foreign key. This arrangement tells us Carmen teaches both Biology and Math and that Jorge teaches English. We can see how this design avoids any possible redundancies, allows individual teachers to teach multiple courses, and implements a one-to-many relationship. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Email Address Sign up There was an error. Please try again. You're in! Thanks for signing up. There was an error. Please try again. Thank you for signing up. Tell us why! Other Not enough details Hard to understand Submit More from Lifewire What Is a Database Relationship? What Is Transitive Dependency in a Database Introduction to Database Relationships Putting a Database in First Normal Form Full Functional Dependency in Database Normalization Entity-Relationship Diagram Definition Putting a Database in Third Normal Form (3NF) Definition of Database Relation Database Normalization Basics Facts Vs. Dimensions Tables in a Database A Database Attribute Defines the Properties of a Table What Is a Database Instance? Glossary of Common Database Terms Creating Database Relationships in Access What Is a Primary Key? What Is a Database Schema?