1:M (One to Many)Relationship

Should be the norm in any relational database design. A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.For example, the Painters and Painting tables have a one-to-many relationship: each painter produces many paintings, but each paintings comes from only one painter.

1:1 (One to One)Relationship

One entity can be related to only one other entity, and vice versa. Sometimes means that entity components were not defined properly, Could indicate that two entities actually belong in the same table. In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:

  • Divide a table with many columns.
  • Isolate part of a table for security reasons.
  • Store data that is short-lived and could be easily deleted by simply deleting the table.
  • Store information that applies only to a subset of the main table.

M:N (Many to Many)Relationship

Can’t be accurately implemented in relational database
Result every row in table 1 links to every row in table 2 – typically an absurdity

Solution
Add a transaction (activity) table
Produce multiple 1:M relationships

For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table’s primary key) and the title_id column (the Titles table’s primary key).