Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)04

Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)04
Nội dung trích xuất từ tài liệu:
Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)0418 CHAPTER 1 s INTRODUCTION TO DATABASE CONCEPTS The choice of primary key is largely a matter of convenience and what is easiest to use. We’ll discuss primary keys later in this chapter in the context of relationships. The important thing to remember is that when you have values that should exist only once in the database, you need to protect against duplicates. Choosing Keys While keys can consist of any number of columns, it is best to try to limit the number of columns in a key as much as possible. For example, you may have a Book table with the columns Publisher_Name, Publisher_City, ISBN_Number, Book_Name, and Edition. From these attributes, the following three keys might be defined: • Publisher_Name, Book_Name, Edition: A publisher will likely publish more than one book. Also, it is safe to assume that book names are not unique across all books. However, it is probably true that the same publisher will not publish two books with the same title and the same edition (at least, we assume that this is true!). • ISBN_Number: The ISBN number is the unique identification number assigned to a book when it is published. • Publisher_City, ISBN_Number: Because ISBN_Number is unique, it follows that Publisher_City and ISBN_Number combined is also unique. The choice of (Publisher_Name, Book_Name) as a composite candidate key seems valid, but the (Publisher_City, ISBN_Number) key requires more thought. The implication of this key is that in every city, ISBN_Number can be used again, a conclusion that is obviously not appropriate. This is a common problem with composite keys, which are often not thought out properly. In this case, you might choose ISBN_Number as the PK and (Publisher_Name, Book_Name) as the AK. s Note It is important to not confuse unique indexes with keys. There may be valid performance-based reasons to implement the Publisher_City, ISBN_Number index in your SQL Server database. However, this would not be identified as a key of a table. In Chapter 6, we’ll discuss implementing keys, and in Chapter 8, we’ll cover implementing indexes for data access enhancement. Having established what keys are, we’ll next discuss the two main types of keys: natural keys (including smart keys) and surrogate keys. Natural Keys Wikipedia (http://www.wikipedia.com) defines the term natural key as “a candidate key that has a logical relationship to the attributes within that row” (at least it did when this chapter was written). In other words, it is a “real” attribute of an entity that the user logically uses to uniquely identify each instance of an entity. From our previous examples, all of our candidate keys so far—employee number, Social Security number (SSN), ISBN, and the (Publisher_Name, Book_Name) composite key—have been examples of natural keys. Some common examples of good natural keys are as follows: • For people: Driver’s license numbers (including the state of issue), company identification number, or other assigned IDs (e.g., customer numbers or employee numbers). • For transactional documents (e.g., invoices, bills, and computer-generated notices): These usu- ally have some sort of number assigned when they are printed. • For products for sale: These could be product numbers (product names are likely not unique). CHAPTER 1 s INTRODUCTION TO DATABASE CONCEPTS 19 • For companies that clients deal with: These are commonly assigned a customer/client number for tracking. • For buildings: This is usually the complete address, including the postal code. • For mail: These could be the addressee’s name and address and the date the item was sent. Be careful when choosing a natural key. Ideally, you are looking for something that is stable,that you can control, and that is definitely going to allow you to uniquely identify every row in yourdatabase. One thing of interest here is that what might be considered a natural key in your database isoften not actually a natural key in the place where it is defined, for example, the driver’s licensenumber of a person. In the example database, this is a number that every person has (or may needbefore inclusion in our database, perhaps). However, the value of the driver’s license number is justa series of integers. This number did not actually occur in nature tattooed on the back of the per-son’s neck at birth. In the database where that number was created, it was actually more of asurrogate key (which we will define in a later section). Given that three-part names are common in the United States, it is usually relatively rare thatyou’ll have two people working in the same company or attending the same school who have thesame three names. (Of course, if you work in a company with 200,000 people, the odds will go upthat you will have duplicates.) If you include prefixes and suffixes, it is a bit less likely, but “rare” oreven “extremely rare” cannot be implemented in a manner that makes it a safe key. If you happen tohire two people called Sir Lester James Fredingston III, then the second of them probably isn’t goingto take kindly to being called Les for short just so your database system isn’t compromised. One notable profession where names must be unique is acting. No two actors who have theirunion cards can have the same name. Some change their names from Archibald Leach to some-thing more pleasant like Cary Grant, but in some cases the person wants to keep his or her name, soin the actors ...