Before discussing how to create data relations inside a DataSet , it makes sense to explain how relationships function inside a relational database system such as Microsoft SQL Server. If you are already familiar with relational databases, you might want to skip ahead to the section entitled "The DataRelation Object."
Suppose you have to store information about customer orders inside a database. You might choose to use a database schema like the one shown in the database diagram in Figure 4.1. There are five tables in this diagram, and they are all related to the main Orders table. Notice the lines connecting the tables together. Each of these lines represents a relationship in the database.
Each of the relationships in Figure 4.1 are "one to many." Notice how each relationship has an infinity symbol on one terminating end and a key on the other. The table connected via the infinity symbol may contain multiple entries from a column in the other table. For instance, the CustomerID field of the Orders table may only contain entries from the CustomerID field of the Customers table. It can contain the same CustomerID as many times as required (one per order per customer, in fact). If you try to enter a value in the CustomerID field of the Orders table that is not also present in the Customers table, you will receive an error.
This is done mainly to avoid duplicating data. Rather than storing redundant customer data in the Orders table, you only need the CustomerID, which you can then use to retrieve that customer's information later, when you need it. (For information on retrieving the customer information, see the section titled "The Join Operator.")
If you are using Microsoft SQL Server 2000, creating a relationship between two tables is easy:
Create a new database diagram in your database by right-clicking on Diagrams and selecting New Database Diagram as in Figure 4.2. You are presented with the Create Database Diagram Wizard. Click Next.
Add all the tables from the left to the tables in the new diagram, on the right side. Select Next and then Finish to complete the wizard. You will see a screen much like Figure 4.3.
Right-click anywhere on the white background of the diagram window and select New Table. Name the new table EmployeeAwards.
Create the new table according to the values entered in Figure 4.4. When you are done, right-click on the grey box to the left of the AwardID column. Select Set Primary Key. A primary key must be present in both tables participating in a relationship.
Next, click and drag the box to the left of the EmployeeID column of the Employees table and drag it on top of the EmployeeID column of the EmployeeAwards table. You are presented with a screen like the one in Figure 4.5.
Select OK and you are done. Notice that the graphical representation of the relationship is added to the diagram, as seen in Figure 4.6.
Now, if an employee wins an award, you can track it in the EmployeeAwards table.
The only problem with using this type of data model is that whenever you need to retrieve records with columns from both tables, you must perform a join operation. The join operation enables you to select columns from two or more tables linked together by a relationship like the one you've just created.
There are a number of different kinds of join operations you can perform. Listing 4.1 displays the syntax to perform an inner join. An inner join will return records from two tables with matching records determined by the columns chosen for the join. For other types of joins, please refer to your database documentation.
SELECT column_names FROM table_a INNER JOIN table_b on table_a.column = table_b.column WHERE search_conditions
The join operation essentially lets you add columns from another table to your current resultset based on the values in a common field. Syntactically, the query in Listing 4.2 and the query in Listing 4.3 are exactly the same.
For instance, suppose you want to retrieve all customer orders from the Orders table and also get their titles and phone numbers at the same time. The queries in Listings 4.2 and 4.3 will both retrieve the same data, one with a join, one without.
SELECT Orders.*, Customers.ContactTitle, Customers.Phone FROM Orders INNER JOIN Customers on Customers.CustomerID = Orders.CustomerID
SELECT Orders.*, Customers.ContactTitle, Customers.Phone FROM Orders, Customers WHERE Customers.CustomerID = Orders.CustomerID
Constraints are basically just rules about the contents of a database field. For instance, Figure 4.7 shows a constraint present in the Employees table of the Northwind database. This ensures that no one enters a future date in the birthdate field.