Describing relationships

French | Spanish | Portuguese | Italian | German | Japanese | Chinese | Korean | Russian | Arabic

Something else that gives us good insight into how the software works with the database is identifying which attributes are absolutely necessary to describe each entity. If an attribute must be present to effectively describe an entity, that attribute cannot contain a NULL value. NULL values have wider significance than may first come to mind; if an attribute cannot be NULL, its value must be acquired during all business processes that create those entities. You may find that significant changes to business processes become necessary just so that you can create correctly defined entities in your database. It may seem backward to most readers, but waiting until last to consider attributes’ data types makes perfect sense. In the same way that considering entities separately from their structure in the beginning of the engineering process helped you to concentrate without distraction, waiting until the last minute to consider attribute data types prevents you from being distracted from more important design issues, such as which attributes are critical to describing the entities to which they belong. If you always engineer your databases by using this layered approach, the Web applications that they support are easier to code, require less maintenance, and perform faster. To recap, you want to follow these steps in the process of creating a relational database:

1. Define the entities that your business processes use.

2. Determine which attributes describe each entity.

3. Determine which attribute or combination of attributes of each entity is the key that uniquely identifies every instance of that entity.

4. Declare the relationships between entities.

5. Determine which attributes of each entity are considered critical to the description of that entity.

6. Define the data type for each attribute.

The preceding six steps are always performed regardless of the target database platform (SQL Server, Oracle, etc.) on which you plan to deploy your database. You may also perform two additional steps in modeling your database. After you have a basic, logical data model, you need to describe a few details about how its relationships work. Then you go about extracting your Physical Data Model, or PDM, from the logical data model and describe the differences between them and why they are significant. The following parts detail how to go about these processes. Relationships are described by four attributes: cardinality, identification, optionality, and degree. These attributes are combined to create specific relationship types. Cardinality describes the quantitative dimension of the relationship as viewed from the parent table’s perspective. The relationship between the CustomerType and Customer tables, for example, is a one-to-many relationship, meaning that from the standpoint of the CustomerType parent table, one CustomerType row can be related to many Customer rows. The end of the relationship line with many lines (the “crows foot”) is connected to the “many” table, and the other end is connected to the “one” table. The zero above the crows foot denotes that there may also be zero children related to the parent, and the numeral one crossing the other end of the relationship line denotes that there must be one parent related to each child. To be perfectly accurate, this is known as a “one to zero or more” relationship. By contrast, the relationship between the InventoryItem and Category entities is a nonspecific relationship (commonly referred to as a many-to-many relationship), because you have no specific rules regarding the relationship between these two entities. You may have one inventory item that is related to multiple categories, a category with no related inventory items, or an inventory item with no category related to it at all. This makes perfect sense when you consider the previous description: “You may have one inventory item that is related to multiple categories, a category with no related inventory items, or an inventory item with no category related to it at all.” One-to-one relationships are also possible, where one row in the parent table is related to one and only one row in the child table. One-to-one relationships are less common than one-tomany and many-to-many relationships and can manifest themselves in more than one way in the physical data model. The related table might be implemented through an identifying relationship whose migrated foreign key also acts as the complete primary key of the inheriting table, or the related table may even be eliminated entirely in favor of migrating its attributes to the other table in the relationship. One-to-one relationships are an advanced design topic that is beyond the scope of this article.

Identification describes how the relationship contributes to the capability of each row in the child table to be uniquely identified among all other rows in the child table. Take, for example, the one-to-many relationship between CustomerType and Customer. Notice the foreign key Customer.CustomerTypeCode contributed by the relationship between the two tables? This foreign key doesn’t help to identify one row in the Customer table from any other row in the Customer table; the CustomerNumber value alone uniquely identifies each row in the Customer table without needing any help from any other column in the Customer table. Because this relationship doesn’t contribute anything to the uniqueness of rows in the Customer (child) table, it is considered a nonidentifying relationship. Now consider the relationship between the SalesOrder and OrderItem tables. Every row in the OrderItem table requires two pieces of data to uniquely identify it: the ItemNumber of the item being sold and the SalesOrderID of the sales order on which the sale is recorded. Together, these two values make up the primary key for the OrderItem table. If you don’t use the combination of SalesOrderID and ItemNumber as the primary key, business would be a disaster. Say that you sell three different toys: flying discs, rubber balls, and yo-yos. Your first sales order is for two yo-yos and a rubber ball, and your second is for ten flying discs. You cannot have a third sales order, because you now have three rows in your OrderItem table, each one containing a unique value for ItemNumber as the primary key. If you try to add another yoyo, rubber ball, or flying disc, the database will not allow it because doing so would enable duplicate primary key values. Clearly, ItemNumber cannot by itself be the primary key for the OrderItem table. If we add SalesOrderID to the primary key everything can work as planned, and your business can sell as many flying discs, rubber balls, and yo-yos on as many sales orders as it wants. When you sell another two dozen yoyos on a later sales order, there is no database conflict because the primary key consists of the combination of the ItemNumber and the SalesOrderID.

