Using a "primary" FK

[moagrius]

Sorry about the less-than-descriptive title.  I'm having a hard time putting the issue into words.

 

Let's say I have a table CUSTOMERS that has just 2 columns: ID and NAME

 

And I have another table ADDRESSES that has standard address information (street, city, state, zip, etc) and an FK to CUSTOMERS (for this example, I'll call it CUSTOMER_FK).  Any customer can have multiple addresses.

 

I wanted to assign one row from ADDRESSES to be the billing/default/"primary" address for a customer.  A customer should only have one billing/default/primary address.

 

My initial thought was to add a column to CUSTOMERS called BILLING_ADDRESS that was an FK to the row in ADDRESSES that served as the billing/default/primary address.  This seems slightly redundant, though, and more importantly makes insertion a little less than elegant.  On creating a new customer from user input, for example, I'd have to create the CUSTOMERS row first, grab that ID, then create the ADDRESSES row (passing the CUSTOMERS ID just generated to CUSTOMER_FK), then go back and update the CUSTOMERS row with the ID of the newly created ADDRESS to serve as the FK for the billing/default/primary address.

 

Another thought was to, instead of the above, add a column to ADDRESSES that was a boolean indicator of whether or not the row was the billing/default/primary address for that customer, but AFAIK there's no way to ensure this is limited to a single address for any customer.

 

I'm guessing this isn't that rare, and there's probably a built-in solution that I'm not aware of.  If not, is there a more straightforward solution anyone could suggest?

 

TYIA

 

Using MySQL 5 (and PHP 5.3, if that matters).

[mikosiko]

Another thought was to, instead of the above, add a column to ADDRESSES that was a boolean indicator of whether or not the row was the billing/default/primary address for that customer, but AFAIK there's no way to ensure this is limited to a single address for any customer.

 

This approach is close to a possible solution... "but AFAIK there's no way to ensure this is limited to a single address for any customer"  why not?.... it is possible.

 

What I will do is this:

Add an additional attribute (column) to your ADDRESSES table with TYPE ENUM ALLOWING NULLS... per example:

is_billing  ENUM('T') DEFAULT NULL

 

and then create an UNIQUE INDEX either on the column pair (address_id, is_billing) or (customer_id, is_billing)... that should solve the problem I think.

 

[mikosiko]

EDITED:  Just realize that the suggested procedure is not going to work     

                  my apologizes.

 

Another thought was to, instead of the above, add a column to ADDRESSES that was a boolean indicator of whether or not the row was the billing/default/primary address for that customer, but AFAIK there's no way to ensure this is limited to a single address for any customer.

 

This approach is close to a possible solution... "but AFAIK there's no way to ensure this is limited to a single address for any customer"  why not?.... it is possible.

 

What I will do is this:

Add an additional attribute (column) to your ADDRESSES table with TYPE ENUM ALLOWING NULLS... per example:

is_billing  ENUM('T') DEFAULT NULL

 

and then create an UNIQUE INDEX either on the column pair (address_id, is_billing) or (customer_id, is_billing)... that should solve the problem I think.

 

[fenway]

I don't see why you can't have a type field in your addresses table with a unique index across the customer UID.

[mikosiko]

if you are asking me Fenway...

 

  in the solution that I did propose with an UNIQUE index across the customer UID the index is going to be duplicated.

 

example:

 

address_id customer_id        Enum

1                                        1        NULL

2                                        1          'T'                // address is billing

3                                        1        NULL          // Duplicated unique index

 

or you see another solution?..... after a bad night  I'm to sleepy now to think clearly 

 

[dgoosens]

I'd go for a third table:

BILLING_ADDRESSES

id | customer_id | billing_address_id

 

id is a simple auto-increment

customer_id should be UNIQUE

billing_address_id is the id of the address

 

this really seems the simplest and most "elegant" solution to me

[moagrius]

Thanks for the replies.  I've started implementing an approach similar to the one suggested by dgoosens, but am just using a single column: ADDRESS_FK that's a unique key.

 

CUSTOMERS - ID, NAME
ADDRESSES - ID, CUSTOMER_FK, STREET, CITY, STATE, ZIP
BILLING_ADDRESSES - ADDRESS_FK

 

are there any problems anyone foresees with this idea?

 

thanks again

[dgoosens]

sounds good to me

[mikosiko]

I'd go for a third table:

BILLING_ADDRESSES

id | customer_id | billing_address_id

.....

this really seems the simplest and most "elegant" solution to me

 

and a normalized one... good choice dgoosens