Professional SQL Server™ 2005 Programming

7.2. Relationships

Well, I've always heard from women that men immediately leave the room if you even mention the word "relationship." With that in mind, I hope that I didn't just lose about half my readers.

I am, of course, kidding — but not by as much as you might think. Experts say the key to successful relationships is that you know the role of both parties and that everyone understands the boundaries and rules of the relationship that they are in. I can be talking about database relationships with that statement every bit as much as people relationships.

There are three different kinds of major relationships:

• One-to-one — This is exactly what it says it is. A one-to-one relationship is one where the fact that you have a record in one table means that you have exactly one matching record in another table.

• One-to-many — This is one form of your run-of-the-mill, average, everyday foreign key kind of relationship. Usually, this is found in some form of header/detail relationship, and generally implements some idea of a parent to child hierarchy. For example, for every one customer, you might have several orders.

• Many-to-many — In this type of relationship, both sides of the relationship may have several records that match. An example of this would be the relationship of products to orders — an order may contain several products, and, likewise, a product will appear on many orders. SQL Server has no way of physically establishing a direct many-to-many relationship, so you cheat ...