One-To-One Relationships

#1

One thing that's not clear to me is when it's appropriate to create a
one-to-one relationship. I mean, in some cases it's obvious, if there's a
set of data that wouldn't always apply; then you'd want to create that set
of fields in a separate table with a one-to-one relationship. But in what
other cases? After the number of fields in a table is greater than X?

I'm just curious about what thoughts/theories/ideas people have about
one-to-one relationships, because that's something that's never been
entirely clear to me.

Thank you.

#2

On 30 Okt., 07:53, "Phil Reynolds" <philr2... (AT) msn (DOT) com> wrote:

Quote:

One thing that's not clear to me is when it's appropriate to create a
one-to-one relationship. I mean, in some cases it's obvious, if there's a
set of data that wouldn't always apply; then you'd want to create that set
of fields in a separate table with a one-to-one relationship. But in what
other cases? After the number of fields in a table is greater than X?

I'm just curious about what thoughts/theories/ideas people have about
one-to-one relationships, because that's something that's never been
entirely clear to me.

Thank you.

I've done it in two cases:
1) when I have a feeling (or even promise) that today's requirements
might change in the future and the 1:1 cardinality might be 1:n after
a year and
2) when entity (and later table) contains different kind of attributes
and application/user usually will be interested in only part of them.
So probably I'd separate all attributes in two logical parts, two
entities with realationsip 1:1.

However such cases are quite rare.

I've never done that only because there are too many attributes in an
entity.

Gints Plivna
http://www.gplivna.eu

#3

On Oct 30, 9:21 am, Gints Plivna <gints.pli... (AT) gmail (DOT) com> wrote:

Quote:

On 30 Okt., 07:53, "Phil Reynolds" <philr2... (AT) msn (DOT) com> wrote:

One thing that's not clear to me is when it's appropriate to create a
one-to-one relationship. I mean, in some cases it's obvious, if there's a
set of data that wouldn't always apply; then you'd want to create that set
of fields in a separate table with a one-to-one relationship. But in what
other cases? After the number of fields in a table is greater than X?

I'm just curious about what thoughts/theories/ideas people have about
one-to-one relationships, because that's something that's never been
entirely clear to me.

Thank you.

I've done it in two cases:
1) when I have a feeling (or even promise) that today's requirements
might change in the future and the 1:1 cardinality might be 1:n after
a year and
I have done it in this kind of situation as well. As for the rest, it

really happens more often (at least, to someone, that, like me, has to
pick up crappy DBs quickly produced by someone else who is not a DBA
or DB professional in general) to transform the 1:1 into 1 table
rather than split 1 table into 2 with 1:1 relationship.

#4

"Phil Reynolds" <philr2354 (AT) msn (DOT) com> wrote

Quote:

One thing that's not clear to me is when it's appropriate to create a
one-to-one relationship. I mean, in some cases it's obvious, if there's a
set of data that wouldn't always apply; then you'd want to create that set
of fields in a separate table with a one-to-one relationship. But in what
other cases? After the number of fields in a table is greater than X?

I'm just curious about what thoughts/theories/ideas people have about
one-to-one relationships, because that's something that's never been
entirely clear to me.

(I am going to assume you mean "one-to-one" relationship although you also
say other things that make me think you might really mean something else.)

You need to distinguish which model you are referring to. If you are
talking about the logical/conceptual model then a good reason to use two
fact types in a 1:1 relationship is when the clarity of the conceptual model
is enhanced by doing so. For example, I find it highly distracting,
unhelpful, and in the long run, just flat-out wrong, to mix business data
and what I call state-of-the-application data in one table. An example
might be when attributes are added to support access arbitration (e.g. user
X has checked out (i.e. locked) this fact until further notice).

