Jump to content

Archived

This topic is now archived and is closed to further replies.

TomTees

Where to put addresses in my design?!

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Sorry, that isn't a solution, because the minute a Customer deletes an address from his/her "Address Book" then there goes the "Proof of Order" information.

 

 

TomTees

 

Share this post


Link to post
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.

Share this post


Link to post
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..

 

 

Share this post


Link to post
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

 

Share this post


Link to post
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

 

Share this post


Link to post
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).

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites

×
×
  • 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.