The first step in developing a database model using ER modeling is to consider the requirements of the system. The requirements for the online winestore were described in Chapter 1 and are typically gathered from a scope document, customer interviews, user requirements documents, and so on.

Many of the requirements affect development of the ER model, while others are more general system requirements used to develop the web database application. One of the skills of ER modeling is extracting the requirements that impact on the database design from those that are functional elements of the system.

Once a system requirements analysis is complete, and the detailed requirements written down, you can proceed to the conceptual database design using the ER modeling techniques.

We can then say that an order is created on a date, and the system requirements in Chapter 1 identify that an order has a discount, a delivery cost, and a delivery note.

We can also say that this model of an order consists of one or more different wines and, for each different wine, a quantity of that wine is purchased. The subparts in each order—the different kinds of wines—are the items that make up the order. But is the wine itself part of an item? The distinction is hard, but the correct answer is probably no: this is a relationship, the items that make up an order are related to wines.

There are now two more entities—orders and items—and two relationships, which illustrates how difficult it is to reason about entities without considering how they are related. Determining entities isn't always easy, and many different drafts of an ER model are often required before a final, correct model is achieved. The ER model for the winestore took several attempts to get right.

Here are the item and order entities:

• The item entity—which is related to an order—has the attributes quantity and price.

• The order entity has attributes date, discount percentage, delivery cost, and delivery note.

The system requirements in Chapter 1 showed that wines are delivered in shipments. Each shipment is on a date and consists of a number of bottles, at a per-bottle and per-case price. How is this incorporated into the model? Perhaps the most obvious solution is to add quantity and price attributes to the wine entity. This doesn't work well: it is difficult to maintain the possibly different prices for different shipments and to maintain the correct shipment dates.

A good solution to the inventory problem is an inventory entity. This entity is related to the wine, and maintains different sets of data for each shipment of each wine:

• The inventory entity has an on-hand quantity, an item cost, a dateadded, and a case cost (for a dozen bottles).

The final entity is somewhat of an oddity. If a wine is a Cabernet Merlot, you can simply store the string Cabernet Merlot in an attribute in the wine entity. Another approach is to have a grape_variety entity, where each different grape variety is described individually. So, Cabernet is one instance of a grape_variety entity, and Merlot is another. The grape_variety entity is then related to the wine entity. This approach does seem overly complicated, but let's opt for it anyway because it introduces an instructive twist to our modeling, a many-to-many relationship discussed in the next section.

Let's add two attributes to the grape_variety entity, variety (the description) and ID (a counter used to, for example, record that Cabernet is the first word in Cabernet Merlot, and Merlot is the second word.

• The grape_variety entity has two attributes, ID and variety.

There are other possible entities. For example, the shopping basket could be an entity: the shopping cart is an object that contains items that will be ordered. However, a shopping cart is an incomplete order and, hence, it's omitted from the entity list. Including it is perhaps valid, and depends on how the entities are interpreted from the requirements.

There are also other entities that are outside the scope of our requirements. For example, a country might contain many regions, but there is no requirement for countries to be modeled in our system. Also, the winestore itself is an entity, but we are actually interested in the entities that make up the winestore, not really the whole concept itself. Selecting entities is all about getting the granularity and scope of choice right.

We have hinted at but not explicitly identified the relationships between the entities. For example, a winery is part of a region, a wine is made by a winery, and an item is related to a wine. The first step is to identify the entities and their attributes; the second step is to identify how the entities are related.

Cardinality refers to the three possible relationships between two entities[14] and, before you can consider how the entities are related, you need to explore the possible kinds of relationship:

[14]Actually, relationships can exist between as many entities as there are in the model. Also, we have deliberately omitted the distinction with relationships that are optional, that is, where one instance of an entity—such as a customer—can exist without a related entity—such as an order. However, we avoid complex relationships in this appendix; more detail can be found in the books listed in Appendix E.

One-to-one

A one-to-one relationship is represented by a line without any annotations that joins two entities. One-to-one means that for the two entities connected by the line, there is exactly one instance of the first entity for each one instance of the second entity. An example might be customers and user details: each customer has exactly one username and password, and that particular username and password is only for that customer.

One-to-many (or many-to-one)

A one-to-many relationship is represented by a line annotated with a 1 and an M (or a 1 and an N). One-to-many means that for the two entities connected by the line, there are one or more instances of the second entity for each one instance of the first entity. From the perspective of the second entity, any instance of the second entity is related to only one instance of the first entity. An example is wineries and wines: each winery sells many wines, but each wine is made by exactly one winery. Many-to-one relationships are the most common relationships between entities.

Many-to-many

A many-to-many relationship is represented by a line annotated with an M and an N. Many-to-many means that for the two entities connected by the line, each instance of the first entity is related to one or more instances of the second entity and, from the other perspective, each instance of the second entity is related to one or more instances of the first entity. An example is the relationship between wineries and delivery firms: a winery may use many delivery firms to freight wine to customers, while a delivery firm may work for many different wineries.

