The principal division of a database is into tables. Every table in a relational database is organized into rows, where each row represents a single record. The rows are organized into columns. All the rows in a table have the same column structure. For example, a Customer table might have columns for the customer ID (the unique ID of the customer placing the order), the name of the customer, the email address of the customer, and so forth.

It is common to make an analogy between tables and classes and between rows and objects. The Customer table, for example, tells you a great number of things about a customer, just as a Customer class does. Each row in the Customer table describes a particular customer, much as an instance of the Customer would do.

This analogy is compelling, but limited. Tables, like classes, typically describe one logical entity and all of what you know about that entity. The mapping isn't reflexive, though—classes can contain properties that actually map across several different tables. This is similar to the notion of relationships between tables to ensure that consistent data in a database is not reflected back as relationships between classes.

One of the challenges facing an object-oriented programmer is overcoming these and other design differences between the object model on the one hand and the database model on the other. Typically, the interface between the backend relational database and the objects in the application is managed by creating a database interface layer of objects that negotiate between the creation of objects and the storage of information in the database tables, all created by hand. The past few years, however, have seen a number of products known as object-relational mappers, which can scan the structure of a database and, with a little help from the developer, produce a set of classes which represent the entities and relationships and make that transition between your code and your database that much easier. Language Integrated Query (LINQ), covered in Chapter 10, Presenting LINQ, actually includes two object-relational mappers:

This distinction is not a hard and fast rule by any means, but it does make the general distinction between the two that Microsoft is using.

Several other mappers are also available, including:

To understand the issues in table design, consider a database for recording orders (such as the AdventureWorksLT database used in this book). You need to know who placed each order, and it would be useful to know the email address, phone number, and other identifying information about each person as well.

You can imagine a form in which you display details about a given order, and in that detail page you offer the email address and phone number of the customer so the salesperson working on the order can contact that customer in case of a delay or stock issue.

You could store the identifying information with each order in an Orders table (named SalesOrderHeader in the database, for reasons we'll mention in a minute), but that would be inefficient. If John Doe placed 50 orders, you'd rather not repeat John Doe's email address and phone number in 50 records. It's also a data maintenance nightmare. If John Doe changes his email address and phone number, you'd have to make the change in 50 places.

Instead, the customer details are kept in a second table, called Customer, in which each row represents a single customer. In the Customer table, there will be a column for the CustomerID. Each customer will have a unique ID, and that field will be marked as the primary key for the Customer table. A primary key is the column or combination of columns that uniquely identifies a record in a given table.

The Orders table will use the PersonID column as a foreign key. A foreign key is a column (or combination of columns) that is a primary (or otherwise unique) key from a different table. The Orders table uses the CustomerID, which is the primary key in Customer, to identify which person placed the order. If you need to determine the email address for that person, you can use the CustomerID to look up the Customer record in the Customer table and that will give you all the detailed information about that person.

By "factoring out" the details of the person's address into a Customer table, you reduce the redundant information in each Order record. This process of taking out redundant information from your tables is called normalization.

The same process explains why an order is actually stored in two tables—SalesOrderDetail and SalesOrderHeader—rather than one. An order can be split into individual requests for products and so the SalesOrderHeader table stores the details of the order as a whole—when it is due, when it was shipped, how much it cost in total, and so on—whereas rows in the SalesOrderDetail table contain details for each separate product in the order.

The language of choice for querying and manipulating databases is Structured Query Language, often referred to as SQL. SQL is often pronounced "sequel." SQL is a declarative language, as opposed to a procedural language, and it can take awhile to get used to working with a declarative language if you are used to languages such as Visual Basic and C#.

Most programmers tend to think in terms of a sequence of steps: "Find me all the orders, then get the customer's ID, then use that ID to look up that customer's records in Customer, then get me the email address." In a declarative language, such as SQL, you declare the entire query and the query engine returns a set of results. You are not thinking about a set of steps; rather, you are thinking about designing and "shaping" a set of data. Your goal is to make a single declaration that will return the right records. You do that by creating temporary "wide" tables that include all the fields you need and then filtering for only those records you want: "Widen the SalesOrderHeader table with the Customer table, joining the two on the CustomerID, then filter for only those that meet my criteria."

The heart of SQL is the query. A query is a statement that returns a set of records from the database. Typically, queries are in this form:

Select column,column,column from table where column = value

For example, you might like to see information about the customers served by Janet. To do so you would write:

Select FirstName, LastName, CompanyName from Customer where SalesPerson = 'Janet'

Select c.FirstName, c.LastName, c.CompanyName, o.OrderDate, o.TotalDue 
   from Customers c
inner join SalesOrderHeader o on o.customerID = c.customerID
where c.SalesPerson = 'Janet' and o.OrderDate > '1/1/97'

inner join SalesOrderHeader o on o.customerID = c.customerID

SELECT statement ::=
    < query_expression >
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
        [ ,...n]    ]
    [ COMPUTE
        { { AVG | COUNT | MAX | MIN | SUM } (expression)} [ ,...n]
        [ BY expression[ ,...n]]
    ]
    [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }
            [ , XMLDATA ]
            [ , ELEMENTS ]
            [ , BINARY base64 ]
        }
]
    [ OPTION ( < query_hint > [ ,...n ]) ]

< query expression > ::=
    { < query specification > | ( < query expression > ) }
    [ UNION [ ALL ] < query specification | ( < query expression > ) [...n] ]

< query specification > ::=
    SELECT [ ALL | DISTINCT ]
        [ { TOP integer| TOP integer PERCENT } [ WITH TIES ] ]
        < select_list >
    [ INTO new_table ]
    [ FROM { < table_source > } [ ,...n ] ]
    [ WHERE < search_condition > ]
    [ GROUP BY [ ALL ] group_by_expression[ ,...n ]
        [ WITH { CUBE | ROLLUP } ]
    ]
    [ HAVING < search_condition > ]

where c.SalesPerson = 'Janet' and o.orderDate > '1/1/97'

where c.SalesPerson = 'Janet' and orderDate > '1/1/97'

Update Customer set FirstName = 'Jesse' where CustomerId = '124'

INSERT INTO [Customer]
 ([FirstName], [LastName], [SalesPerson])
VALUES
 ('Dan', 'Maharry', 'Janet')

DELETE from Customer where FirstName='Dan' and LastName='Maharry'

In this book, the database server of choice is Microsoft's SQL Server 2005, but as we mentioned earlier, it is not the only kid on the block and there are a number of different free versions of the major databases to tide you over while you learn. They all have a lot of good documentation installed with them as well, making them well worth a read.

Likewise, there are many good books and websites to teach you a lot more about database design and SQL queries than you'll ever need (probably). For example: