1

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.

7

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

|improve this answer|||||
3

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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