This master plan for the logical entities (and their relationships) that make up the database should be as complete and thorough as possible. A good logical design has many facets and phases, including the analysis of relationships and the setting of constraints.
Entities and attributes
At a basic level of a data model are entities — objects that you are interested in as part of the data model you are creating. For example, if you were making an online
094932-4 ch07.F 5/29/02 3:39 PM Page 110
110
Part I ✦ Getting Started
store, you would want to know the customer’s name . Steve Suehring is an example of an entity. The specific name, Steve Suehring, is the entity. For a successful online store, you probably want more than one customer. Therefore you should abstract the entity into a general entity type. For this example, the entity type would be Customer.
Entities have identifying qualities — attributes. For example, the Customer entity of Steve Suehring has such attributes as height, weight, home address, and telephone number. To manage all the attributes of all the entities, you abstract the attributes into attribute types that become part of all the entity types.
At the initial meeting(s) for producing a data model, you define the entities — and subsequent entity types — that you are interested in. In addition, you will most likely start determining attributes and attribute types as well. For now, the entity types can be written down as a simple list. For example, suppose you know that both customer information and product information are to be kept in the database.
You could create a list somewhat like the one given here — which, though incomplete, works fine for a basic site:
✦ Customer
✦ Product
✦ Manufacturer
That gives you three entity types. At this point in the research process, I usually jump right into listing some attribute types for each entity type, like this:
✦ Name
✦ Address
✦ Supplemental Address
✦ City
✦ State
✦ Zip
✦ Telephone Number
✦ E-mail Address
✦ Credit Card Number
✦ Credit Card Expiration
✦ Name on Card
✦ Product Name
✦ Product Price
✦ Quantity on Hand
✦ Manufacturer
✦ Product Category
094932-4 ch07.F 5/29/02 3:39 PM Page 111
Chapter 7 ✦ Database Concepts and Design
111
Tip
You can add fun things like Ratings and Reviews for products later. (And if the attribute types in the list are starting to resemble column headings, that’s not accidental. More about that later.)
Upon further review, I believe Name should be broken into two separate attributes, First Name and Last Name. I also believe Telephone Number should be broken apart to account for area code and number. (I assume that the online store only ships within the United States, otherwise I would add Country Code as well.) It would also be useful to know the credit card type, even though this information can usually be gleaned from the digits of the card. Having the credit-card type allows some sanity checking (the use of a special program to ferret out and report system problems) later in the process. In addition, it would be useful to keep information about the manufacturers such as their address and contact information as well.
Here’s what the new attribute type list looks like:
✦ First Name
✦ Last Name
✦ Address
✦ Supplemental Address
✦ City
✦ State
✦ Zip
✦ Area Code
✦ Telephone Number
✦ E-mail Address
✦ Credit Card Type
✦ Credit Card Number
✦ Credit Card Expiration
✦ Name on Card
✦ Product Name
✦ Product Price
✦ Quantity On Hand
✦ Manufacturer Name
✦ Manufacturer Address
✦ Manufacturer City
✦ Manufacturer State
✦ Manufacturer Zip
094932-4 ch07.F 5/29/02 3:39 PM Page 112
112
Part I ✦ Getting Started
✦ Manufacturer Contact
✦ Manufacturer Area Code
✦ Manufacturer Telephone Number
✦ Product Category
Now that I have a list of entity types and attribute types for the data model, I can start to analyze how they relate.
Relationships
In my example, it’s obvious that some attribute types relate to each other. For example, each First and Last Name pair can have one or more Addresses, Supplemental Addresses, Cities, States, and Zips.
As should be evident, First Name/Last Name (which I will refer to as Customer Name for brevity’s sake), Address, Supplemental Address, City, State, Zip (which I will refer to simply as Address), and so on are all items relating to the Customer entity type. It quickly becomes clear that I could go down a slippery slope because one Customer Name could have more than one Address and one Address could have more than one Customer Name.
However, what may appear clear is not necessarily as clear as you may think. While it is true that a Customer Name could have more than one Address, a Customer Record is made up of one and only one Address. What appeared to be a problem in the design is actually a false relationship.
Tip
By adding other entity types, you can add support for more than one address to allow the customer to ship to different addresses.