COMMON DATA MODELING PROBLEMS - A Developer's Guide to Data Modeling for SQL Server - page 183

Now let's look at a bad 1:1 that would probably mean you have too
many entities. In this example, we have a data model that stores informa-
tion about a small nonprofit organization consisting of only a board of di-
rectors and no other employees. You could model the board members as
shown in Figure 8.8.

F IGURE 8.8

Board of directors model for a small nonprofit organization

In this model, we have two entities—one for the board members and
one for the board members' positions—and the relationship between them
is 1:1. We have to ask ourselves, “What does this buy us? Is there any value
in splitting this data into two entities versus adding a position attribute to
the employee table?” If the answer is that we are not adding value, then we
should collapse these two entities into one table. If the second table were
holding a lot of extra detail about the position and not every board mem-
ber had an assigned position, then we would be more willing to let the en-
tities exist as two. In our case, we would opt to combine the entities.
When examining 1:1 relationships you should ask yourself a couple of
questions.

1. Are the two entities truly distinct?
2. Are the values in one of the entities only attributes of the other
entities?

If you look at each 1:1 in this way, you will avoid potential problems that
can crop up later.
That said, if you have an entity and quite a few attributes are needed
only for a small minority of your records, you might consider splitting them
off with a one-to-one relationship. This is a rare case, but it can help keep
your model cleaner and simpler to use. For example, suppose you have
an entity that will contain as many as 10 million records, and it has 100