TomTees Posted May 25, 2010 Share Posted May 25, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/ Share on other sites More sharing options...
Zane Posted May 26, 2010 Share Posted May 26, 2010 - 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 Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/#findComment-1063359 Share on other sites More sharing options...
TomTees Posted May 26, 2010 Author Share Posted May 26, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/#findComment-1063409 Share on other sites More sharing options...
ignace Posted May 26, 2010 Share Posted May 26, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/#findComment-1063421 Share on other sites More sharing options...
Zane Posted May 26, 2010 Share Posted May 26, 2010 "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.. Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/#findComment-1063429 Share on other sites More sharing options...
TomTees Posted May 27, 2010 Author Share Posted May 27, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/#findComment-1063963 Share on other sites More sharing options...
TomTees Posted May 27, 2010 Author Share Posted May 27, 2010 "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 Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/#findComment-1063964 Share on other sites More sharing options...
ignace Posted May 27, 2010 Share Posted May 27, 2010 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). Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/#findComment-1063989 Share on other sites More sharing options...
Zane Posted May 27, 2010 Share Posted May 27, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/202894-where-to-put-addresses-in-my-design/#findComment-1064100 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.