Orders exercise - ERD help

Building the ERD

The steps for data structure analysis that we discussed in class can be summarized as follows:

1. Identify entities.
2. List attributes of entities.
3. Identify key fields.
4. Identify relationships.
5. Use linking tables to resolve many-to-many relationships.
6. Specify key field relationships for linking tables.
7. Look for repeating groups; add tables expressing appropriate 1:M relationship.
8. Repeat until ERD is stable (your judgment call).

Entitites, attributes, and key fields

At least two entities are relatively easy to identify: customers and products. Attributes related to customers, to begin with, are likely to resemble those used for the last assignment.

A preliminary list of product attributes can be Name and Price. You can probably imagine a number of others, but for the purposes of test data that is probably all you need for now.

The resulting entities and attributes might look like the following:

The attributes list two key fields, C# for Customer number and P# for Product number. These can be counter fields generated automatically by Access if you wish. Note that later in the exercise we will need to be sure that all the data types we are using match across tables (more on this later).

Relationships

To identify the relationship between Customers and Products, ask the pair of questions that attempt to identify one-to-many (1:M) relationships in each direction:

1. Can one customer order many products? (e.g., 1 customer : Many products)
2. Can one product be ordered by many customers? (1 product : Many customers).

If the answer to both of these questions is yes, then you have a many-to-many (M:M) relationship.

Graphically, this step looks like this:

Figure 2: Question 1 - Customers and Products

Figure 3: Question 2 - Products and Customers

Since the answer to both questions is yes, this suggests that a many-to-many (M:M) relationship exists between Customers and Products.

Figure 4: Result - M:M between Customers, Products

Linking Tables

Since we're dealing with a M:M relationship, this suggests establishing a linking table to resolve the M:M into two 1:Ms. What shall we call it?

From the structure of linking tables we know that this table will represent a combination of primary keys from both the Customer and the Product tables. In other words, it will represent the point in time when a specific customer comes together with a specific product. From this perspective, what the linking table is in fact describing is an order.

If Orders represent events were customers and products meet, then the structure of such a table is relatively clear. We know it will have a primary key (let's call it O# for Order Number). It will also show the primary keys of both Customer and Product as so-called foreigh keys (e.g. primary keys from other tables that appear in this table). Since orders represent a transaction one of whose important features is time, a logical field to include would be the date of the order (you've probably heard of time-stamping -- that's what this field is for). Since a customer might order more than just one of a product at any given time, another field might represent quantity ordered. The result suggests the following Order table:

Figure 5: An Order entity provides a linking table

Checking key fields in the Order table

As a check, trace the 1:M links using key fields. This means that for a second time you ask the questions that you used to identify the linking table, checking to see how the key fields would handle the 1:M relationships.

1. For each customer (e.g., each C#), the Order table can list many rows of orders (e.g., each with a C# in it).
2. For each product (each P#), the Order table can list many rows of orders (each with a P# in it).

In a quasi-mathematical shorthand, the answers to these questions would look like:

From the shorthand you can begin to get a feel for how relational algebra generates matches, or "joins" across tables.

You may have noticed by now that this table structure assumes that only one product will be ordered by any customer on any given day. This does not appear to be very realistic, but check the test data suggested for this exercise and you will see that no orders in fact do occur on the same day. We'll resolve this design problem in the next exercise. Unless, of course, you want to mess around with trying to resolve it now. If you do, please go ahead -- it'll shorten the time you spend on the assignment for next week. For now, I'll just say that one solution includes a second linking table between Orders and Products representing an entity that might be called Order Detail or Line Items. If you want an example, take a look at the sample Northwind database that comes with Access.

Identifying repeating groups

Once you have built the linking table it is time to examine the attributes of each of the entities that you have identified to look for repeating groups. We'll discuss some of the most obvious here.

For example, consider the Customers entity. In the database design described here, this entity identifies a table that we have (not surprisingly) named Customer. We listed a number of the attributes of a customer earlier. These included name and contact information such as addresses and telephone number(s). Presuming that each customer can be assumed to have one and only one address (a big assumption) and that we don't want to maintain a history of customer addresses (another big assumption), our Customer table should prove adequate for maintaining addresses.

As the assignment points out, it appears less realistic to assume a single telephone number. Each customer would have at least one business phone and one home phone, but may be associated with an indeterminate number of other telephone numbers (e.g., mobile, fax, car, vacation, best daytime number, best evening number, etc.).

This is a classic example of a repeating group, as suggested by the italics used to describe telephone numbers in Figure 1. Given this situation, the question becomes how to resolve the problem. Here is one solution.

1. To handle a repeating group, isolate the field that implies the group in its own table. In this case, we would introduce a new table named Telephone.
   
   Figure 6: A Telephone table isolates the repeating group
   
   
2. Include the primary key from the table from which you isolated the field as a foreign key in the new table. In this case, we've included C# as a foreign key in the Telephones table.
   
   Figure 7: Primary key for Customer table is foreign key in Telephone table
   
   
3. Include other information that you need to distinguish between instances of the repeating field. In this case, we've introduced a description field that tells you what type of telephone number you are talking about (e.g., home, business, mobile, fax, etc.). Note that introducing this field gives you more flexibility for describing telephone numbers in the future (e.g., you can add best day, best evening to the list - you aren't limited to field descriptions embedded in the field names you would use in a flat-file database where you needed a new field for each new type of phone).
4. This means you now have a more general telephone table that can adapt to new types of phone numbers as they arise without losing its connections to each customer. The result looks something like this:

Figure 8: New table design

Repeat until ERD is stable

This last step is included to encourage you to review your ERD analyses. It is important to become familiar enough with these ideas so that you can express real-world relationships fairly readily as entities and relationships. For example, how would one handle the fact that customers in the real world (rather than in this exercise) might be expected to order more than one order during the same telephone call? How would you redesign the database to maintain a list of past prices for products? A history of shipping costs?

As you can see, there are some reasons for anticipating that one's ERD will not be stable following your first draft. The point is to be able to make useful changes quickly before you lock those changes into a database design.