Understanding the Role of Keys in Database Design

So far, this chapter has presented quite a few ERDs. Many of them depict relationships, but so far there's been no discussion of exactly how a relationship between two entities is created and maintained. The answer is simple: We create fields in each entity called keys, which allow instances of one entity to be associated with instances of another. You might relate orders to customers, for example, by using a customer's Social Security number as a key. Each order would then contain the Social Security number of the related customer as one of its attributes. The following sections explore the concept of keys in more detail.

Keys That Determine Uniqueness

One of the crucial tenets of relational database theory is that it has to be possible to identify any database row, anywhere, without ambiguity. Put differently, every row in every table should have a unique identifier. If I have a record in a table of orders, I want to be able to ask it "What customer do you tie to?" and get an unambiguous answer. I need a simple answer: "Customer 400." End of story. The number 400, as it appears in the customer table, is a unique identifier.

A piece of data that is capable of uniquely identifying a database row is known as a primary key. A primary key is an attribute the values of which are (and always will be) unique for every single row in the database. It's a unique identifier, like a Social Security number, an ISBN number for a book, or a library card catalog number.

We recommend that every database table you design have a primary key, without exception. Some database systems force you to create a primary key for each new table. FileMaker Pro doesn't, but we strongly recommend that you do so anyway. There's very little to lose and a great deal to gain by following this practice.

The discussions in this chapter assume that every table you design, without exception, has a primary key.

What Makes a Good Primary Key?

So far, we've mentioned that every database table should have a primary key, and that those keys have to be unique, to distinguish one row from another absolutely. There's one other important rule: Primary keys are best (in our opinion) if they're meaningless.

The important idea here is that data chosen to act as a primary key should be free of real-world meaning or significance. When data has meaning in the real world, such meaning is subject to change. In simple terms, data that is supposedly unique may turn out not to be.

Here's an example. You're designing a database that holds information about the different offices of a company. Offices are stored in their own table. You decide that, because there's no more than one office in a city, the City field in the Office table will make a great primary key. It's unique, after all, and every Office record has a City value.

Just to be sure, you check with someone highly placed in the firm, and they assure you that, no, the company will never need to open more than one office in any one city. So you go ahead and build a database structure around the assertion that the City field in the Office table is unique.

Seven months later the company announces plans to open its second office in New Delhi, and you're left to explain why an important part of the database structure needs to be rewritten.

Imagine instead that you'd decided that the database system itself should generate a primary key. Offices will be numbered sequentially starting from 1. The important thing about this data is that it has meaning only to the database system itself. No one else cares, or even knows, that the New Delhi office is office number 14. The number 14 has no business significance.

The critical difference here is that when you used the City field as a primary key, you were relying on the stability of an assertion about the real world (a place notoriously subject to change). By contrast, when you create your own key, you're working in an environment that no one but the database programmers care about, so you're at liberty to design uniqueness rules that won't be affected by decisions beyond your control.

 

Keys That Refer to Other Tables

Keys are essential to specifying relationships between tables. Going back to the example of customers and orders, the relationship between these entities is one-to-many: One customer may have many orders.

If you've followed the rule about always having a primary key, your Customer entity has a primary key, which you might call Customer ID. Now, each unique customer may have many related orders. To forge that relationship, each record in the Order table needs to store the Customer ID (the primary key) of the related customer. This value, when it's stored in the Order table, is known as a foreign key. The reason for the term is simple: The value in the Order table refers to a primary key value from a different ("foreign") table.

Figure 5.13 demonstrates how primary and foreign keys work together to create relationships between database tables. In a one-to-many relationship, the "many" side of the relationship always needs to contain a foreign key that points back to the "one" side. The child record thus "knows" who its parent is.

Figure 5.13. A one-to-many relationship between customers and orders, showing primary and foreign keys.

FileMaker Pro has several built-in capabilities that help you add strong key structures to your FileMaker databases. For some ideas on how best to define key fields in FileMaker Pro, see "Working with Keys and Match Fields," p. 162.

 

Many to Many Relationships Solving the Puzzle