Alternatively, if you are talking about the physical model then there may be
any number of reasons to do it. The main concern in my experience is
performance. Some DBMS products do support certain queries better when the
rows are fragmented in some way. For instance if you routinely scan one or
two columns of what is conceptually a 100 column table with 100,000,000
rows, and rarely look at the other columns that might appear in the same
table, then using two tables in a 1:1 relationship (in which the columns are
separated according to frequency of use) can be a massive net benefit.
Hence all the gushing enthusiasm for column stores lately.

Roy

#5

Phil Reynolds wrote:

Quote:

One thing that's not clear to me is when it's appropriate to create a
one-to-one relationship. I mean, in some cases it's obvious, if there's a
set of data that wouldn't always apply; then you'd want to create that set
of fields in a separate table with a one-to-one relationship.

But that is not a one-to-one relative cardinality. That is actually a
one-to-zero_or_one relative cardinality.

But in what

Quote:

other cases? After the number of fields in a table is greater than X?

I'm just curious about what thoughts/theories/ideas people have about
one-to-one relationships, because that's something that's never been
entirely clear to me.

Thank you.

I have seen no theory-based arguments for partitioning a relation into
two relations with one-to-one relative cardinality. At the same time, I
haven't seen any compelling theory-based arguments against doing so
either. After all, with join and project one can express either from the
other.

#6

"Phil Reynolds" <philr2354 (AT) msn (DOT) com> wrote

Quote:

One thing that's not clear to me is when it's appropriate to create a
one-to-one relationship. I mean, in some cases it's obvious, if there's a
set of data that wouldn't always apply; then you'd want to create that set
of fields in a separate table with a one-to-one relationship. But in what
other cases? After the number of fields in a table is greater than X?

I'm just curious about what thoughts/theories/ideas people have about
one-to-one relationships, because that's something that's never been
entirely clear to me.

I think that a relationship is something you discover, not something you
create. Are you talking about creating two tables where there is a
one-to-one relationship between rows in table A and rows in table B? If
so, I think what you have created is not the relationship as such, but a
way of representing it in the database.

I hope this isn't too nit picky. I think the distinction between what you
discover via analysis and what you create during implementation (following
design) is very fundamental, and needs to be kept clear in all our
discussions.

#7

paul c wrote:

Quote:

paul c wrote:

David Cressey wrote:

...

I hope this isn't too nit picky. I think the distinction between
what you
discover via analysis and what you create during implementation
(following
design) is very fundamental, and needs to be kept clear in all our
discussions.

I don't think it's too nit picky at all. I wish the OP had given an
example because I think people here are talking about two different
things as you suggest, ie., a one-to-one as the ER people would see it
(eg., dept has one mgr and mgr has one dept) versus a relation that
somebody wants to make into two relations.

Regarding ER, here are some quotes from Codd's book (available for free
at acm.org). The sarcasm of the second one made me laugh.

From the first chapter:

quote
About six years after my first two papers on the relational model [Codd
1969 and 1970], Chen [1976] published a technical paper describing the
entity-relationship approach to database management. This approach is
discussed in more detail in Chapter 30, which deals with proposed
alternatives
to the relational model. Although some favor the entity-relationship
approach, it suffers from three fundamental problems:
11 Only the structural aspects were described; neither the operators upon
these structures nor the integrity constraints were discussed. Therefore,
it was not a data model.
2. The distinction between entities and relationships was not, and is still
not, precisely defined. Consequently, one person's entity is another
person's relationship.
3. Even if this distinction had been precisely defined, it would have added
complexity without adding power.
Whatever is conceived as entities, and whatever is conceived as
relationships,
are perceived and operated upon in the relational model in just
one common way: as relations. An entity may be regarded as inter-relating
an object or identifier of an object with its immediate properties. A
relationship
may be regarded as a relation between objects together with the
immediate properties of that relationship.
end quote

From chapter 30:
quote
Of the five
approaches discussed in this chapter, this one is clearly the winner in
terms
of its lack of precise definitions, lack of a clear level of
abstraction, and
lack of a mental discipline. The popularity of ER may lie in its multitude
of interpretations, as well as its use of familiar but obsolete modes of
thought.
end quote

