Jump to content

Where to put addresses in my design?!


TomTees

Recommended Posts

My wife and I are trying to start a home business selling things online.  I'm in charge of bulding the website.  While I have a fairly extensive IT background, a lot of this stuff is foggy in my brain since I don't do it much these days.

 

This is a pretty simple question, but where should I put Addresses in my database design?

 

Here is where I'm confused...

 

- A Customer really has to have one address always associated with them (in case we need to track them down).

 

- A Customer could have multiple addresses when you include Shipping Addresses.

 

- A Billing Address might not be the same as their Permanent Address.

 

- Address are also tied to an Order.

 

My thoughts...

 

There should be fields to handle Billing Address and Shipping Address in the Order table.

 

There might be fields to handle a "Permanent Address" in the Customer table.

 

It might make sense to create an "Address Book" table where the Customer can store all kinds of fields.

 

Then there is the further issue of where and how do you store "Historical Addresses"?!

 

See why I am confused...

 

 

 

TomTees

Link to comment
Share on other sites

- A Customer could have multiple addresses when you include Shipping Addresses.

- A Billing Address might not be the same as their Permanent Address.

There should be fields to handle Billing Address and Shipping Address in the Order table.

There might be fields to handle a "Permanent Address" in the Customer table.

Then there is the further issue of where and how do you store "Historical Addresses"?!

 

 

It might make sense to create an "Address Book" table where the Customer can store all kinds of fields.

There's your answer right there.  Though I would just call it addresses

Link to comment
Share on other sites

because the minute a Customer deletes an address from his/her "Address Book" then there goes the "Proof of Order" information.

 

Isn't this what data-integrity rules are for? Plus I thought you mentioned that you wanted to keep historical addresses? If you allow your customer to really delete addresses then that can be a problem.

Link to comment
Share on other sites

"Proof of Order"...? elaborate a little.

 

What are you trying to achieve? And as ignace said, if you wanted to have historical data in the first place why would you allow them to delete addresses?

 

An address book table seems to me to be the simplest solution..  Using one address per row.  This way you can query for all the addresses of a particular user.. or you can query for the primary address of a particular user, etc..

 

 

Link to comment
Share on other sites

because the minute a Customer deletes an address from his/her "Address Book" then there goes the "Proof of Order" information.

 

Isn't this what data-integrity rules are for? Plus I thought you mentioned that you wanted to keep historical addresses? If you allow your customer to really delete addresses then that can be a problem.

 

Websites like Amazon let Customers add and delete addresses from their Address Book willy-nilly.

 

If you place an Order, they obviously permanently store the Billing and Shipping Address for an Order.

 

So you could have Box 123, Anywhere, USA in your Address Book, place an Order, then delete it, and it would be gone from your Address Book but still in Amazon's Order History.

 

The Address Book is really more just a convenient "scratch pad" for the Customer, and seperate from how you would want to store things on the back end.

 

Or that is how I've seen it at least.

 

 

 

TomTees

 

Link to comment
Share on other sites

"Proof of Order"...? elaborate a little.

 

If the [Address Book] pointed to [Orders] and you let Customers delete an Address, then your Order would have nulls for the Billing and/or Shipping Address.

 

It seems like you have to store the Billing and Shipping Address in the Order table itself, even if it is in the Customer's Address Book which they can add to or delete from.

 

 

What are you trying to achieve? And as ignace said, if you wanted to have historical data in the first place why would you allow them to delete addresses?

 

An address book table seems to me to be the simplest solution..  Using one address per row.  This way you can query for all the addresses of a particular user.. or you can query for the primary address of a particular user, etc..

 

As I said in my response to ignace, the Address Book is just for the Customer's convenience.  We absolutely must have Billing and Shipping Addresses for each Order (and thus I would say store that infor in the Order table).

 

It *might* also make sense to have a history of a Customer's address, but then again, maybe that isn't necessary?!

 

If we can link a Customer to Orders - which have the Billing and Shipping Address at the time of the Order - is that enough for legal purposes?

 

Or do we need to keep an address of where people live too?  (Because you could live at "123 Main Street" but have a Billing Address of "P.O. Box 77" and a Shipping Address of "MyJob, Route 27, Suite C".)

 

 

 

TomTees

 

Link to comment
Share on other sites

If you place an Order, they obviously permanently store the Billing and Shipping Address for an Order.

 

Yes, just add the selected address to the order (no reference). But you are also able to - instead of delete - hide an address and when they re-enter it, un-hide. Both serves your client needs (manage) and yours (historical data).

Link to comment
Share on other sites

You need what's called a many to many relationship.. in which you have 3 tables..

 

- The customer table

- The addresses table

- The orders table

 

Every time a new address is added simply add it to the address table and update the address_id in the customer table.  Then, whenever a customer makes an order..use that address_id.  If a customer deletes an address.. it will still be in the address table, though they will have no address_id until they add another one.

 

Get me?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.