There is also one crucial relationship that links the wines sold to the customer, that is, the relationship between an order item and the inventory. Last, a wine contains one or more different grape variety entities.
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.