Sadly, neither the familiarity nor the obsolescence have changed and
neither are they likely to.

#8

"paul c" <toledobythesea (AT) ooyah (DOT) ac> wrote

Quote:

David Cressey wrote:
"Phil Reynolds" <philr2354 (AT) msn (DOT) com> wrote in message
news:CGzVi.2012$%13.1165 (AT) newssvr22 (DOT) news.prodigy.net...
One thing that's not clear to me is when it's appropriate to create a
one-to-one relationship. I mean, in some cases it's obvious, if there's
a
set of data that wouldn't always apply; then you'd want to create that
set
of fields in a separate table with a one-to-one relationship. But in
what
other cases? After the number of fields in a table is greater than X?

I'm just curious about what thoughts/theories/ideas people have about
one-to-one relationships, because that's something that's never been
entirely clear to me.

I think that a relationship is something you discover, not something you
create. Are you talking about creating two tables where there is a
one-to-one relationship between rows in table A and rows in table B?
If
so, I think what you have created is not the relationship as such, but
a
way of representing it in the database.

I hope this isn't too nit picky. I think the distinction between what
you
discover via analysis and what you create during implementation
(following
design) is very fundamental, and needs to be kept clear in all our
discussions.

I don't think it's too nit picky at all. I wish the OP had given an
example because I think people here are talking about two different
things as you suggest, ie., a one-to-one as the ER people would see it
(eg., dept has one mgr and mgr has one dept) versus a relation that
somebody wants to make into two relations.

Thanks.

It gets even foggier because many of us (myself included) use the term
"relation" in reference to something that, in the strictest mathematical
definition, is really a "relationship".

#9

Roy Hann wrote:

Quote:

"Phil Reynolds" <philr2354 (AT) msn (DOT) com> wrote in message
news:CGzVi.2012$%13.1165 (AT) newssvr22 (DOT) news.prodigy.net...
One thing that's not clear to me is when it's appropriate to create a
one-to-one relationship. I mean, in some cases it's obvious, if
there's a set of data that wouldn't always apply; then you'd want to
create that set of fields in a separate table with a one-to-one
relationship. But in what other cases? After the number of fields in
a table is greater than X? I'm just curious about what
thoughts/theories/ideas people have about
one-to-one relationships, because that's something that's never been
entirely clear to me.

(I am going to assume you mean "one-to-one" relationship although you
also say other things that make me think you might really mean
something else.)
You need to distinguish which model you are referring to. If you are
talking about the logical/conceptual model then a good reason to use
two fact types in a 1:1 relationship is when the clarity of the
conceptual model is enhanced by doing so. For example, I find it
highly distracting, unhelpful, and in the long run, just flat-out
wrong, to mix business data and what I call state-of-the-application
data in one table. An example might be when attributes are added to
support access arbitration (e.g. user X has checked out (i.e. locked)
this fact until further notice).
Alternatively, if you are talking about the physical model then there
may be any number of reasons to do it. The main concern in my
experience is performance. Some DBMS products do support certain
queries better when the rows are fragmented in some way. For
instance if you routinely scan one or two columns of what is
conceptually a 100 column table with 100,000,000 rows, and rarely
look at the other columns that might appear in the same table, then
using two tables in a 1:1 relationship (in which the columns are
separated according to frequency of use) can be a massive net
benefit. Hence all the gushing enthusiasm for column stores lately.

Doh - thats why optimizers support index-only scan retrieval optimizations.
The correct solution to your example is almost invariably to have an index
on the two columns and let the optimizer figure it out. That is the whole
point of having one

Dweeb

Quote:

Roy

#10

"Dr. Dweeb" <spam (AT) dweeb (DOT) net> wrote

Quote:

Roy Hann wrote:

Doh - thats why optimizers support index-only scan retrieval
optimizations. The correct solution to your example is almost invariably
to have an index on the two columns and let the optimizer figure it out.
That is the whole point of having one

I completely agree with you.

Roy