Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 6 down vote accepted

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 ;)

share|improve this answer
+------------------+
| 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.

share|improve this answer

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       |
+------------------+
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.