James,
> However, I came to the realization that if somebody changes their
> address, I
> don't want it to be changed on previous orders. So I think i'll
> change the
> orders table to contain the actual address information and use an
> INSERT ...
> SELECT instead. That way I can be sure I have an accurate record of
> all
> orders.
Hey, if you really want to be sophisticated about it, you should have
multiple addresses per customer, with perhaps a status selector to
indicate which addresses are new and which are old. For my current
Postgres app, it goes futher than that to support companies with many
contacts and locations:
Company Address Contact
. CompanyID -----------CompanyID------------CompanyID
. Name AddressID------------AddressID
. Status/Type Status/Type
. Address Name, Title
This allows me to have mutliple addresses per company (including old
addresses for historical compatibility) and multiple contacts at each
address. There's also an elegant (at least I think so) way to maintain
a "default" contact and address for each company.
Of course, this all requires some sophistication in the user interface,
as one has to make the user distinguish between address corrections
(UPDATE) and new addresses (INSERT) which possibly replace old addresses
(UPDATE SET STATUS = old).
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