It isn't surprising that many database modelers make mistakes with cardinalities. Determining the cardinalities of the relationships between the entities is the most difficult skill in ER modeling, but one that, when performed correctly, results in a well-designed database. To illustrate how cardinality is determined, let's consider the relationships between the entities in the winestore and present arguments for their cardinalities.

To assign cardinalities—which crucially affect the database design—start with the relationship of wines to wineries. To begin, you need to decide what sort of relationship these entities have and assign a descriptive term. A good description of the relationship between wines and wineries is that a winery makes wine. Now draw a diamond labeled makes between the entities wine and winery, and connect the relationship to the two entities with an unannotated line. This process is shown in Figure C-3 (A).

Figure C-3. A partial ER model showing the relationship between wines and wineries

The next step is to determine what cardinality to assign to this relationship. The most effective approach to determining cardinality is to consider the relationship from the perspective of both entities. From the perspective of a winery, the question to ask is:

Does a winery make exactly one wine or one or more wines?

The answer is the latter, so you write M at the wine-end of the relationship. From the other perspective—that of the wine—you can ask a second simple question:

Is a wine made by exactly one or more than one winery?

This answer is the former—that limitation is noted in the system requirements—and you can write a 1 at the winery-end of the relationship. The annotated, one-to-many relationship is shown in Figure C-3 (B).

Dealing with the relationship between wineries and regions involves similar arguments. You begin by describing the relationship. In this case, an appropriate label might be that a winery is situated in a region. After drawing the diamond and labeling it, now consider the cardinalities. A winery belongs in exactly one region, so label the region end with a 1. A region contains one or more wineries, so you label the winery end with an M.

There are three more relationships that can be completed using the same one-to-many arguments:

• The consists-of relationship between orders and items

• The purchase relationship between customers and orders

• The stocked relationship between wines and inventories

You can label all three with a 1 and an M (or N). The consists-of relationship is labeled with a 1 at the order end and an M at the item end. The purchase relationship is labeled with an M at the order end and a 1 at the customer end. The stocked relationship is labeled with an M at the inventory end and a 1 at the wine end. These relationships are shown as part of Figure C-4.

Figure C-4. An almost complete ER model for the winestore

You know that the users and customer have a one-to-one relationship. Now draw a line between the two entities and label it with a 1 at each end. Label the relationship as has. You can also add the password attribute to the customers entity and omit the users entity altogether. However, to fully illustrate the different features of ER modeling, let's maintain the separation between customer and users entities.

The final two relationships are a more difficult to identify and annotate.

The first is the relationship between an order item and a wine. The one-to-many cardinality isn't a difficult proposition, but determining that this relationship actually exists is harder. When considering what makes up an order, there are two possibilities: an item can be related to a specific inventory entry, or an item can be related to a wine. The former is possibly more intuitive because the item that is delivered is a bottle from our inventory. However, the latter works better when modeling the system's data requirements.

In our design, a customer order is made up of quantities of wines. You can label this relationship as sells. The price of the wine is copied from the inventory and stored in the order. This design is appropriate because the relationship between a customer and a specific bottle is uninteresting once the order is shipped and, arguably, it is uninteresting even as the order is packed.

The second difficult—and final—relationship is that between wines and grape varieties. Naming the relationship is easy: let's call this relationship blend. Determining the cardinality is harder. First, consider the relationship from the wine perspective. A wine can contain more than one grape variety when it is a blend, so you label the grape variety end of the relationship with an M. Now consider the relationship from the grape variety perspective. A grape variety, such as semillon, may be in many different wines. So, let's settle on a many-to-many relationship and label the wine end with an N.

Our ER model is almost complete, and Figure C-4 shows it with all its entities and relationships. What remains is to consider the key attributes in each of the entities, which are discussed in the next section. As you consider these, you can adjust the types of relationships slightly.

There are a few rules that determine what relationships, entities, and attributes are, and what cardinalities should be used:

• Expect to draft a model several times.

• Begin modeling with entities, add attributes, and then determine relationships.

• Include an entity only when it can be described with attributes that are needed in the model.

• Some entities can be modeled as attributes. For example, a country can be an entity, but it might be better modeled as one of the attributes that is part of an address.

• Avoid unnecessary relationships. Only model relationships that are needed in the system.

• One-to-one relationships are uncommon. If two entities participate in a one-to-one relationship, check that they aren't actually the same entity.

• Many-to-many relationships are complex. Use one-to-many relationships in preference where possible.

A less complicated approach for unique identification—and a common one that's guaranteed to work—is to add an identifier number (ID) attribute to the entity. A short unique identifier also leads to better database performance, as discussed in Chapter 3. Using this approach, assign ID #1 to the first customer, ID #2 to the second customer, and so on. In the model, this new attribute is underlinedto indicate that it uniquely identifies the customer as shown in Figure C-5.

Figure C-5. The customer entity with all attributes; the primary key is shown underlined

You can take the same approach with wine as for customers—for the same reasons—and add an ID field.

For wineries and regions, the name is most likely unique or, at least, it can be made so. However, for simplicity, you should also use the ID attribute approach to prevent any ambiguity or need for the winestore administrator to create unique names for wineries or regions. The same argument can be applied to grape varieties.

