Re: One to One Relationship

From: Peter <no_email_at_no_email.com>
Date: Thu, 20 Mar 2003 13:55:41 +1000
Message-ID: <b5be5j$2tr$1@bunyip.cc.uq.edu.au>

"Martin Burbridge" <pobox002_at_bebub.com> wrote in message news:45a06b65.0303190501.4bc74da5_at_posting.google.com... > "Peter" <no_email_at_no_email.com> wrote in message news:<b58pu5$q2r$1_at_bunyip.cc.uq.edu.au>...
> > > > For example, imagine that you have a table of employees and a table
of
> > > > departments that the employees work for. This would be a one to many
and
> > > > possible many to many relationship. Now imagine that you also want
to
> > > > capture the managers of each department. Each department can only
have
> > one
> > > > manager and an employee can only be a manager of one department, so,
you
> > > > would need a one to one relationship, called manages, between the

> >  employee


> > > > and department table.

> > > >
> > > > How else would you model this? Both entities, employees and
departments
> > are
> > > > completely different and thus you couldn't put them in the one
table.
> > Maybe
> > > > the database books that I have been reading have been incorrect or
has
> > > > database theory changed recently?
> > > >
> > > > 1 1
> > > > |employee| --- <manages> --- |department|
> > > >
> > >
> > > Not at all. 1:1 is often found in a logical model. One normalizes
then,

> >  where

> > > appropriate, denormalizes when creating the physical model.
> > >
> > > Same goes for M:M (many to many) relationships. They may exist in a

> >  logical

> > > model but they absolutely do not belong in a physical model.
> > >
> > > Daniel Morgan
> > >

> >
> > Ok then, then how could I change the above model so that it didn't have to
> > be a one to one and still have it make sense? Afterall, there can be only
> > one manager per department, but the manager should go in the employee table
> > where there could be many employees per department. I'm just curious as I
> > don't know how it could be changed to fit a physical model and still make
> > sense as implementing this another way may save me heaps of time and make

> > the databse heaps simpler.
>
> I'm not certain if I've got this right, you say how can you change it
> so it doesn't have to be 1:1, but then describe a situation where it
> does have to be 1:1. Assuming the latter, your department table would
> have a manager id that has a unique index and is a foreign key to the
> employee table. There is no need for an intermediate table in a 1:1
> relationship.
>
> Hth
> Martin

The original question I was asking was in regard to Daniel stating the following,

"By ignoring the rules of normalization and deciding to turn a relational database into an electronic version of a bunch of 3x5 cards."

which was in reference to the question put forward originally which was,

"How can a one to one relationship between two tables be created in Oracle 9i?"

So I was wondering why Daniel had this view, and so I stated a case in which I thought a 1:1 relationship was necessary and so you needed to enforce it with Oracle.

I'm now waiting for someone to either state that 1:1 is necessary, which I believe, but hard to enforce the cardinality issues, or that 1:1 isn't needed as Daniel seemed to suggest and which I think is incorrect. Please correct me if I'm wrong. Received on Wed Mar 19 2003 - 21:55:41 CST