On 5/19/06, Martijn Tonies <m.tonies@stripped> wrote:
> Well, one reason could be, for example, that the address changes
> over time and they want to know what address the customer was
> when the order was processed.
> I wouldn't enter the "delivery" and "billing" info either, but create
> some sort of "customer_address" table or "order_customer_address"
> in which records would be inserted if and only if the addresses used
> for billing and delivery differ from the normal customer address.
And what happens if the customer changes their "normal" address but
you want to keep that historical information? This is mostly an
exercise in thinking what can happen, which you want to do.
The schema originally presented is NOT normalized, but I don't see
that it matters. An address for an order is just that, and isn't
technically redundant data, because if the customer's address changes,
the address for any *closed* orders won't change.
I will agree that it's redundant because for the most part the
addresses will be the same.
So an "Addresses" table and a reference to the billing and shipping
address ids in the orders table, and a reference to the "normal"
address in the customer table is the way I'd go -- that way if the
customer changes their address you don't have to change historical
data (which is a bad idea anyway, and you'd need an audit trail if you