>Actually, I've found more work up front usually means less work later, so full normalization to third normal form makes sense. Your point about missing an important piece has happened more than once with other projects. And you're right, we were half way through the project and the re-design of the data and screens was much more time consuming.
>
>Sometimes the user needs to send a bill to a separate new address, one that has never been input and will probably never be input again. You have a default billing address but you allow them to override that billing address.
>

Dan,

I have an ADDRESS table that is related to the CUSTOMER table. It can be pictured as the CUSTOMER table having a foriegn key that points back to the ADDRESS table (customer.addressid).

In reality, you would not keep the addressid in the CUSTOMER table because a customer can change their address and we still want to know about their previous addresses. Plus the customer can have a shipping address or seasonal addresses. To accomidate this, you would put a many-to-many table in between CUSTOMER and ADDRESS that had the following fields:

CUSTOMERADDRESS table:

customeraddressid	(I)	&& Primary Key
customerid		(I)	&& FK: customer table
addressid		(I)	&& FK: address table
homeaddress		(L)	&& Is this the customer's home address?
inactive		(L)	&& Is this address still active?

This scenario allows multiple addresses per customer, defaults to the home address, can present the order-taker with a list of current known addresses for that customer and allows old invoices to retain their correct address.

If you extrapolate this relationship out further, you will see that we need a link to the address in the INVOICE table as well as the CUSTOMER table. Here's where the normalization rules start to get complex. You might be tempted to simply drop an addressid in the INVOICE table, but this breaks the normalization rules. The normalized way to do it is to replace the customerid field in the INVOICE table with customeraddressid. Now our customers can have many addresses and each invoice is linked to just one of those addresses.

>How would you handle this scenario without a city and state table. Do you force them to entry an address first ( one time entry ) or do you allow them to enter city and state as text boxes as opposed to drop downs.
>

You could create tables that serve as lists for city/state/zip, but I find that zip codes change enough over time that it is too cumbersome to enforce normalization with relationships between tables. I would provide the user with a combobox where they could pick out a city from the master list or type one in.

- Keith

<snip>