Many-To-Many Relationships Examples

Can somebody give a practical example of a many to many relationship?

One-to-many & many-to-many relationships are not the property of the data rather the relationship itself. And yes you do need bridging/third table for many-to-many relationship in perfect normalized RDBMS world. Lets see each of it with real life example:

One-to-many relationship: When one row of table A can be linked to one or more rows of table B.


Real Life Example: A car manufacturer makes multiple models of the cars, but a same car model can't be manufactured by two manufacturers.

Data Model:

Many-to-many relationship: When one row of table A can be linked to one or more rows of table B, and vice-versa.


Real Life Example: A user can belong to multiple community, and a community can have multiple users.

Data Model:

Many-to-many relationships examples

Example scenario: students and courses at a university. A given student might be on several courses, and naturally a course will usually have many students.

Example tables, simple design:

CREATE TABLE `Student` (
    `StudentID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `FirstName` VARCHAR(25),
    `LastName` VARCHAR(25) NOT NULL,
    PRIMARY KEY (`StudentID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `Course` (
    `CourseID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Code` VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
    `Name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`CourseID`)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `CourseMembership` (
    `Student` INT UNSIGNED NOT NULL,
    `Course` SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (`Student`, `Course`),
    CONSTRAINT `Constr_CourseMembership_Student_fk`
        FOREIGN KEY `Student_fk` (`Student`) REFERENCES `Student` (`StudentID`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `Constr_CourseMembership_Course_fk`
        FOREIGN KEY `Course_fk` (`Course`) REFERENCES `Course` (`CourseID`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci

Find all students registered for a course:

SELECT
    `Student`.*
FROM
    `Student`
    JOIN `CourseMembership` ON `Student`.`StudentID` = `CourseMembership`.`Student`
WHERE
    `CourseMembership`.`Course` = 1234

Find all courses taken by a given student:

SELECT
    `Course`.*
FROM
    `Course`
    JOIN `CourseMembership` ON `Course`.`CourseID` = `CourseMembership`.`Course`
WHERE
    `CourseMembership`.`Student` = 5678

Many to two' relationship

This question shows that you don't fully understand entity relationships (no rudeness intended). Of which there are four (technically only 3) types below:

One to One
One to Many
Many to One
Many to Many

One to One (1:1):
In this case a table has been broken up into two parts for purposes of complying with normalisation, or more usually the open closed principle.

Normalisation compliance: You might have a business rule that each customer has only one account. Technically, you could in this case say customer and account could all be in the same table, but this breaks the rules of normalisation, so you split them and make a 1:1.

Open-Close principle compliance: A customer table, might have id, first & last names, and address. Later someone decides to add a date of birth and with it the ability to calculate age along with a bunch of other much needed fields. This is an over simplified example of one to one, but you get the main use for it is to extend your database without breaking existing code. Much code written (sadly) is tightly coupled to the database so changes in the structure of a table will break the code. Adding a 1:1 like this will extend the table to meet new requirements without modifying the origional, thereby allowing old code to continue functioning normally and new code to make use of the new db features.

The downside of normalisation and extending tables using 1:1 relationships in this way is performance. Often times on heavly used systems, the first target to increase database performance is de-normalising and combining such tables into a single table, and optimising the indexes thus removing the need to use joins and read from multiple tables. Normalisation / De-Normalisation is neither a good or bad thing, as it depends on the needs of the system. Most systems usually start off normalised changing back when needed, but this change needs to be done very carefully as mentioned, if code is tightly coupled to the DB structure, it will almost definitely cause the system to fail. i.e. When you combine 2 tables, one ceases to exist, all the code that includes that now nonexistant table fails until it is modified (in db terms, imagine connecting relationships to any of the tables in the 1:1, when you remove those tables, this breaks the relationships, and so the structure has to be greatly modified to compensate. Unfortunately, such bad designs are much easier to spot in the DB world than in the software world in most cases and you don't usually notice something went wrong in code until it all falls apart) unless the system is properly designed with separation of concerns in mind.

It the closest thing you can get to inheritance in object oriented programming. But its not quite the same.

One to Many (1:M) / Many to One (M:1):
These two relationships (hense why 4 become 3), are the most popular relationship types. They are both the same type of relationship, the only thing that changes is your point of view. An example A customer has many phone numbers, or alternately, many phone numbers can belong to a customer.

In object oriented programming this would be considered composition. Its not inheritance, but you are saying one item is composed of many parts. This is usually represented with arrays / lists / collections etc. inside of classes as opposed to an inheritance structure.

Many to Many (M:M):
This type of relationship with current technology is impossible. For this reason we need to break it down into two one to many relationships with an "association" table joining them. The many side of the two one to many relationships is always on the association / link table.

For your example, the person who said you need a many to many is correct. Because a two to many is effectively a many (meaning more than one) to many relationship. This is the only way you would get your system to work. Unless you are intending to research the field of relational calculus to find some new type of relationship that would allow this.

Also for such relationships (m2m) you have two choices, either create a compound key in the linker table so the combination of fields become a unique entry (if you are interested in db optimisation this is the slower choice, but takes less space). Alternately, you create a third field with an auto generated id column and make that the primary key (for db optimisation, this is the faster choice, but takes more space).

In your example specifically above...

A real world example would be phone numbers, users and companies. A company can have many phone numbers, a user can have many phone numbers, but ideally the user shouldn't provide the same phone number as the company as there would be duplicate content in the DB.

This would be a many to many relationship with the phone number table as the linker table between companies and users. As explained, to ensure no phone number is repeated, you simply set it as the primary key or use another primary key and set the phone number field to unique.

For those kind of questions, it is really down to how you phrase them. What is causing you to get confused about this, and how you overcome this confusion to see the solution is simple. Rephrase the problem as follows. Start by asking is it a one to one, if the answer is no, move on. Next ask is it a one to many, if the answer is no move on. The only other option remaining is many to many. Be careful though, ensure you have considered the first 2 questions carefully before moving on. Many inexperienced database people often over complicate issues by defining one to many as many to many. Once again, the most popular type of relationship by far is one to many (I would say 90%) with the many to many and one to one spliting the remaining 10% 7/3 respectevely. But those figures are just my personal perspective, so dont go quoting them as industry standard statistics. My point is to make extra extra sure it is definitely not a one to many before choosing many to many. It is worth the extra effort.

So now to find the linker table between the two, decide which two are your main tables, and what fields need to be shared between them. In this case, company and user tables both need to share the phone. Hense you need to make a new phone table as the linker.

The warning alarm of misunderstanding should show as soon as you decide none of the 3 are working for you. This should be enough to tell you that you simply are not phrasing the relationship question correctly. You will get better at it as time passes, but it is an essential skill and really should be mastered as soon as possible for your own sanaty.

Of course you could also go to an object oriented database which will allow a range of other relationships called "Hierarchacal" relationships. Thats great if you are thinking of becomming a programmer too. But I wouldnt recommend this as it going to make your head hurt when you start finding ways to combine the various types of relationships. Especially given there is not much need since nearly all databases in the world consist of just those 3 types of relationships unless they are something super duper special.

Hope this was a reasonable answer. Thanks for taking the time to read it.

SQL - How to handle multiple many-to-many relationships

I would go with option 1 and do join queries.
For the example query:
from a person, select precautions tied to them

you should get the person's activities from join table Person and Activities on person id and then join that with risks on activities and finally join that with precautions on risks. In the end, you will get the precautions associated with a person.

Why no many-to-many relationships?

Think about a simple relationship like the one between Authors and Books. An author can write many books. A book could have many authors. Now, without a bridge table to resolve the many-to-many relationship, what would the alternative be? You'd have to add multiple Author_ID columns to the Books table, one for each author. But how many do you add? 2? 3? 10? However many you choose, you'll probably end up with a lot of sparse rows where many of the Author_ID values are NULL and there's a good chance that you'll run across a case where you need "just one more." So then you're either constantly modifying the schema to try to accommodate or you're imposing some artificial restriction ("no book can have more than 3 authors") to force things to fit.

Many to many relationship?

Standard practice would be this, as an example...

You have a "tblEngineer" table...

 tblEngineer
 -----------
 (PK) EngineerId
 EngineerName

And a "tblTicket" table...

 tblTicket
 ---------
 (PK) TicketId
 TicketDetails

You now add a link table called "tblEngineerTickets" (or similar) which references the Ids of both the Engineer and their tickets...

 tblEngineerTickets
 ------------------
 (PK) EngineerTicketId
 (FK) EngineerId
 (FK) TicketId

So that way, you keep all the Ticket Details and the Engineer details separately, and link them using ONLY the Ids... the link table would look something like this...

  EngineerId | TicketId
 ------------+----------
      1      |    1
      1      |    2
      1      |    3
      2      |    1
      2      |    2
      2      |    3

This way, you can have multiple engineers assigned to one ticket, and/or multiple tickets assigned to an engineer.

This is best practice and it gives you the most opportunity for expansion. If you were to just add fields to your existing Engineer tables saying "Ticket1", "Ticket2", "Ticket3" etc... you would be effectively be placing a limit on the code, and potentially you'd have to keep going in to the code to add columns.

Query examples in a many-to-many relationship

The first thing I would do is recommend using an ORM like Linq-To-Sql or NHibernate which will give you object representations of your data-model which make it much simpler to handle complex things like many-to-many CRUD operations.

If an ORM isn't part of your tool set then here is how this would look in SOL.

Users       UserAddresses     Addresses
=======     =============     =========
Id          Id                Id
FirstName   UserId            City
LastName    AddressId         State
                              Zip

Our tables are joined like this:

   Users.Id -> UserAddresses.UserId
   Addresses.Id -> UserAddresses.AddressId

• All records in Users based on Addresses.Id

SELECT        Users.*
FROM            Addresses INNER JOIN
                         UserAddresses ON Addresses.Id = UserAddresses.AddressId INNER JOIN
                         Users ON UserAddresses.UserId = Users.Id
WHERE        (Addresses.Id = @AddressId)

• All records in Addresses based on Users.Id

SELECT        Addresses.*
FROM            Addresses INNER JOIN
                         UserAddresses ON Addresses.Id = UserAddresses.AddressId INNER JOIN
                         Users ON UserAddresses.UserId = Users.Id
WHERE        (Users.Id = @UserId)

one to many vs many to many relationship

The food sample does work well for a one-to-many relationship: One customer can order many dishes.

The many-to-many relationship is better described by a book sample:

An author can write many books (that would be a one-to-many relationship). But he can have co-authors also involved - one book can have many authors.

---

Related Topics