Because the relationship between the SalesOrder and OrderItem tables contributed something (the foreign key OrderItem.SalesOrderID) that is used to uniquely identify each row in the OrderItem table, it is considered an identifying relationship. In other words, if a foreign key in a table is also part of the primary key for that same table, the relationship that contributed the foreign key is an identifying relationship. Optionality describes whether a relationship must exist between all rows in the child table and one or more rows in the parent table. Take, for example, the Customer and SalesOrder entities and the relationship between them. Say that you use a common database to store sales transactions from both your physical storefront and your Web site. Storefront sales orders are for walk-in sales, for which you don’t need to gather customer information, but online sales are handled differently. The user enters the information necessary to ship his online order during the checkout process. If the purchaser is new to your Web site, you want to automatically create a customer record for him and then relate the sales order to this newly created customer. If the purchaser is a returning customer who has logged in, you want to retrieve his customer record and automatically fill his customer information in the entry form that appears during the checkout process. So some sales orders are related to a customer, and some are not. The relationship between Customer and SalesOrder, therefore, is an optional relationship. What this means is that the value of the foreign key SalesOrder.CustomerNumber may be NULL, or it may contain a matching value in the CustomerNumber column from a row in the Customer table, but no other values are possible. The relationship between the CustomerType and Customer tables is a different story. Every customer must be of a specific customer type, so every value in Customer.CustomerTypeCode must match an existing value in CustomerType. CustomerTypeCode. The relationship between the CustomerType and Customer tables, therefore, is a mandatory relationship. For this reason, the value of Customer. CustomerTypeCode cannot be NULL. Contrast and compare the Nullability of the foreign-key columns in the Customer and SalesOrder tables. If a foreign key is Nullable, the relationship that contributed it is an optional relationship. If a foreign key disallows NULL values, the relationship that contributed it is a mandatory relationship. A relationship between two tables is known as a binary relationship, because it relates two elements. The majority of relationships in your databases are binary relationships. A unary relationship relates a table to itself to represent a hierarchy, such as an inventory item that is itself composed of other inventory items. Unary relationships are also known as both recursive relationships and reflexive relationships.

The four attributes of a relationship can be combined to produce specific types of relationships. The majority of your database relationships will be one of the following four types:

Nonidentifying mandatory relationships.

Nonidentifying optional relationships

Identifying relationships

Nonspecific relationships

This relationship is by far the most common that you use. Nonidentifying mandatory relationships are used whenever a child must have one and only one parent to which it is related. The relationship between CustomerType and Customer is an excellent example, because every Customer must have a CustomerType associated with it, and each customer may be of only one type. The only difference between this type of relationship and its mandatory cousin is the fact that the foreign key in non-identifying optional relationships can contain NULL values in addition to values that match the primary key value in the parent entity. The relationship between the Customer and SalesOrder tables typifies a nonidentifying optional relationship. We’ve already discussed identifying relationships; an example is the relationship between the SalesOrder and OrderItem tables. Perhaps, however, illustrating a point here may help. A foreign key can be NULL if it is contributed by an optional relationship, but no part of any primary key can ever be NULL under any circumstances whatsoever. Because an identifying relationship makes the foreign key also a part of the primary key, that foreign key can never be NULL. If a foreign key cannot be NULL, it must be contributed by a mandatory relationship. In other words, all identifying relationships are mandatory. We’ve also discussed nonspecific relationships earlier, but another explanation of why no mandatory or optional is attached to the name of this relationship type is appropriate here. This lack of keys means that we have neither a mandatory nor an optional relationship—in other words, the relationship is nonspecific in its optionality. Furthermore, because you may have one inventory item that is related to multiple categories, a category with no related inventory items, or an inventory item with no category related to it at all, the cardinality of this relationship is also nonspecific. And because no foreign keys are present to either include or not include in a primary key, the identification of the relationship is nonspecific, too. Because nonspecific refers not only to the cardinality of the relationship, but also to the identification and optionality referring to this relationship as a nonspecific relationship is more correct than calling it a many-to-many relationship, as it is most often referred to.

Having a solid understanding of database relationships is just as important as knowing when not to create them. Just because you can relate two entities doesn’t mean that you should. To create an OrderItem, an application looks up the value of ItemNumber in InventoryItem, copies its Description and UnitPrice values into memory, and then creates an OrderItem by using these values (along with the ItemNumber and SalesOrderID values, of course). This process is the correct way to create an OrderItem. Declaring a relationship between the InventoryItem and OrderItem tables. Why is this relationship an incorrect one? Because a correct relationship perseveres over time, and this one doesn’t. The sale of an OrderItem is a legal transaction that occurs at a specific point in time and, as such, must be stored in the database by using the specific values that described the transaction at that point in time; these values must never change because of outside events. But eventually, InventoryItem.UnitPrice does change because of outside influences such as inflation, changes in cost, and so on, and after it does, the relationship between InventoryItem and OrderItem can serve only to corrupt the integrity of the transaction stored in the database. Quantity ×x InventoryItem.UnitPrice. One week later, the prices of inventory items change, and the same sales report is run again—but this time, the results are all incorrect because they no longer report on the facts of the sale at the moment in time that the sale was made. Instead, the report shows the current unit price for inventory items that were sold some time in the past at a different price, so this information is of no value at all. Always ask yourself before you declare a relationship in your database whether facts stored in the resulting data structure are certain to persevere over time or whether, at some point, some future set of conditions may make the relationship represent nonfactual data. Create a relationship between tables only if you’re certain it is permanent under all possible conditions. Otherwise, copy values between tables so that they preserve their values at that time. A database exists as a logical data model only until it is deployed on a specific database platform. Although all relational database platforms are basically similar, every database platform has its distinct capabilities and restrictions. So your database’s physical model on Microsoft SQL Server is at least somewhat different from its physical model on Oracle 9i. In other words, a database has only one logical model and one physical model for every database platform on which it is deployed.