Orders can also be dealt with by a unique ID, as can items and inventory. However, the uniqueness of this ID may be questionable. To illustrate, consider an example. You can number each order across the whole system uniquely, beginning with the system's first order #1. Alternatively, you can combine the customer ID with an order ID and begin each different customer's orders with order ID #1. The combination of customer ID and order ID is still unique, e.g., customer #37, order #1 is different from customer #15, order #1. This latter scheme is an example of a full participation relationship by a weak entity: an order isn't possible without a customer (hence, the term full participation) and the customer ID forms part of the order entity's unique identifier, hence the term weak entity.

You can use the scheme of full participation by a weak entity for orders; the other approach of numbering orders across the whole collection also works well. An advantage of this scheme is that the order number is more meaningful to the user—for example, a user can tell from their order number how many orders they have placed—and the order number provides a convenient counting tool for reporting. Participation is discussed briefly in the next section and weak entities are discussed in more detail later in Section C.2.1.7.

You can follow similar arguments with items. An item can be uniquely numbered across the whole system or can be numbered from #1 within an order. Again, this depends on the participation and, as with orders, we follow the latter approach. The same applies for inventory, which is numbered within a wine.

Because customer and users have a one-to-one relationship, the customer ID can be used to uniquely identify a user. Therefore, the users entity has full participation as a weak entity in the relationship with customer.

Other ER modeling tools include double ellipses and double lines. These tools permit the representation of other constraints, multivalued attributes, and the specification of full participation. In addition, it is possible for a relationship to have an attribute, that is, for a diamond to have attributes that are part of the relationship, not part of the entities. Useful references for more advanced ER modeling—and enhanced ER (EER) modeling—are provided in Appendix E.

Double lines as relationships indicate full participation and represent cases where an instance of one entity can't exist without a corresponding instance of the entity that it is related to. An example is an order as discussed in the previous section. An order can't exist without a customer to make that order. Therefore, correctly, the relationship between order and customer should be represented as a double line; the same constraints apply in the model to items and inventories.

Dashed ellipses represent multivalued attributes, attributes that may contain more than one instance. For example, the attribute address can be multivalued, because there could be a business address, a postal address, and a home address. Multivalued attributes aren't used in our model.

In addition, there are other extensions to the modeling techniques that have already been applied. For example, more than two entities can be related in a relationship (that is, more than two entities can be connected to a diamond). For example, the sale of a wine can be described as a three-way relationship between a wine, a customer, and an order. A second complex technique is the composite attribute; for example, an attribute of customer is address and the attribute address has its own attributes, a street, city, and zipcode. We don't explore complex relationships in this book.

Figure C-6 shows the final ER model with the unique key constraints shown. Notice that for items, orders, users, and inventories, the attributes from other entities aren't included. They are instead indicated as weak entities with a double rectangle and they participate fully in the related entities as indicated by double lines.

Figure C-6. The complete ER model for the winestore database

If items, orders, and inventories are numbered across the whole system, you can omit the double rectangles. The double lines can be omitted if any entities can exist without the related entity.

A summary of ER notation tools is shown in Figure C-7.

Figure C-7. Tools used in ER modeling

To perform this step, you need to make decisions about attribute types in the SQL CREATE TABLE statements. Attribute types are discussed in Chapter 3.

There are several non-weak entities in the model. Begin with the region entity, which has the attributes region_id, region_name, description, and map. You might anticipate no more than 100 different regions, but being cautious is important if more than 1,000 regions need to be stored. Accordingly, a type of int(4) allows up to 10,000 regions. Using a similar argument, define region_name as a varchar(100). Because descriptions may be long, let's define description as a blob. A map—which is an image—is defined as a mediumblob.

As decided earlier in the chapter, the unique key of the region table is an ID, which is now called region_id. Accordingly, you define a PRIMARY KEY of region_id. A requirement of all primary keys is that they are specified as NOT NULL, and this is added to the attribute. Now automate the creation of the values by adding the auto_increment clause and a DEFAULT '0'. (Recall from Chapter 3 that storing NULL or 0 in an auto_increment attribute is a MySQL feature that automatically stores a unique ID larger than all other IDs for this table.)

The resulting definition for the region table is then as follows:

CREATE TABLE region (
  region_id int(4) DEFAULT '0' NOT NULL auto_increment,
  region_name varchar(100) DEFAULT '' NOT NULL,
  description blob,  
  map mediumblob,
  PRIMARY KEY (region_id),
  KEY region (region_name)
);

Notice an additional KEY on the region_name named region. By adding this key, you anticipate that a common query is a search by region_name. Also, a region must have a name, so a NOT NULL is added to the region_name attribute.

The CREATE TABLE statements for the other non-weak entities are listed in Example C-1. Remember, however, that this is only the first step: some of these CREATE TABLE statements are altered by the processes in later steps.

The table contains the primary keys of the wine and grape_variety and defines these—along with the ID attribute—as the PRIMARY KEY. No change is required to the wine or grape_variety tables.