Re: Many to Many Relationships

Adam,

What you've done is split a single many to many relationship into three one to many relationships.

The resulting design is not normalized, if you consider the database to be a representation of the original
relationship between lawyers and cases. Depending on how you use the data, you might never run into
trouble with that design. Here's where your design can lead to problems:

Say you want to query the databases for all cases where lawyer Sam Jones (with ID 123456) is involved IN ANY OF THE THREE POSSIBLE ROLES. You can still do that, but it's a little awkward. You need the union of the cases where lawyer 123456 is the Lenders attorney, where lawyer 123456 is the Bank's attorney, and where lawyer 123456 is the buyer's attorney. This three way union captures all three roles, but it codes each one in separately and explicitly.

Now let's say, that some day, you add a fourth role that a lawyer can play in a given case, say AMICUS CURIAE, just as a wild example. Now you have to add a fourth column to your table. Not only that, but you have to go back and revise the query I just mentioned above so as to make the union of four different query results, instead of three. This may well never happen. But if it does, you will be better off with a more normalized original design.

Closely related to "why do I normalize" is "when do I normalize". My scenario above illustrates this. If you can safely rule out the possibility that a fourth role will ever be added, then this scenario becomes moot, and the unnormalized design "works" ok. So, in practice, it isn't always better to implement the most normalized possible database.

But in data modeling, it's generally desirable to understand what the most normalized model would be, and to document deviations from this logical model when designing the physical database.

BTW, if you do use a junction table, you will want to add the role the referenced lawyer is playing in the referenced case, as an attribute column in the junction table.

HTH.

--
Regards,
    David Cressey
    www.dcressey.com