Re: How can I specify columns for foreign key relationships in Oracle Designer?

First of all you need to add a third table to your ERD since the relationship between the Employee and Address table is "many to many". The third table will contain the EmployeeID and AddressID and those two fields together will comprise the Primary Key. You should add a third field called "AddressType" and this can be used to show whether it is a "Business" or "Home" address or maybe in some cases "Both". The "EmployeeID" will be a Foreign Key to the Employee table and the "AddressID" will be a Foreign Key to the Address table. This is the correct way to handle this relationship.

Peter C.
"Ally" <afirenze95_at_yahoo.com> wrote in message news:e7f9ce51.0106011119.189dc6b9_at_posting.google.com...
> Hi, would you readers out there know how I can specify which columns
> are used in establishing foreign key relationships in Oracle Designer?
>
> I am using Designer 6i, Oracle server 8.1.7 on Win2K. I created some
> entities in ERD, and after I ran it through the Database Design
> Transformer and view the entities again in Design Editor, Designer has
> created a new column in one of the entities for it to refer to the
> primary key in the other entity. I can view the properties of that new
> column, but I can't seem to change any of its properties, including
> its name.
>
> Here is some info about my situation and what I am trying to
> accomplish --
> -> I have a table named Addresses, with a primary key column named
> Address_id.
> -> I have another table named Employees, with a primary key column
> named Employee_id. This table also has a column called "Home_Address"
> which contains an Address_id. This table has another column called
> "Business_Adsress" which contains another Address_id.
>
> -> I want to have 2 foreign key relationships in the Employees table,
> columns "Home_Address" and "Business_Address", both referring to the
> primary key column in the Addresses table.
>
> -> I have run into 2 problems with Designer in trying to accomplish my
> task:
> 1) Because each employee can have more than one address, it is a one
> to many relationship in the ERD. From the documentation that I have
> read, it's always the "many" side of the relationship that gets a
> foreign key column created. Therefore, Oracle Designer decides that
> the Addresses table that should have a foreign key column referring to
> the Employees table. So it generates a foreign key column in the
> Addresses table refering to the primary key in the Employees table.
> But that is the opposite of what I want to do.
>
> 2) I have already specified the "Home_Address" and "Business_Address"
> columns in my Employees table, and I would like these 2 columns to be
> the foreign key columns. But I cannot find a way to specify that using
> these columns' Property Palette, or the Foreign Key Column Property
> Palette.
>
> I am new to Designer, so maybe the more experienced users could give
> me some pointers in how they approach an issue like this. I don't
> understand how this is suppposed to work, I mean, if you accept the
> foreign key column Designer creates for you, but you are not allowed
> to modify the name or any other properties of that column, then it's
> not doing you any good, is it?
>
> Anyway, any insight would be appreciated. I've pulled way too much of
> my hair out over this!
>
> Thanks!
> Ally
Received on Sat Jul 21 2001 - 23:48:28 CEST