Perhaps the simplest type of relationship is the one-to-one relationship. If it's true that any instance of entity X can be associated with only one instance of entity Y, then the relationship is one-to-one. Most IsA relationships will be one-to-one, but otherwise, it's fairly unusual to find one in the problem domain.
One thing to be careful of: When choosing a one-to-one relationship between entities, you need to be sure that the relationship is either true for all time or, if it does change, that you don't care about past values. For example, say you're modeling the office space in a building. Assuming that there is one person to an office, there's a one-to-one relationship between the Office and Employee relations, as shown in Figure 3-6.
Figure 3-6. There Is a One-to-One Relationship between Office and Employee
But the relationship between an employee and an office is true only at a specific moment in time. Over time, different employees will be assigned to the office. (The arrangement of offices in the building might change as well, but that's a different problem.) If you use the one-to-one relationship shown in Figure 3-6, you will have a simple, clean model of the building, but you'll have no way of determining the history of occupancy.
You might not care. If you're building a system for a mail room, you need to know where to send Jane Doe's mail today, not where it would have been sent three months ago. But if you're designing a system for a property manager, you can't lose this historical informationthe system will be asked to determine, for example, how often tenants change.
Although one-to-one relationships are rare in the real world, they're very common abstract constructs, and extremely useful ones. They're most often used in two situations: when you want (or need) to reduce the number of attributes in a relation or to model entity sub-classes.
There is a physical limitation of 255 fields per table if you're using the Jet database engine, and 1024 fields per table if you're using SQL Server. I'm suspiciousvery suspiciousof any data model that exceeds these limitations. But I have occasionally seen systems, usually in science and medicine, where the entities genuinely had more than 255 attributes. In these cases, you have no choice but to create a new relation with some arbitrary subset of attributes and to create a one-to-one relationship between it and the original, controlling relation.
A problem domain that often appears to require that the physical limitations on table size be exceeded is the modeling of tests and questionnaires. Given a test with an arbitrary number of questions, you might be tempted to model an individual's responses as shown in Figure 3-7.
Figure 3-7. This Structure Is Sometimes Used to Model Tests and Questionnaires, but It Is Not Ideal
This structure is easy to implement, particularly if the database will only be used for this test, and this test only. But it is not the best solution, and will fail if you ever have a test with a different number of questions. The answer attributes are a repeating group, and the relationship is therefore not in first normal form. A better model is shown in Figure 3-8.
Figure 3-8. Although More Difficult to Implement, This Structure Is Preferable for Modeling Tests and Questionnaires
A more interesting use of one-to-one relationships is for entity sub-classing, a concept borrowed from object-oriented programming. To see some of the benefits of sub-classing entities, let's first look at a more traditional implementation. In the Microsoft Access Northwind sample database, each product is assigned to a product category, as shown in Figure 3-9.
Figure 3-9. Each Product in the Northwind Database Is Assigned to a Product Category
Having a Categories relation allows the products to be grouped for reporting purposes and might be all that is required by your problem space. But with this design, you can treat a product only as a product, not as an instance of its specific category. Any attributes defined for Products are stored for all products, whatever their type. This isn't a very close match to the problem domainBeverages intrinsically have different attributes than Condiments.
You might be tempted to model the Northwind product list as shown in Figure 3-10. This model allows us to store all the information specific to each product type since each relation can have a different set of attributes. The problem with this schema is that it makes it difficult to treat a product as a product.
Figure 3-10. This Model Allows Category-Specific Attributes to Be Captured
Imagine, for example, the process of checking that a product code entered by a user is correct: "if the code exists in the x relation, or the y relation, or...". This is as ugly as the repeating group query in Chapter 2. Also, you might run into integrity problems with this structure if you have certain attributes that apply only to one product category (UnitsPerPackage, for example, might pertain to Beverages but not DairyProducts) and the category of a particular product changes. What do you do in these circumstances? Throw away the old values? But what if the change was accidental and the user immediately changes it back?
Sub-classing the product entity provides the best of both worlds. You can capture information specific to certain product categories without losing the ability to treat the products simply as products when that's appropriate, and you can defer the deletion of the no-longer-applicable information until you're certain it really is no longer applicable. Figure 3-11 shows a model developed using entity sub-classes.
Figure 3-11. This Model uses Sub-Classing to Provide the Capabilities of Figures 3-9 and 3-10
It must be said that while entity sub-classing is an elegant solution to certain types of data modeling problems, it can be an awkward beast to implement. To take just one example, a report containing Product details would need to include conditional processing to display only the fields appropriate to the current sub-class. This isn't an insurmountable task by any means, but it is a consideration. Under most circumstances, I wouldn't recommend that you compromise the data model to make life easier for the programmers. But there's certainly no point in adding the complexity of sub-classing to the model if all you need is the ability to group or categorize entities for reporting purposes; in this situation, the structure shown in Figure 3-9 is perfectly adequate and far more sensible.
Identifying the primary and foreign relations in a one-to-one relationship can sometimes be tricky. As always, you must base the decision on the semantics of the data model. If you've chosen this structure in order to subclass the entity, the generic entity becomes the primary relation and each of the sub-classes becomes a foreign relation.
In this situation, the foreign key that the sub-classes acquire is often also the candidate key of the subclasses. There is rarely a reason for subclasses to have their own identifiers.
If, on the other hand, you're using one-to-one relationships to avoid field limitations, or the entities have a genuine one-to-one relationship in the problem space, the choice will be somewhat arbitrary. You must choose the primary relation based on your understanding of the problem space.
One thing that can help in this situation is the optionality of the relationship. If the relationship is optional on one side only (and I've never seen a model where a one-to-one relationship was optional on both sides), the relation on the optional side is the foreign relation. In other words, if only one of the entities is weak and the other regular, the regular entity is the primary relation and the weak entity is the foreign relation.