Re: One-To-One Relationships

"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.

Roy Received on Tue Oct 30 2007 - 10:26:30 CET