Unanswered: Foreign Key Referencing more than 1 table?
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?
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),
Foreign Key(cust_No) references customer(cust_No),
Foreign Key(emp_No) references employee(emp_No));
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..
Originally posted by r937
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...