Which relationship is where multiple records of a table are related to a single record of another table?
Join the DZone community and get the full member experience. Show
Join For Free A many-to-many relationship occurs when multiple records in a table are related to multiple records in another table. When normalizing a database or adding tables to an existing database, we need to be able to relate tables to each other. There are three ways in which a table can be related to another table:
Handling a one-to-one relationship or a one-or-many relationship can be done by adding the primary key of one table into the other table as a foreign key. However, for many-to-many relationships, it’s a bit different. Let’s have a look at an example. Many-to-Many Relationships: An ExampleLet’s say we are creating a database for a university (which is an example I’ve used often). We capture details about students who attend classes, among other things. The rules are:
This means a student has many classes, and a class has many students. We can’t add the primary key of one table into the other, or both, because this only stores a single relationship, and we need many. We couldn’t do this:
This would mean we have one column for storing multiple values, which is very hard for maintenance and querying. We also couldn’t have many columns for class ID values, as this would get messy and create a limit on the number of relationships.
Many-to-Many RelationshipsSo how do we capture this? We use a concept called a joining table or a bridging table. A joining table is a table that sits between the two other tables of a many-to-many relationship. Its purpose is to store a record for each of the combinations of these other two tables. It might seem like a bit of work to create, but it’s simple to do and provides a much better data structure. To create one for this example, we can create a new table called Now, the name of the table is important. It’s good to be descriptive with the table. I’ve seen joining tables named with the two names of the other two tables together (such as So, we have a new table called Our table would look like this:
This stores separate records for each combination of student and class. Our student and class tables remain the same: Student:
Class:
Having our data structure in this way makes it easier to add more relationships between tables and to update our students and classes without impacting the relationships between them. Database design Relational database Opinions expressed by DZone contributors are their own. In which relationship one record in a table is associated with?In a one-to-one relationship, one record in a table is associated with one and only one record in another table. For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person.
Which relationship means one record in a table has exactly one matching record in the related table?In a one-to-one relationship, each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table.
What is the name of the relationship where one record in either table can have many associated records in the other table?A One-to-Many relationship is a relationship between two tables where a record in one table can be used repeatedly as a value in another table.
When a record from one table is related to several records in another table the tables have a oneWhen a record from one table is related to several records in another table, the tables have a one-to-many relationship. In Table Datasheet view, you must use a property sheet to increase the decimal places in a Number field.
|