I have an address table, and employee and customer tables. I am thinking of adding a faddress_owner field to the address table and use it as a foreign key referencing both the customer_no and the employee_no number. I have just done that on mysql, but I do not feel that confident about it - is this acceptable? what are the pros and cons? and how else can I handle it?
Best Regards

see the thread Multiple Possible Parents

or see this article: Supertypes and Subtypes

your supertype is "entities which have addresses"

you have two subtypes: customer and employee

each of these subtype tables should have the supertype id as its primary key, as well as being a foreign key to the supertype table

then your address table can have a foreign key to the supertype table too

Thanks for the reply...
Maybe I am a little slow - the tables look like this:

create table customer (cust_No int not null, blah, blah, primary key(cust_no));

create table employee (emp_No int not null, blah, blah, primary key(emp_No));

create table address(addr_No int not null, addr_Owner int not null, blah, blah, primary key(addr_No), index(addr_Owner), Foreign Key(addr_Owner) references customer(cust_No), Foreign Key(addr_Owner) references employee(emp_No));

Am I on the right track?
Thanks again for your time and help!

no, not the right track

a foreign key can reference only one primary key

you cannot have

, Foreign Key(addr_Owner) references customer(cust_No)
, Foreign Key(addr_Owner) references employee(emp_No)

in the address table

Hi

From reading the other post, am I right to think that I can have the address table have cust_no and emp_no fields, i.e.

create table address(addr_No int not null, cust_no int not null, emp_no int not null, blah, blah, primary key(addr_No),
index(cust_No), index(emp_No),
Foreign Key(cust_No) references customer(cust_No),
Foreign Key(emp_No) references employee(emp_No));

Thanks again!

yes you can do that

i wouldn't want to be around when you have to rewrite your code to accommodate a third entity having an address...

I agree, I also would not want to be there
my 1st design was using link tables:-
Customer_address, Employee_Address, etc to get around this, then I started thinking about the address_owner field. I still am not sure why it is not acceptable to have address_owner foreign key link to both customer and employee tables - I obviously need to spend more time on this forum and read more on dbs..
Best Regards