Re: One to One Relationship

Comments in line...

"Peter" <no_email_at_no_email.com> wrote in message news:<b5b11q$8c$1_at_bunyip.cc.uq.edu.au>...
> Hmmn, seems to be a misunderstanding between us.
>
> Here is what the original posted question was:
>
> > How can a one to one relationship between two tables be created in Oracle
> > 9i?
>
> And this is what you said:
> ------------------------------
> By ignoring the rules of normalization and deciding to turn a relational
> database into an electronic version of a bunch of 3x5 cards.
>
> You can do it with constraints. You can do it with triggers. And you can
> decide to be smart and not do it at all. Get a book on relational database
> theory and normalization and read it.
> ------------------------------
>
> So I gave an example of a 1:1 relationship and asked how it could be done,
> and you have now replied to that answer (as can be seen below) stating that
> I should do it as a 1:1 relationship which is what I have done.
>

This is where it falls apart. You did not really give an example of a 1:1 relationship, you just attempted to model a relationship using an unneccessary intermediate table, which then required a 1:1 constraint.

> > 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.
>
>
> So now I'm all confused :(
>
> I thought that you originally said that by creating a 1:1 relationship you
> would be, "...ignoring the rules of normalization and deciding to turn a
> relational database into an electronic version of a bunch of 3x5 cards."
>
> But just now you have said, "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." and so creating a 1:1
> relationship (although this can't be enforced with standard oracle
> referential integrity, I think).

No it does not create a 1:1 relationship unless you only have 1 employee for each department. You created the 1:1 requirement by adding a table managers. Removing that table and having only department and employee tables removes the need for a 1:1 contraint. As Daniel and I have pointed out you then have a foreign key from manager id in department to employee id in employees and have a unique constraint on manager id and that achieves your stated goal of 1 manager per department and the employee managing only 1 department. Or am I missing something?

> So, just getting back to the original question, "How can a one to one
> relationship between two tables be created in Oracle 9i?" as I think that we
> have established the need for it.

I would disagree that the need has been established using this particular example. Received on Thu Mar 20 2003 - 08:30:11 CST