A many-to-many relationship is also best shown by example.

One author can write many books – this is a one-to-many relationship. However, one book can also have many authors. Putting these two facts together, we see a many-to-many relationship. Two “one-to-many” relationships in opposite directions. Since this is not always easy to understand, let’s look at a few examples.

  • A car can have many different drivers. A driver can drive many different cars.
  • A shopping cart can have many product lines in it. The same product line can be in many different shopping carts.
  • A traveller can take many flights. Each flight can carry many travellers.

A many-to-many relationship exists when you can have more than one connection in each direction. Using the first example above, if a car needs to “point to” many drivers, we can store a foreign key in the driver rows as a many-to-one relationship. But if each of the drivers also needs to “point to” many cars, the situation becomes confusing. The cars need to have a many-to-one relationship with the drivers – each car needs to store the foreign key of a driver. If both of these relationships is mandatory (each car must have at least one driver and each driver must have at least one car) then it can be difficult to create new table entries unless these constraints can be deferred. We will talk more about constraints later.

Overall, it is much easier to use an intermediate or connecting table which stores two foreign keys (one for a car and one for a driver). This is a connection “object” which can be created after the car and driver rows. Constraints can still make this difficult to manage if the connections each way are mandatory. Deferring the constraints is normally necessary for success.

Constraints

Constraints are simply limitations on something. In a database, many different constraints can be set up. For example. if you store contact information about your friends, they must have a name, but an email address or Facebook account may be optional (OK, for many people it isn’t optional!). To store details of your friends in a database table, you could add a constraint: “name” is a mandatory column. Trying to create a new friend in your table would fail unless you provide a name. However, creating a friend with no email address or Facebook information would not cause any problems. The same concept is used for connection constraints. A foreign key may be mandatory or optional. If a table has a mandatory foreign key column and you try to create a row with no connection to another object, the database will not let you create the row.

Not all data constraints can be implemented through mandatory columns or database foreign key constraints. At times, it will be better for checks to be made by a supervising application which knows the true data requirements, particularly with complex connections. In such a situation, the software might need to insert or edit rows and there could be times during this process where the data is not valid, connections are missing or the connected objects are missing. In theory, as long as the data is valid once the process is complete, the intermediate situation is often not important. Be aware, however, that this can be a source of problems and invalid data, since the application logic will not be flawless. Bugs in the software can easily cause corrupt data.

Database constraints are well understood and easily tested. Application logic is less reliable, particularly when the application is still being extended or improved over time. Despite this, there are some situations where application logic is the best place for the application or checking of constraints.