Re: One to One Relationship

Peter wrote:

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

Assuming a person manages one and only one department (otherwise it is a 1:M) put a column in the department table with the person id of the manager from the person table.

If it is a 1:M or M:M then I would suggest an intersecting entity with two columns: One the department id and the second the person id of the manager.

Daniel Morgan Received on Wed Mar 19 2003 - 10:28:31 CST