PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 70

F IGURE 3.3

Many-to-many relationship between a Student and a Class table

not violate the primary key. This means that you can relate each student to
all the classes he attends, and you can relate all the students in a particular
class to that class. This gives you a many-to-many relationship.
It may sound complex, but once you create a many-to-many relation-
ship and add some data to the tables, it becomes pretty clear. The best way
to really understand it is to do it. When we build our physical model in
Chapter 9, we look more closely at many-to-many relationships, including
ways to make them most useful.

Implementing Advanced Cardinality
In Chapter 2, we talk about cardinality. Cardinality simply describes the
number of rows in a table that can relate to rows in another table.
Cardinality is often derived from your customer's business rules. As with
one-to-one relationships, SQL Server does not have a native method to
support advanced cardinality. Using primary and foreign keys, you can eas-
ily enforce one-or-more-to-many, zero-or-more-to-many, or one-to-one
cardinality as we have described previously.
What if you want to create a relationship whereby each parent can con-
tain only a limited number of child records? For example, using our em-
ployee and vehicle tables, you might want to limit your data so that each
employee can have no more than five cars assigned. Additionally, employ-
ees are not required to have a car at all. The cardinality of this relationship
is said to be zero-to-five-to-many. To enforce this requirement, you need
to be creative. In this scenario you could use a trigger that counts the num-
ber of cars assigned to an employee. If the additional car would put the
employee over five, the insert could be reversed or rolled back.
Each situation is unique. In some cases you might be able to use check
constraints or another combination of PKs, FKs, and constraints to imple-
ment your cardinality. You need to examine your requirements closely to
decide on the best approach.