billing address and shipping address

blackhorse

for an e-commerce project, we allow the customer has multiple shipping address in the database.

How do you handle the same situation:

approach 1) two tables, customer and address.

customer table will only have the name, account number etc. address table holds the multiple addresses for the customer (including billing and shipping, with a flag telling which address is the billing address.)

approach 2) two table, customer and shipping_address

customer table will have the name, account number and billing address. shipping_address table will only have the shipping addresses.

I don't see the obvious advantages and disadvantages in both approaches. I used both. Recently I used the approach 2) more often.

Just want to know what do you think? If I can hear from your experience, it might help me to stick with one approach for a long time. Reuse some codes, not switch back and forward between these two approaches.

Thanks!

sid

the answer of this is entirely dependent on the application. in fact, in a recent job interview i was asked this very question and how i would decide what to go for.

in my opinion, the cleaner and more scalable approach is the first one.

blackhorse

I used to use approach 1).

But I found out in quit a few cases, that billing address should be separated from shipping address. The customer should be able to edit their shipping address freely. And they should also be able to delete all of their shipping address from the database (of course, next time when they order, they need input at least one shipping address.) without the merchant concerns. If billing address merged with shipping address, we have to concern all these activities, are they on billing address or shipping address.

But when customer edit their billing address, sometimes, the merchant may want to be auto notified by email or trig a database action. The merchant may have an off line desktop account or CRM system they need to update for this customers' account. They want to keep track if the billing address or other customer account crucial information is edited by the customer. (of course, even we save the billing address in the address table with other shipping addresses, it could also be achieved. Just I think separte them may make it more clear and straightforward.)

So what I do is save the billling address in customer table and save the shipping address in the shipping_address table. When customer first register, I take the address both as billing address in the customer table, and the default shipping address in the shipping_address table. Then the customer will deal with these two different records later on if they want to edit them.

More people want to share their opinions?

sid

i am not sure why you think that the customer wouldn't be able to change any data with the first approach.
your best bet to prevent any problems with changing data would be some temporal database scheme. this would log all the addresses that the user ever entered or edited. alternatively you could also just use a log file, which stores all changes to the database. the temporal database approach is probably going to be easier to implement though.

nemonoman

The question should be based on your business rules. What is the data REQUIRED for a customer? If a SINGLE billing address is ALWAYS required, then it reasonable to make the billing address an attribute of the customer, and include it in the customer table.

If multiple billing addresses or shipping addresses are part of the business rules, then these address belong in separate, related tables.

If neither billing nor shipping addresses are required, then these are also better placed in a separate table.

If using the separate table approach, you should NOT use separate tables billing_address and shipping_address. You SHOULD use one customer_address table only, identifying the address_type with a separate column.

Remember that placing addresses in a separate table means that you will need to consider JOINs rather carefully.

Consider:
SELECT customer.name, address.zip
FROM customer, address
WHERE address.customer_id=customer.id
AND address.type='BILLING'

If a billing address is an option, not a requirement, this query would return only the SUBSET of customers having billing address records.

To find all the customer names, even where the customer doesn't have a billing address, you'd need an OUTER JOIN.

SELECT customer.name, address.zip
FROM customer
LEFT JOIN address ON customer.id=address.customer_id
WHERE address.type='BILLING'

Or this:

SELECT customer.name, address.zip
FROM customer, address
WHERE address.customer_id=customer.id
AND address.type='SHIPPING'

If a customer has MULTIPLE shipping addresses, this query would return the customer's name MULTIPLE times (once for each shipping address).

name zip
nemo 12345
nemo 99999
nemo 67676

etc.

You could reduce the duplication by doing what a lot of websites do: assign an attribute to one of the shipping address ONLY, like address.status='DEFAULT'

SELECT customer.name, address.zip
FROM customer, address
WHERE address.customer_id=customer.id
AND address.type='SHIPPING'
AND address.status='DEFAULT'

Assuming you managed the status correctly, this would return only one record per customer shipping address.

Piranha

I can think of two ways of doing this. The first one is the same as your first option. The advantages are of course what have been said here before, but also that if you have to change something in the table you only have to change one table, it won't be problems that you forget to change something. It is also easy if you need to add a new type of address.

The other way is only possible if the customer have 0-1 billing address and 0-1 shipping address. Then it is possible to place both addresses in the customer table. With this way you don't have to worry about joining tables, everything is in the same place. I suggest this instead of your second option since I think that it is important to keep the same kind of information in the same place to avoid confusion.

Whatever choise you do don't forget to copy all the information to the invoices.