Re: One to One Relationship

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 19 Mar 2003 05:01:54 -0800
Message-ID: <45a06b65.0303190501.4bc74da5@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 Received on Wed Mar 19 2003 - 07:01:54 CST