Need suggestion for placing foreign keys in the databases

Question

I have the database for customers. The customers table is something like this

+------------------+
|     Customers    |
+------------------+
| id  (PK)         |
| business_email   |
| business_name    |
| customer_name    |
| payment_terms    |
| currency         |
| business_address |
| city             |
| state            |
| postal_code      |
| country          |
| phone            |
| created_at       |
| updated_at       |
+------------------+

Now I want to put the shipping address in my application where the customer can optionally mention his shipping address.S o I have made an extra table for shipping address like this

+------------------+
| Shipping Address |
+------------------+
| id  (PK)         |
| contact_name     |
| contact_address  |
| delivery_address |
| created_at       |
| updated_at       |
+------------------+

Now my problem is I am little bit confused in whose key should be foreign key in which table.I am strucked in this.So any help and suggestion will be highly appriciable.

Answer 1

Assuming a company can have more than one shipping address, the key would be a column added on Shipping Address table (aptly named 'customer_id' and the foreign key definition on the Shipping Address table. If you wanna avoid same address more than once, also add a unique key on shipping address table that can include (customer_id, contact_name)

On an unrelated note, make sure your created_at and updated_at use timestamps ;)

Answer 2

Perhaps you may want to add a customer id

+------------------+
| Shipping Address |
+------------------+
| id  (PK)         |
| customer_id (FK) |
| contact_name     |
| contact_address  |
| delivery_address |
| created_at       |
| updated_at       |
+------------------+

Answer 3

+------------------+
| Address          |
+------------------+
| address_id  (PK) |
| address_line_1   |
| address_line_2   |
| address_line_3   |
| address_line_4   |
| city             |
| state_province   |
| country          |
| postal_code      |
| created_at       |
| updated_at       |
+------------------+

+------------------+
| Cust_X_Address   |
+------------------+
| Customer_id (PK) |  < references Customer
| Address_id  (PK) |  < references Address
| address_type (PK)|  < may be hardcoded  (Billing, Shipping) 
| active_flag      |     or may reference an address type table
| address_contact  |
| created_at       |
| updated_at       |
+------------------+

Problems this design solves:

1. What happens if a customer moves - and a new customer uses that old address? If you store the customer_id in the address, you no longer have any knowledge that the first customer ever used that address.

2. Why must an address be a shipping address? An address is an address - the fact that you're using it as a shipping address is contextual to that customer, not a proper attribute of the address itself. Using an address type in the Customer_X_Address entity allows you to see, for example, the difference between a customer's billing and shipping addresses easily, and to store contacts that are more appropriate to billing (accounts payable) and receiving (shipping)..

3. Every shipment/transaction could then reference the customer_x_address address entity, and perhaps multiple times, one for billing, one for shipping. If so, it'd probably be a good idea to add a surrogate key to the customer_x_address table.