One to One Relationships
The one to one (1:1) relationship is infrequently used even though it’s very
powerful. It seems strange, after all, to split records into two tables if
each record is described by the same primary key.
Types of Relationships
Let's back up just a second to look at the most common relationship: the
one-to-many. A one-to-many relationship might describe the relationship of
one customer to many orders or of one order to many products.
In these relationships, all information about a particular
customer like contact info and credit rating is stored in one table, let’s
call it Customers. All order information like order date and ship date is
stored in another table (Orders) and all product information like product
name, model number and cost in a third (Products).
You may also hear about a relationship referred to as many-to-many, though
no such thing really exists. The single so-called many-to-many relationship
is actually two or more one-to-many relationships. In our tables above, the
Customers table and the Products table have a many-to-many relationship
through the Orders table. The Orders table acts as a joining table. In ‘the
real world’, you certainly hope that one customer orders many different
products and you also hope that one product will be bought by many different
customers.
These relationships make sense when you think about them in ‘the real
world’, but the one-to-one relationship doesn’t. If the record belongs
together, then why do we divide it? There are several reasons.
Why Chop Up The Record?
One of our clients has a database table that contains several hundred
descriptors of any given material: thermal conductivity, heat capacity,
thermal diffusivity; electrical resistance and capacitance; shear, tensile
and compressive strengths; the list goes on and on. As it turns out, only a
half a dozen are regularly used in any kind of study.
When this big table is used in a query, performance is terrible. Simple
queries can run for over ten minutes. To get a huge performance boost, the
table was broken into two pieces: the frequently-used properties and the
infrequently-used properties. All of the data is kept and can be referred to
when needed, but everyday queries run in seconds because they are run on a
smaller table.
Another reason to split records into two tables is because individual
items share many, but not all, descriptors. Consider a table in a database
containing mailing information for clients in hospitality. Hotels and
restaurants contain many descriptors in common like address, annual revenue
and owner. But only hotels require a field describing how many rooms are in
the hotel and whether or not they have a swimming pool. Only a restaurant would
have a field describing their type of food and whether or not they have a
drive-through.
A one-to-one relationship can add to the flexibility of a table by adding
‘custom’ fields to the main table. One related table would contain the
primary key from the main table and fields appropriate to hotels. Another
related table would contain the primary key from the main table and fields
appropriate to restaurants.
When a mailing goes out that is indifferent to
the distinctions of hotels and restaurants, the custom fields (indeed,
entire tables) can be ignored. If the mailing goes out to hotels that have
more than a certain number of rooms, the related table is joined to the main
table in the query.