How do I use multiple foreign keys in one table referencing another table in cakephp

Question

Using cakephp, I have a generic address table, which I want to link to customers, vendors, contacts. most of the tables only have a 1 to 1 relationship, but I want my customers table to have 2

perhaps for clarification: I have a customers table

id, int
mailing_address_id, int
billing_address_id, int

and an addresses table

id,int
addr, varchar
city, varchar
etc....

Now I know I could put a customer_id in the addresses table. But I don't want to do that because I have a vendors table, and contacts table, and other tables that all are going to use the addresses table. the customer_id would not really be relavant to those other tables.

I'd like the Customer model to automatically link in the two addresses

Answer 1

Follow Travis Leleu's suggestion - because it's a good idea, regardless.

Then add an enum field to the Addresses table called table_id. The value of the table_id field could be "customer", "vendor", "contact", and whatever other tables would link to the addresses table.

Also include a single foreign key called entity_id. This foreign key would be the primary key of the corresponding customer, vendor, or whatever.

When you, for example, want the billing address for a certain vendor, add in the $conditions array:

'Address.entity_id'=>'123456'
'Address.table_id'=>'vendor'
'Address.type'=>'billing'

With this set-up you could have as many tables as you want referencing the Addresses table.