COMMON DATA MODELING PROBLEMS - A Developer's Guide to Data Modeling for SQL Server - page 185

model a relationship whereby tickets are placed into work queues. Each
queue can have multiple tickets, and each ticket can be in multiple queues;
so far, our many-to-many is looking OK. What you have to ask yourself in
this situation is, “Are there any other attributes we should have in the join
table to complete the picture of what we are modeling?” In this example,
there indeed are other helpful attributes that we could add. For example,
we might add an InsertedTime column to the TicketQueue table to track
when the ticket was placed on the queue.
There are perfectly valid uses for many-to-many relationships, but if
you look closely at your model, you will find that there are other attributes
that you can add to the join tables. Don't get us wrong; we are not saying
that many-to-many relationships are bad, but they do add the extra over-
head of having to bring a third table into a relationship. We are big fans of
reducing the number of tables to increase performance. Properly evalu-
ating your many-to-many relationships is a big step toward a clean, well-
performing database model.
Summary
In this short chapter, we've covered some of the common problems that
you may run into when building your data model. By making yourself
aware of these issues you should be able to plan accordingly and avoid
these mistakes. Once a data model has been completed—physically imple-
mented—and you start using the database in a production environment,
making changes is often difficult at best. We always want to build good,
sound models, but sometimes we fall into these simple traps. Hopefully,
you now have the tools you need to build models without building in these
little headaches.