How many relationship in database

These foreign key fields are populated with data as records in the join table are created from either table it joins. A typical example of a many-to many relationship is one between students and classes. A student can register for many classes, and a class can include many students. The following example includes a Students table, which contains a record for each student, and a Classes table, which contains a record for each class. A join table, Enrollments, creates two one-to-many relationships—one between each of the two tables.

The primary key Student ID uniquely identifies each student in the Students table. The primary key Class ID uniquely identifies each class in the Classes table. Using the example above, create a table named Enrollments.

This will be the join table. Join tables typically hold fields that might not make sense to have in any other table. You can add fields to the Enrollments table, such as a Date field to keep track of when someone started a class, and a Cost field to track how much a student paid to take a class. This stores separate records for each combination of student and class. Our student and class tables remain the same:. 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.

Thanks for visiting DZone today,. Edit Profile. Sign Out View Profile. Over 2 million developers have joined DZone. Handling a one-to-one relationship or one-or-many relationship can be done pretty simply.

But many-to-many relationships are a bit different. Like Join the DZone community and get the full member experience. For instance, order information is useless unless you know which customer placed a particular order. By now, you probably realize that you don't store customer and order information in the same table.

Instead, you store order and customer data in two related tables and then use a relationship between the two tables to view each order and its corresponding customer information at the same time. If normalized tables are a relational database's foundation, then relationships are the cornerstone.

Relationship types You share many relationships with members of your family. For instance, you and your mother are related. You have only one mother, but she may have several children. You and your siblings are related—you may have many brothers and sisters and, of course, they'll have many brothers and sisters as well. If you're married, both you and your spouse have a spouse—each other—but only one at a time. Database relationships are very similar in that they're associations between tables.

There are three types of relationships: One-to-one: Both tables can have only one record on either side of the relationship. Each primary key value relates to only one or no record in the related table. They're like spouses—you may or may not be married, but if you are, both you and your spouse have only one spouse.

Most one-to-one relationships are forced by business rules and don't flow naturally from the data. In the absence of such a rule, you can usually combine both tables into one table without breaking any normalization rules.

One-to-many: The primary key table contains only one record that relates to none, one, or many records in the related table. This relationship is similar to the one between you and a parent. You have only one mother, but your mother may have several children. Many-to-many: Each record in both tables can relate to any number of records or no records in the other table.

For instance, if you have several siblings, so do your siblings have many siblings. Many-to-many relationships require a third table, known as an associate or linking table, because relational systems can't directly accommodate the relationship. Establishing relationships By the time you get around to establishing relationships between the related tables, you'll probably be very familiar with the data.

Consequently, associations are more obvious at this point than when you started. Your database system relies on matching values found in both tables to form relationships. When a match is found, the system pulls the data from both tables to create a virtual record. For instance, you might want to see all the books written by a particular author. In this case, the system would match values between the Books and the Authors tables. It's important to remember that most of the time, the resulting record is dynamic, which means any change made to the virtual record will usually work its way back to the underlying table.

Those matching values are the primary and foreign key values. The relational model doesn't require that a relationship be based on a primary key. You can use any candidate key in the table, but using the primary key is the accepted standard.