I am trying to create a system where several parent tables share the same
child table.
For example - Employee and Client will have their addresses stored in
Address.
I have no problems creating the tables. However, Interbase has a different
interpretation of my intentions:
For every primary key in the child table, it expects to find matches in
BOTH
parent tables' primary keys. I can enter ADDR_ID "1" only if EMP_ID "1" and
CLI_ID "1" already exist. This makes my design useless.
Is there a way to achieve my goal without using a seperate id system for
the Address table, and intermediary table to connect it to the parent
tables?

Below is the SQL script file I created.

Thanks for any advice,

Irad Carmi

CREATE TABLE EMPLOYEE (
       EMP_ID  integer NOT NULL PRIMARY KEY
);

CREATE TABLE CLIENT (
       CLI_ID  integer NOT NULL PRIMARY KEY
);

CREATE TABLE ADDRESS (
       ADDR_ID integer NOT NULL PRIMARY KEY
);

ALTER TABLE ADDRESS ADD FOREIGN KEY (ADDR_ID) REFERENCES EMPLOYEE(EMP_ID);
ALTER TABLE ADDRESS ADD FOREIGN KEY (ADDR_ID) REFERENCES CLIENT(CLI_ID);

I think the problem you are having is that the design you have created is

Employee <-> Address <->Client, when you want

Employee -> Address and
Client -> Address.

What you may want to do is include an Address ID in both the Employee and
the Client table, retrieve the address ID after adding the address record
and place it in the Employee or Client record (which ever one you are
using).

Connie

On 7 Nov 1997 23:46:17 GMT, "Connie Okiro" <Ok...@compuserve.com>
wrote:

That could be a solution but how do you find the just-inserted
addresses's address ID? Use the address details to search the address
table? And if two addresses are virtually identical, which is possible
when two persons (or if a client is also an employee) share the same
address... And, in this last case, would you provide a mean to assign
the same address to several people? This can be tricky and so my
suggestion goes to composite keys.

The address table's key would be something like:

Type_of_person char(1)
/*  C for client and E for employee */
ID_of_person integer
 /* assuming both Employee and Client tables have this key
Address_Number smallint
/* sequential number (1 for first address, 2 for second, etc...) */

This structure is much more flexible and easy to maintain as you don't
need generators (and thus to retrieve the generated keys) for the
address table... The drawback is that, if you want to ensure
integrity, you have to enforce it through triggers...

Nuno

Nuno Leal

----
email: nl...@mail.telepac.pt

Also, there would be duplicate data if two clients or two employees, or a
client and and employee shared the same address.

Another solution could be to have a third table - PersonID, PersonType,
AddressSequence, and AddressID.  You would still have to have a stored proc
or something to retrieve the new address's ID.  But then, you could
duplicate the addresses easier, because then you could do something like
assign 'Person 2 the same address as Person 1'. (Assuming you know that
they have the same addresses).

Connie