Re: One to One relationships

Stu,

It is hard to come up with good examples of 1:1 relationships between different kinds of things, but let's pretend that we're a medical practice and take the example of patients and charts, which is the closest thing to your problem that comes to mind.

Option A:

CREATE TABLE patients (
  patient_id INT NOT NULL PRIMARY KEY,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  dob DATE NOT NULL,
  ...);

CREATE TABLE charts (
  chart_no INT NOT NULL PRIMARY KEY,

  patient_id INT NOT NULL UNIQUE REFERENCES patients (patient_id),
  created_date DATE NOT NULL,
  created_by INT NOT NULL REFERENCES personnel (emp_id),

Every patient has exactly one chart forever. In fact, in the minds of many of the administrative staff (hopefully not the physicians), patients and charts are the same thing.
We even go so far as to print cards for our patients featuring the chart number for identification. So we might try this:

Option B:

CREATE TABLE patients (
  patient_id INT NOT NULL PRIMARY KEY,
  chart_no INT NOT NULL UNIQUE,
  dob DATE NOT NULL,
  created_date DATE NOT NULL,
  ...);

In the real world, "date of birth" isn't actually an attribute of a chart. Neither is "created by" an attribute of a patient (at least not in the context presented here). Try stating the predicate for Option B. Nothing I've come up with is satisfying. But so long as the business rule that every patient has exactly one chart forever holds, Option B will work; and I've seen systems designed this way.

Having said this though, let's imagine that, dispite the equivelance in the minds of most of the staff, the new manager of the medical records department sees things differently. She notes all of the empty charts on the shelves and complains that her staff shouldn't be expected to prepare a chart for every Tom, Dick and Harry who calls for an appointment but never shows up. Now the business rule we were depending upon is invalid and if we've chosen Option B, we're forced into the unpleasantness of dealing with NULLs for all those attributes which actually describe a chart. In other words, my example is imperfect -- the relationship turns out to be "one to zero-or-one".

Every case that comes to mind where an apparent 1:1 relationship might have tempted me to describe two distinct entities in one table has turned out like this one. Option B, it turns out, suffers from transitive dependancies (patient_id -> chart_no -> created_date). And I think that my difficulty, and your difficulty, in thinking of a perfect example where this fact isn't consequential is a good reason to be wary of any temptation to avoid joins by bundling entities together.

As far as performance is concerned, the implications of options A and B will differ between DBMSs. Option B may save the DBMS effort on JOIN operations. But the overall benefit isn't obvious. If someone wants to count the charts currently checked out by Dr Jones, Option A may result in better performance in many systems due to their storage of data for rows in blocks or pages and the resulting fact that less IO will be required to scan the "narrower" table. It's possible that Option A may be better also when considering blocking, as a lock acquired by one process on a chart doesn't block another process changing the patient's address.

Hope that's helpfull.

• Rich

"stu" <smcgouga_at_nospam.co.uk> wrote in message news:b88rd8$9oo$1$8300dec7_at_news.demon.co.uk...
> If an entity must have exactly one thing what is the point in making
another
> entity (table) for the 'thing'?
>
> Eg: One person must have one and only one house. A person will stay in
> that house forever! (my attempt at making up a 1:1 relationship) Maybe
> this is my problem: It is difficult to find a model that has a 1:1?
>
> What do we gain from separating people and houses? Why would we not want
to
> store house as an attribute of person or vice versa?
>
> Guess what im asking is when should house become a separate entity vs an
> attribute?
>
> Practically speaking performing large row joins like this can be expensive
> and if we have a compound primary key we do not need 2 tables. Are there
> any other things I should consider?
>
> Im talking generically here. Im not actually interested in people and
> houses. If you have a better eg please use that.
>
> Cheers
> Stu
>
>
Received on Sat Apr 26 2003 - 22:52:43 CEST