Re: One to One Relationship

Hi Martin, thanks for your input. I appreciate your time and views.

Comments also inline.

"Martin Burbridge" <pobox002_at_bebub.com> wrote in message news:45a06b65.0303200630.6ce1decc_at_posting.google.com...
> 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.

I'm not sure what the intermediate table is that you mention, as you would just add a employee key into the department table as a foreign (which is what Daniel correctly stated), therefore there would be no intermediate table, just the employee and department table.

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

I think there is a misunderstanding, no new table is needed, only the two original tables which are employees and departments. The relationship of manager doesn't create a new table, it simply adds a new employee ID column into the department table. This then allows you to find out who is the manager of the department. The question this then creates is how can I enforce this foreign key constraint, between department and employees, to a cardinality of 1:1 with oracle? I hope this makes the problem clearer for you, maybe I didn't explain it correctly originally. Also keep in mind, that there would also be a 1:M relationship between department and employees indicating what employees worked in what departments.

>
> > 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 - 18:14:47 CST