Re: One-To-One Relationships

Roy Hann wrote:
> "Phil Reynolds" <philr2354_at_msn.com> wrote in message
> news:CGzVi.2012$%13.1165_at_newssvr22.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

>
> Roy
Received on Wed Nov 21 2007 - 08:26:40 CET