Relationship Modeling

In this book, we're not dealing on the entire application development process but concentrating on how to create a database design that models business needs.

As you've just seen, the business objects in particular dictate the database's tables. But the database also is influenced heavily by business rules because they govern things such as data types, allowable values, and relationships between tables.

Relationships are probably the single greatest challenge for the system architect: not only are relationships the most complex things to design, but if you define them wrongly, it can be even more difficult to sort out things later.

Apart from making sure that the business need is suitably represented, the designer needs to think about things such as normalization of data. This can mean tables looking a little different in their final form than in the initial pencil sketch. We'll look at how to implement normalization later today.

Because much of this book is about how to design tables bearing in mind technical and performance limitations, we won't go into these considerations here. Today, let's concentrate on the types of relationships you'll encounter, and how to represent them in your database design.

One-to-One Relationships

One-to-one relationships are generally the easiest to model. In the store example, when a customer buys something, it creates one purchase transaction and one invoice.

This would typically mean a one-to-one relationship between a purchases table and an invoices table.

In some cases, you might combine items in a one-to-one relationship into a single table—after all, why have two tables?

You may prefer having two tables so that you can record unsuccessful purchases (perhaps the customer's credit card payment failed) without having an empty invoice part on that record. Or you may want to attach different security levels to the two tables (which personnel can access the tables, and what they can do).

There may also be technical reasons for breaking things into two or more tables, where they logically belong in the same table. For example, if you're trying to develop an electronic library, to model books in a real library, it might seem logical that one of your business objects is a book. Therefore you would expect to have a table of books, perhaps with the one single table containing the title, author, and complete contents of each book. However, storage and performance limitations are likely to mean that this is a bad idea. You'll learn more about how data is stored in Day 5, “MySQL Data Types.”

If you do have two tables in a one-to-one relationship, you will need an index, or a key field, to relate them together. Often you'll want a key to be a primary key—this means that it is unique and must always contain some data.

Figure 3.2 illustrates how tables of purchases and invoices might be represented, being related by purchase ID.

One-to-Many Relationships

One-to-many relationships are perhaps the most common kind of relationships that a database designer has to deal with.

In our store example, you would find that many relationships are one-to-many, such as many purchases to one customer, or many products to one product category, as shown in Figure 3.3.

As you will see from Figure 3.3, keys are used again. On the product categories object (which will become a table called, say, categories) there is a primary key for the product category ID. No two categories can have the same ID, and every category must have an ID.

On the products object (which in the database will be a table called products), each item has a category ID to indicate which category it belongs to. Because the relationship is one-to-many, several products will have the same category ID. Therefore the category ID is a multiple key in this table, and because it really belongs to a different table, it's often referred to as a foreign key.

The products table's primary key is the product ID, to uniquely identify each product. This is essential when you're creating further relationships between products and other objects.

Many-to-Many Relationships

The relationships you've seen so far have been fairly easy to forge, just by having a key in one table whose values correspond with key values in another table. But with a many-to-many relationship—several instances of one object are related to several instances of another object—this structure is inadequate.

A many-to-many relationship might occur in our store if we relate products with purchases. For example, several different products usually exist in a single purchase, and any one product can be bought in several purchases (that is, by many customers).

This can not be represented by a one-to-many relationship because it would be too restrictive, and the two-table approach just doesn't let us model the true picture. So a third table is required—a linking table—to represent the many-to-many relationship (see Figure 3.4).

Figure 3.4 shows how a many-to-many relationship might be represented between products and purchases. The middle table, the product-purchase link table, has one entry (one row) for each incidence of a product being purchased.

Each linking table row can be simple: it necessarily has to store the primary key from each of the tables it links. In this example, it means the product ID (the primary key of the products table) and the purchase ID (the primary key of the purchases table). In Figure 3.4, this table is also used to store additional information, the quantity of items in the order.

Figure 3.4 shows purchase 1 being for a blue sweater, purchase 2 being for a green sweater, and purchase 3 being for a green sweater and a white sweater.