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

Jack,

Perhaps I do not understand the question correctly, so I apologize if I've misinterpreted.

I would probably just make an enum field in the address table to record what type of address it is (billing or mailing). Then you can use a direct $hasMany relationship between your customers model and your address model. When you perform a query (e.g. you just want the billing address for a given customer), just specify in the $conditions array that 'Address.type'=>'billing'.

HTH, Travis

Answer 2

If a customer HASMANY addresses, use the has-many association:

http://book.cakephp.org/view/82/hasMany

If a customer HASONE (and only one) address, use the has-one association:

http://book.cakephp.org/view/80/hasOne

If customers could possibly share the same address (same record), you will need to use HABTM with the join table you alluded to.

More info about Cake's associations: http://book.cakephp.org/view/78/Associations-Linking-Models-Together

Answer 3

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.

Answer 4

I like Kyle's and Travis's suggestions, but you can also put the foreign keys the other direction.

If you want your addresses to be independent and have several other tables reference them, then you should be able to define two belongsTo relationships from customer to address. Each relationship then has to specify which field to use as the foreign key.

<?php

class Customer extends AppModel {
    var $name = 'Customer';                
    var $belongsTo = array(
        'BillingAddress' => array(
            'className'    => 'Address',
            'foreignKey'    => 'billing_address_id'
        ),
        'MailingAddress' => array(
            'className'    => 'Address',
            'foreignKey'    => 'mailing_address_id'
        )
    );  
}
?>

However, both of these solutions leave you open to orphaned addresses, because the foreign key constraint isn't really correct. The simplest solution might be to just add a bunch of optional foreign keys to the address table, like customer_id, company_id, employee_id, and so on. Then you've got a standard arc pattern, and the keys are pointing the right direction, so you get correct referential integrity.

Another solution is to design a more general entity table that has address as a child table. Then customer, company, and employee are all subtypes of the entity table. For more details on that style of schema, I recommend Data Model Patterns by David Hay.