how do you handle the shipping address in the e-commerce?

searain

How do you handle the shipping address in the e-commerce?

I am working on an online shop. For the registered account coustmer we will keep the billing address, we will also need to keep the shipping address if it is differnet from billing address.

I have thinking about these two approaches.

1) in the client table, add the shipping address (such as shipping name, shipping city, shipping state ...) fields. But this way only one shipping address could be saved for one client.

2) add another table, called "client shipping address"

All the shipping address related to this client could be saved there and with the client id as the foreign key. this way, the shipping address could be unlimited.

Which one is better? How do you handle this kind situation?

DeltaRho2K

I would have one table with all demographics for the customer except the address (billing or shipping).
I would have another table for addresses.

In the form, have the person input their billing address with a "Check if shipping address is the same" checkbox.

If it is checked, have them fill out the shipping address form fields as well.

Run a query to insert both with foreign keys to refer back to the customer.

In the address table, just have an extra field called shipping or something, that is set to Y or 1 or whatever unique value to represent a yes. If it is the billing addy, and shipping addy, set it to Y. If it is billing but not shipping, don't set it and instead set the field to Y for the other address.

This way, your tables will be properly normalised, and you will be able to quickly pull the shipping addresses. I am sure others have other opinions, but that is my take on it. Good luck with whatever you do.

searain

I like Delta's idea here.

There is a small reason that I keep the billing address in the client table because it could be easily directly export the whole table to excel file and then the client can transfer the data from excel to his other datbase systems.

Of course, the client can use the database management tools to gather the same information too if I use Delta's ideas.

Any other ideas from anyone else?

DeltaRho2K

You can always create a merge table (and merge the tables based on the foreign keys.)

That would make it easy to export the info like you want to.

Just look in the mySQL manual at the merge table type, and see if that will suit your needs... If so, then bam, you have a winner... (I have a client with this type of setup, and it works well...)

And keep in mind, my idea is just one of many, so keep your feelers out. I replied here so this post will stay fresh, and hopefully people like Weedpacket and Stolzyboy will see it, and come in to put their dollar's worth for you.