Re: One to One relationships

"Jeffrey Bailey" <mrwizard1208_at_yahoo.com> wrote in message news:vMSpa.254765$o8.4232841_at_twister.tampabay.rr.com...
> I use a one to one relationship to define sub-categories of items. Whether
> or not this is correct, I don't konw. One of the best examples I can think
> of comes from the aircraft parts industry. All parts have certain
> characteristics in common (partnumber, serialnumber, qty, etc.) only certain
> parts have other characteristics (time limiters). I store all parts in the
> inventory table, and life-limited parts join in a one to one relationship to
> a life-limited parts table for the extra values that describe them.
>
> This arrangement works well in practice. I also prevents the inventory table
> from having characteristics that would be null for most parts(indicating
> unknown) or have to have an entry indicating "not applicable".

That is a good example and good practice, but it is not 1-to-1, it is 1-to-0or1.
A true 1-to-1 would (I guess) be what Chris Date would call sixth normal form. I.e. decomposing out all the nontrivial join dependencies of a relation. This is only really useful when considering how values change over time.

For example, if every Person has exactly one (unchanging) Name, exactly one (unchanging) Gender and exactly one (changing) Height, then there is an argument for using 6NF. E.g.

Person (PersonID, Name, Gender)
PersonHeight (PersonID, Height)

rather than a 5NF

Person (PersonID, Name, Gender, Height)

BTW the above also shows the limitation of the whole one-to-zero_one_whatever terminology. It is not always clear if the relationship describes what can occur at any given time (a Person has one Height), or what can occur over time (a Person can have one or many Heights). I believe most people assume the former, but I've seen people use both interchangeably in a single model!

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Thu Apr 24 2003 - 17:33:08 CEST