Relationships in MySQL


Share

MySQL is called a relational database management system because its tables store not only data, but the relationships among the data. There are three categories of these relationships.

When I speak of database relationships, I specifically mean how the data in one table relates to the data in another. A relationship between two tables can be one-to-one, one-to-many, or many-to-many.

One-to-One Relationship


A one-to-one relationship between two types of data is like a (traditional) marriage: each item has a relationship to only one item of the other type. This is surprisingly rare. For instance, an author can write multiple books, a book can have multiple authors, and even an address can be associated with multiple customers. Perhaps the best example in this chapter so far of a one-to-one relationship is the relationship between the name of a state and its two -character abbreviation.

The relationship is one-to-one if one and only one item in Table A applies to one and only one item in Table B (e.g., each U.S. citizen has only one Social Security number, and each Social Security number applies to only one U.S. citizen; no citizen can have two Social Security numbers, and no Social Security number can refer to two citizens).


1. You want to be prepared in case the relationship changes later.
2. The table has a lot of columns and you think that performance or maintenance would be improved by splitting it.



One-to-Many Relationship


One-to-many (or many-to-one) relationships occur when one row in one table is linked to many rows in another table.
one-to-many relationship if multiple customers were allowed at the same address which is why it would have to be split up if that were the case. This one-to-many relationship is also the preferred scheme to use when describing a many-to-one relationship, in which case you would swap the left and right tables to view them as a one-to-many relationship


A relationship is one-to-many if one item in Table A can apply to multiple items in Table B. The terms female and male will apply to many people, but each person can be only one or the other. A one-to-many relationship is the most common one between tables in databases.



Many-to-Many Relationship


In a many-to-many relationship, many rows in one table are linked to many rows in another table. To create this relationship, add a third table containing a column from each of the other tables with which they can be connected. This third table contains nothing else, as its sole purpose is to link up the other tables.


A relationship is many-to-many if multiple items in Table A can apply to multiple items in Table B. For example, a record album can contain songs by multiple artists and artists can make multiple albums. You should try to avoid many-to-many relationships in your design because they lead to data redundancy and integrity problems.

Example:-

  Customer ISBN 
1 0596101015
2 0596527403
2 0596101015
3 0596005436
4 0596006815


With this intermediary table in place, you can traverse all the information in the database through a series of relations. You can take an address as a starting point and find out the authors of any books purchased by the customer living at that address.