How To Software Database Relationships Database relationships are the backbone of all relational databases Share Pin Email Print Software Databases Documents Spreadsheets Presentations Desktop Publishing Graphic Design Animation & Video by Mike Chapple An IT professional with more than 10 years of experience in the fields of databases and cybersecurity. Updated March 22, 2018 51 51 people found this article helpful A relationship is established between two database tables when one table has a foreign key that references the primary key of another table. This is the basic concept behind the term relational database. How a Foreign Key Works to Establish a Relationship Let's review the basics of primary and foreign keys. A primary key uniquely identifies each record in the table. It is a type of candidate key that is usually the first column in a table and can be automatically generated by the database to ensure that it is unique. A foreign key is another candidate key (not the primary key) used to link a record to data in another table. For example, consider these two tables that identify which teacher teaches which course. Here, the Courses table's primary key is Course_ID. Its foreign key is Teacher_ID: Course_ID Course_Name Teacher_ID Course_001 Biology Teacher_001 Course_002 Math Teacher_001 Course_003 English Teacher_003 Courses You can see that the foreign key in Courses matches a primary key in Teachers: Teacher_ID Teacher_Name Teacher_001 Carmen Teacher_002 Veronica Teacher_003 Jorge Teachers We can say that the Teacher_ID foreign key has helped to establish a relationship between the Courses and the Teachers tables. Types of Database Relationships Using foreign keys, or other candidate keys, you can implement three types of relationships between tables: One-to-one: This type of relationship allows only one record on each side of the relationship. The primary key relates to only one record – or none – in another table. For example, in a marriage, each spouse has only one other spouse. This kind of relationship can be implemented in a single table and therefore does not use a foreign key. One-to-many: A one-to-many relationship allows a single record in one table to be related to multiple records in another table. Consider a business with a database that has Customers and Orders tables. A single customer can purchase multiple orders, but a single order could not be linked to multiple customers. Therefore the Orders table would contain a foreign key that matched the primary key of the Customers table, while the Customers table would have no foreign key pointing to the Orders table. Many-to-many: This is a complex relationship in which many records in a table can link to many records in another table. For example, our business probably needs not only Customers and Orders tables, but likely also needs a Products table. Again, the relationship between the Customers and Orders table is one-to-many, but consider the relationship between the Orders and Products table. An order can contain multiple products, and a product could be linked to multiple orders: several customers might submit an order that contains some of the same products. This kind of relationship requires at minimum three tables. What are Database Relationships Important? Establishing consistent relationships between database tables helps ensure data integrity, contributing to database normalization. For example, what if we did not link any tables through a foreign key and instead just combined the data in the Courses and Teachers tables, like so: Teacher_ID Teacher_Name Course Teacher_001 Carmen Biology, Math Teacher_002 Veronica Math Teacher_003 Jorge English Teachers and Courses This design is inflexible and 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. Or perhaps we decided to simply add a second record for Carmen, in order to enforce 1NF: Teacher_ID Teacher_Name Course Teacher_001 Carmen Biology Teacher_001 Carmen Math Teacher_002 Veronica Math Teacher_003 Jorge English Teachers and Courses This is still a weak design, introducing unnecessary duplication and what is called data insertion anomalies, which just means that it could contribute to inconsistent data. For example, if a teacher has multiple records, what if some data needs to be edited, but the person performing the data editing does not realize that multiple records exist? The table would then contain different data for the same individual, without any clear way to identify it or avoid it. Breaking this table into two tables, Teachers and Courses (as visualized above), creates the proper relationship between the data and therefore helps ensure data consistency and accuracy. Continue Reading Understanding the Types of Database Relationships How to Maintain Relationships in Microsoft Access 2013 How to Establish a One-To-Many Relationship Between Database Tables An Intro to Databases That's Suitable for the Brand New Beginner Ditch Your Spreadsheet for a Database to Access the Power of your Data Transitive Dependencies Can Creep Into Your Data and Muck with Its Accuracy Protect Your Data by Normalizing It to at Least 2NF The Role of Referential Integrity in Your Database How Do you Put a Database in Second Normal Form (2NF)? How to Create Database Relationships in Access What Is a Database Foreign Key? How BCNF Is Used in Relational Database Theory Here's How to Create Foreign Keys in Microsoft SQL Server How to Choose a Primary Key for Your Database The Basics of Normalizing a Database Are You Making These Common Mistakes With Your Database Design?